Can not see queries in Excel when codes seems lighter.

[mbouaici@hotmail.com]

If your data is in Excel, fix it there. PowerQuery will run circles around anything you can do in Access. If you have dates as column headers, you likely need to unpivot your data so that you end up with something more like (category, date, amount). Takes like 2 seconds in PowerQuery

I can not import it to Excel. It is in Access and working there but need it in Excel through Get Data, Access Database and then select the query. POwer query does not even see it..



M.

[mike60smart]

When I use Get Data in Excel the only query that displays for selection is "qryTotalSum"
All other queries are not listed.

[madpiet]

i would use Access as a source in Excel, and then when it prompts you for a table, select all of the ones you want. Then you can use the Wizardy Append query thing in PowerQuery to append all the data to a single table, and you're done. then import that into Access. But do it any way you want. I just found doing in Excel to be orders of magnitude quicker and easier than doing it in Access. But you do you.

[madpiet]

In PowerQuery, you should be able to filter your table names for Starts With "MITCLI_TB_Non_Current_" and return just those tables.

[madpiet]

All this for something like
SELECT MonthYear, SUM([NumericColumn])
FROM Chart_of_Accounts INNER JOIN MITCLI_TB ON Chart_Of_Acounts.[No] = MITCLI_TB.[No]
WHERE Chart_Of_Acounts.Cat3='Deferred tax assets'
GROUP BY MITCLI_TB.[No], MITCLI_TB.Name, Chart_Of_Acounts.Cat3

?
This is why I've been restructuring. This design is crazy brittle and it's going to be a huge PITA to answer anything more than the most basic questions. I transformed it in PowerQuery/Excel (in spite of your protests) because PowerQuery/Excel just blow the doors of Access for doing this kind of thing. Maybe Database Design for Mere Mortals by Michael Hernandez would be a good place to start.

[Welshgasman]

I think beacuse your data is so not normalised, that Access will not show those queries?
I tried a simple select of the NCA query and that still did not show up in Excel.

I would learn PowerQuery and go with MadPiet's solution, else export the queries to Excel with some VBA.
So you Push instead of Pull.

[Gicu]

You could change those two select queries (in Access) to make-table queries, create a macro to run them both then run that macro from Excel when you open your Excel file (in which you link to the new tables not the original select queries).
https://stackoverflow.com/questions/...ros-from-excel

Cheers,

[madpiet]

How about this... don't do any summary in Excel or Access or whatever.

Put all the Excel files in the same folder (if the data is on different tabs, I think you have to use PowerQuery and loop over the tabs in Excel). But anyway... so they're NOT summarized at all.

Append all those together (so no dates in column names), so you have one table with all the data.

(Then if you don't need gory level detail, summarize that in PowerQuery... if you don't need super low level detail)
Then import that into your database. Then summarize.

[madpiet]

Are you basically trying to create a crosstab type report in Excel from this stuff?

(similar to the crosstabs you have Non_current_assets, and Non_current_liabilities?)

You'd do that in Excel maybe using a data model and DAX

What questions are you trying to answer with this? Maybe that will make it easier to choose how to approach it.

[madpiet]

I fixed tables problem. Now I have one table with all the Transactions. To what do [ID] and [No] refer? Are they join columns to another table?

SELECT [3_Entities_01_2024].ID, [3_Entities_01_2024].[No], [3_Entities_01_2024].Amount, [3_Entities_01_2024].Month, [3_Entities_01_2024].Year
FROM 3_Entities_01_2024
UNION ALL
SELECT [3_Entities_02_2024].ID, [3_Entities_02_2024].[No], [3_Entities_02_2024].Amount, [3_Entities_02_2024].Month, [3_Entities_02_2024].Year
FROM 3_Entities_02_2024
UNION ALL
SELECT [3_Entities_03_2024].ID, [3_Entities_03_2024].[No], [3_Entities_03_2024].Amount, [3_Entities_03_2024].Month, [3_Entities_03_2024].Year
FROM 3_Entities_03_2024
UNION ALL
SELECT [3_Entities_04_2024].ID, [3_Entities_04_2024].[No], [3_Entities_04_2024].Amount, [3_Entities_04_2024].Month, [3_Entities_04_2024].Year
FROM 3_Entities_04_2024
UNION ALL
SELECT [3_Entities_05_2024].ID, [3_Entities_05_2024].[No], [3_Entities_05_2024].Amount, [3_Entities_05_2024].Month, [3_Entities_05_2024].Year
FROM 3_Entities_05_2024
UNION ALL
SELECT [3_Entities_06_2024].ID, [3_Entities_06_2024].[No], [3_Entities_06_2024].Amount, [3_Entities_06_2024].Month, [3_Entities_06_2024].Year
FROM 3_Entities_06_2024
UNION ALL
SELECT [3_Entities_07_2024].ID, [3_Entities_07_2024].[No], [3_Entities_07_2024].Amount, [3_Entities_07_2024].Month, [3_Entities_07_2024].Year
FROM 3_Entities_07_2024
UNION ALL
SELECT [3_Entities_08_2024].ID, [3_Entities_08_2024].[No], [3_Entities_08_2024].Amount, [3_Entities_08_2024].Month, [3_Entities_08_2024].Year
FROM 3_Entities_08_2024
UNION ALL
SELECT [3_Entities_09_2024].ID, [3_Entities_09_2024].[No], [3_Entities_09_2024].Amount, [3_Entities_09_2024].Month, [3_Entities_09_2024].Year
FROM 3_Entities_09_2024
UNION ALL
SELECT [3_Entities_10_2024].ID, [3_Entities_10_2024].[No], [3_Entities_10_2024].Amount, [3_Entities_10_2024].Month, [3_Entities_10_2024].Year
FROM 3_Entities_10_2024
UNION ALL
SELECT [3_Entities_11_2024].ID, [3_Entities_11_2024].[No], [3_Entities_11_2024].Amount, [3_Entities_11_2024].Month, [3_Entities_11_2024].Year
FROM 3_Entities_11_2024
UNION ALL SELECT [3_Entities_12_2024].ID, [3_Entities_12_2024].[No], [3_Entities_12_2024].Amount, [3_Entities_12_2024].Month, [3_Entities_12_2024].Year
FROM 3_Entities_12_2024;

[madpiet]

I fixed tables problem. Now I have one table with all the Transactions. To what do [ID] and [No] refer? Are they join columns to another table?

What are the base tables? I'm thinking this design needs to be normalized and rebuilt so that it works.

[madpiet]

Oh, I used Excel to unpivot this into something super simple. (No, Value, Date). Then I assume that joins to the Accounts dimension.