I'm using Office 2003 and have done some programming in Access, but not in Excel. - I have an Access DB with a form to enter all receipts and invoices. I use Excel to view the data as a Monthly/Yearly Budget Summary based on categories of the Items entered in the Access form. I am pulling the data from the Items table in Access and want Excel to SUM each category and enter the values in the worksheets by month. So the user will only need to enter the receipts in the Access form, open Excel, select the month, then view/print the worksheets. I do not have any code in excel yet, as I don't even know where to start. Currently I use the toolbar button in Access to Analyze the Items table with Excel, sort by Category, then highlight the values to get the total and enter it in the appropriate worksheets. The worksheets are an instant snapshot of Current vs Projected amounts for the budget. I have about a dozen applications for this process, once I can finally understand it. Thank you for your assistance.
-
-
You should be able to do this in either an Aggregate query and report or using a crosstab query; thus, no need for Excel... Unless you need the statistics, other financial tools, what-if analysis, or (like me) you have several people that are expecting an Excel workbook for the results and just will not change their ways. Sum data by using a query (ACC2007) (I know this references Access 2007; however, on a fairly quick review, I didn't see anything in the article that wouldn't also be valid for ACC2003)
A few ways to get at the Access data:
You can link from Excel to Access:
[url=http://office.microsof t.com/en-us/access-help/import-export-and-link-data-between-access-and-excel-HP001095095.asp x]Import, export, and link data between Access and Excel[/ur] once you have the link established, then you can manipulate away.
Or you can take a look at the TransferSpreads heet Method (Access 2003 VBA Language Reference) A few things to note with this method is that it requires the use of a "stored" or "Named" query... that is to say, that you cannot use a SQL/Record-set at runtime. However, there are ways around this limitation using a parameter based query that pulls the data in question, I don't remember if ACC2003 will allow that or not for this method, or by adding/modifying a query in the QRYDEFS-Collection.
Then there is "Office Automation" where we can use a runtime SQL and then open a workbook and move the information from the record set into the workbook; however, this can be very heavy-duty VBA coding! -
I guess you answered both questions I posted (1 for Access and 1 for Excel). I've attached the DB PrtScr (in Word) and the Workbook so you can see exactly what I'm trying to do.
The purpose of these files is for budgeting and reporting for home and home business use. The DB is used for entering all receipts and invoices and the workbook is used for analysis and reporting. Income is entered into the workbook and the expenses in the DB. The FMB worksheet is the report and I will be working more with the data from there (i.e. charts, etc). I want this automated so that all the (non-tech) user will have to do is enter the receipts/invoices in the DB (Receipts Main Form) and Paychecks/Income in Excel (Wages tab), then be able to view the current data in the FMB and YMC-B worksheets. Each month will be a new FMB sheet and the YMC-B sheet is for the year. Each Group/Item on the FMB sheet corresponds to the FMBGroup and FMBLineItem fields in the Items table in the db.
The source of the data I want to bring into Excel is the Items table in the db. A link is likely the best way to connect them? I hope this gives you a better idea of what I'm trying to do.Comment
Comment