Transpose

[zezo2021]

I want to convert all query rows to excel like the report in image 2

The report not converted correctly to excel

and transpose in excel need a lot of manual work

 

[plog]

This might be a first--what you have/want in Excel is the correct way to store your data in a database,

Unless 1.png is an image of the results of a cross-tab query, there is no easy way to get your data from Access to the format you want. You should not have fields in tables named after specific data types (e.g. Taxes, Dividends, Wages, etc.). Instead you should have a field called [FinancialType] and in that field you would store the type value ('Taxes', 'Dividends' etc.). Your table when opened, should look like your expected Excel format.

I suggest you bite the bullet and restructure your database to properly structure your data so its easy to export it in the manner you want.

 

[ebs17]

transpose in excel need a lot of manual work

Excel has transpose functions, in Access this has to be programmed more laboriously. A crosstab query can transform rows into columns, but not the other way around.

What you might examine: A recordset's GetRows method produces a transposed array, which could be pasted (copied) directly into an Excel spreadsheet. However, only a part of your table would be included, the other via CopyFromRecordset. But those are just thoughts, pictures in my head.
Only someone who has the database table and the Excel sheet on the opposite side can try something clever. So I can stop right there.

 

[MajP]

If you are starting with that query in Access you can simply make the exact output using a Union query.

 

[Pat Hartman]

Why do you need to convert the report to Excel? Is there something wrong with the Report format? Wouldn't a .pdf solve the problem if you need a file to send to someone?

 

[arnelgp]

see finalQuery and also Query1.

 

[zezo2021]

see finalQuery and also Query1.

Thank you so much for your great help

I'm testing

and try to learn