Export Data to Excel

[AccessKurzo]

How would you export Invoice information to Excel with multiple line items and then do a subTotal of Line Items?

I know how to export just the Invoice info or just the line items, but I need something like an access report that contains a subReport - only in Excel

I have 2 queries - one for all Invoice info and another query for the Line Items. I can merger the 2 queries into one, but then I get the invoice info repeated for each line item.

What would be nice is to have the Invoice Info on one line, then have all line items underneath that Line - and continue for all invoices

 

[AccessKurzo]

Ok - so I found the 'Insert subdatasheet' in the datagrid view of the Invoice info query.

I can expand the record to show the line items that correspond, but when I export the query to excel - it just exports the main Invoice info query - not with the subdatasheet details.

How can I get it to export the subdatasheet details also?

 

[neileg]

Why do you want to do this? Why can't you just create an Access report and be done with it?

Is there something extra you want to do in Excel?

 

[Limbersky]

Hi,
there is an Access add-in (MultiLevelExporter) available on the web that does this kind of job, i.e. it exports data of both master datasheet and also the underlying linked subdatasheet into Excel.
Norbert

 

[spikepl]

A word of advice, take it or leave it: do not raid old (in this instance 10 years!) posts to advertise yourAccess- extention. This site would die under all kinds of spam if they allowed it.

 

[xanksx]

Why don't you try creating a pivot in excel which is linked to your table / select query from your access database.

This not only provides great flexibility in terms of have different summary views in excel but it also enables you to drill down to see the underlying records that make up the subtotals by double clicking on them.

Besides, it is linked to your database so all you need to do to see the latest information is refresh the pivot.

Below are steps to create a pivot in excel that is linked to access database table / query.
1) In excel go to the data tab.
2) Select "From Other Sources" > "From Microsoft Query"
3) Choose "MS Access Database" in the choose data source window.
4) Enter the full location of the database in the Database name.
5) Select the table / query that has the data you want to show in excel. Drag all the columns you need to the right using the ">" button.
6) Just select next for the following questions.
7) You'll get a prompt in excel to create a table or pivot. Choose pivot and you are done.
8) Drag the fields in the row / column / total sections to see how your summary shows up. (If you haven't used pivots before this can be the tricky part. But rest assured this is very powerful method of presenting summary in excel from your database.)