473,703 Members | 2,355 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Suggestions: Best Way to Integrate Excel with Access - Pivot Tables

Dear All,

I have an Access database that tracks the progress of income tax
audits. When the taxing authorities make a change (an "Adjustment "), I
record the pertinent information in the database. At the end of the
audit cycle, these adjustments are used to compute the revised taxes
due. This computation is done in a fairly complex, but accurate and
easy to use, spreadsheet.

In the spreadsheet, there is a sheet that lists out all the adjustments
in a record format (ie - a row is a record, columns are fields, and the
range is named for easy reference.) On another sheet, there are pivot
tables that summarize this data in various ways. From there, other
parts of the spreadsheet reference these pivot tables to drive the
computation.

Currently, the spreadsheet and the database don't talk. I would like
to integrate them to prevent the redundant storage of the adjustments.
I would like your thoughts/war stories on the best way to integrate the
spreadsheet and database. Should I write something to pull the
adjustments over, and then refresh the pivot tables? Should I point
the pivot tables at a query in Access? I'm really looking for you
opinions before I start down a rabbit hole and have to climb back out
and try something different.

As always, TIA for you highly-valued opinions,
Johnny Meredith

Nov 13 '05 #1
1 4129

Personally, since there are aspects of the Excel Spreadsheet that you
can't control, I'd recommend having Excel pull the data out of Access,
rather then having Access "send" the data to the Excel spreadsheet.
This will aso overcome some rather interesting aspects of file sharing
that are not really controlable from Access.

Anyways, any competent Excel programmer should be able to help you in
this task, or if you prefer, you can check out some of the Microsoft
KnowledgeBase articles on how to get data out of an Access database
from Excel.

Last time I looked, there were more then a few examples in the KB, as
well as some "templates" in the downloads section of
<http://www.office.micr osoft.com/> , which is pretty much the _best_
resource for samples on the net. (IMHO, and no I don't have anything
there.)
Johnny Meredith wrote:
I have an Access database that tracks the progress of income tax
audits. When the taxing authorities make a change (an "Adjustment "), I
record the pertinent information in the database. At the end of the
audit cycle, these adjustments are used to compute the revised taxes
due. This computation is done in a fairly complex, but accurate and
easy to use, spreadsheet.
In the spreadsheet, there is a sheet that lists out all the adjustments
in a record format (ie - a row is a record, columns are fields, and the
range is named for easy reference.) On another sheet, there are pivot
tables that summarize this data in various ways. From there, other
parts of the spreadsheet reference these pivot tables to drive the
computation.
Currently, the spreadsheet and the database don't talk. I would like
to integrate them to prevent the redundant storage of the adjustments.
I would like your thoughts/war stories on the best way to integrate the
spreadsheet and database. Should I write something to pull the
adjustments over, and then refresh the pivot tables? Should I point
the pivot tables at a query in Access? I'm really looking for you
opinions before I start down a rabbit hole and have to climb back out
and try something different.


Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
6603
by: [Yosi] | last post by:
Can I make an Excel file without having Excel in my PC ? I want to create Excel files from my C# application , then open those files later in another PC who have Excel installed . As we can open and read from Access files in C# application without having Access in this PC in this case we install MDAC , Is there any thing similar to MDAC for Excel ? if yes where can I download it ? what is the name of the file ?
1
3881
by: Jerome Ranch | last post by:
I consider myself an Excel PT wizard of sorts, but now I have a situation with so much infromationthat I need to categorize and summarize, that I will use access to manage it. Interestingly, though, the pivot tables in excel 2003 do not resemble the pivot tables in access 2003 as closely as they did previous versions of the products. One of the most powerful features for me is the grouping function. In excel 2003 if one right clicks date...
8
4863
by: Jerome Ranch | last post by:
Okay So I've got pivot tables setup in Access 2003. Only about 30K records in the current 2005 databases...the pivots summarize the info in a number of nice ways. I need to get the pivot tables into a document so I can distribute the tables, as is, as an electronic report (without the detailed data) So I export to rtf and xls, and I get an error that there is a too much information. I don't want all the data, just the summary table!
3
2395
by: Decreenisi | last post by:
Hi, I have to convert an existing spreadsheet for rejects into an access database. My problem is I use a load of lookup tabels in excel. How do I approch this in access. Also, just a general question, can you do pivot tables in access. Thanks
3
8488
by: nikila | last post by:
Hi, I have to create excel pivot tables from vb.net. Already I am creating excel file using oledb connection. I want to use the same to create the excel pivot tables. Can anyone please help me on this. It is very urgent. Please reply immediately Thanks for the help!!!! nikila
1
3263
by: NS3687 | last post by:
Hi, Is there a way to simulate the Excel Pivot table flexibility in MS Access? I mean, once I get the needed data into one table, instead of writing one query to for an analysis point, I'd lke to be able to change (add / remove) columns from the Form GUI without having to go back to Query Design/SQL. Similarly to those flexibility we have in Excel. I know I can always transfer table to Excel and use Pivot table, but when I have a large...
2
3333
by: VitorCastro | last post by:
Hello... First, sorry for my bad english... i have generated many pivot tables in Access 2003. and i want the code to export all of them to one single excel sheet and specific cell. i have little knowledge about this export function. i have generated many reports in excel, and save the sheet for information that pivot tables in access. with information in the specifici sheet and specific cell, e generated automatically the reports.. ...
4
3382
by: drt | last post by:
NEDERLANDS: Hallo, Ik heb eigen functies gemaakt in access, die werken perfect in de access query. Zodra ik echter vanuit excel een draaitabel maakt naar de access query (als een externe database) krijg ik de foutmelding dat er een 'ongedefinieerde functie' in de access database aanwezig is. Kan iemand mij op weg helpen dit probleem op te lossen? Alvast bedankt voor de reactie,
4
1027
by: DeanL | last post by:
Hi guys, I'm fairly fluent in MS Access but I've been handed a task that is baffling me. I've been asked to produce a report(s) in Excel that has a series of sales by territory. I have a bunch of raw data in a spreadsheet that has several items including Name, Address, State, ZIP, Territory, ItemCode, SaleDate, Quantity and Price. From this data, I would like to create a macro or something that would take the data for a given...
0
8758
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9121
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9017
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4432
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3123
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2450
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2069
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.