Can not see queries in Excel when codes seems lighter.

[mbouaici@hotmail.com]

Hey Guys,

Hope you are all well. Thanks for maintaining this forum. I love access but I struggle sometimes. FOr instance here, I have a code that seems lighter than one of my previous codes but I could see the query in the previous codes and can not now. I do not see any NZ in my code (hear it needed to be changed)... Coming to you guys see if you can help. Just copying the code but if needed, I can send the db.

Thank you again gents.

This is the code that works. There are some slight changes from the versions but I would need this one to be working.

SELECT MITCLI_TB.[No], MITCLI_TB.Name, Chart_Of_Acounts.Cat3, Sum(IIf((SELECT SUM(Dec23) FROM qryTotalSum WHERE Dec23 > 0),([MITCLI_TB].[Dec23]),0)) AS Dec23, Sum(IIf((SELECT SUM(Jan24) FROM qryTotalSum WHERE Jan24 > 0),([MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Jan24, Sum(IIf((SELECT SUM(Feb24) FROM qryTotalSum WHERE Feb24 > 0),([MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Feb24, Sum(IIf((SELECT SUM(Mar24) FROM qryTotalSum WHERE Mar24 > 0),([MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Mar24, Sum(IIf((SELECT SUM(Apr24) FROM qryTotalSum WHERE Apr24 > 0),([MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Apr24, Sum(IIf((SELECT SUM(May24) FROM qryTotalSum WHERE May24 > 0),([MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS May24, Sum(IIf((SELECT SUM(Jun24) FROM qryTotalSum WHERE Jun24 > 0),([MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Jun24, Sum(IIf((SELECT SUM(Jul24) FROM qryTotalSum WHERE Jul24 > 0),([MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Jul24, Sum(IIf((SELECT SUM(Aug24) FROM qryTotalSum WHERE Aug24 > 0),([MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Aug24, Sum(IIf((SELECT SUM(Sep24) FROM qryTotalSum WHERE Sep24 > 0),([MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Sep24, Sum(IIf((SELECT SUM(Oct24) FROM qryTotalSum WHERE Oct24 > 0),([MITCLI_TB].[Oct24]+[MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Oct24, Sum(IIf((SELECT SUM(Nov24) FROM qryTotalSum WHERE Nov24 > 0),([MITCLI_TB].[Nov24]+[MITCLI_TB].[Oct24]+[MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Nov24, Sum(IIf((SELECT SUM(Dec24) FROM qryTotalSum WHERE Dec24 > 0),([MITCLI_TB].[Dec24]+[MITCLI_TB].[Nov24]+[MITCLI_TB].[Oct24]+[MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Dec24, Sum(IIf((SELECT SUM(Dec24) FROM qryTotalSum WHERE Dec24 > 0),([MITCLI_TB].[Dec24]+[MITCLI_TB].[Nov24]+[MITCLI_TB].[Oct24]+[MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Total, Sum(IIf((SELECT SUM(Jan24) FROM qryTotalSum WHERE Jan24 > 0),([MITCLI_TB].[Jan24]),0)) AS VarJan24, Sum(IIf((SELECT SUM(Feb24) FROM qryTotalSum WHERE Feb24 > 0),([MITCLI_TB].[Feb24]),0)) AS VarFeb24, Sum(IIf((SELECT SUM(Mar24) FROM qryTotalSum WHERE Mar24 > 0),([MITCLI_TB].[Mar24]),0)) AS VarMar24, Sum(IIf((SELECT SUM(Apr24) FROM qryTotalSum WHERE Apr24 > 0),([MITCLI_TB].[Apr24]),0)) AS VarApr24, Sum(IIf((SELECT SUM(May24) FROM qryTotalSum WHERE May24 > 0),([MITCLI_TB].[May24]),0)) AS VarMay24, Sum(IIf((SELECT SUM(Jun24) FROM qryTotalSum WHERE Jun24 > 0),([MITCLI_TB].[Jun24]),0)) AS VarJun24, Sum(IIf((SELECT SUM(Jul24) FROM qryTotalSum WHERE Jul24 > 0),([MITCLI_TB].[Jul24]),0)) AS VarJul24, Sum(IIf((SELECT SUM(Aug24) FROM qryTotalSum WHERE Aug24 > 0),([MITCLI_TB].[Aug24]),0)) AS VarAug24, Sum(IIf((SELECT SUM(Sep24) FROM qryTotalSum WHERE Sep24 > 0),([MITCLI_TB].[Sep24]),0)) AS VarSep24, Sum(IIf((SELECT SUM(Oct24) FROM qryTotalSum WHERE Oct24 > 0),([MITCLI_TB].[Oct24]),0)) AS VarOct24, Sum(IIf((SELECT SUM(Nov24) FROM qryTotalSum WHERE Nov24 > 0),([MITCLI_TB].[Nov24]),0)) AS VarNov24, Sum(IIf((SELECT SUM(Dec24) FROM qryTotalSum WHERE Dec24 > 0),([MITCLI_TB].[Dec24]),0)) AS VarDec24


FROM Chart_Of_Acounts INNER JOIN MITCLI_TB ON Chart_Of_Acounts.[No] = MITCLI_TB.[No]
GROUP BY MITCLI_TB.[No], MITCLI_TB.Name, Chart_Of_Acounts.Cat3
HAVING (((Chart_Of_Acounts.Cat3)='Deferred tax assets'));

This is the code that does not work. It is bigger than the other version but I still can see it when I try to bring it to Excel through Get Data.

SELECT MITCLI_TB.[No], MITCLI_TB.Name, Chart_Of_Acounts.Cat3, Sum(IIf((SELECT SUM(Dec23) FROM qryTotalSum WHERE Dec23 > 0),([MITCLI_TB].[Dec23]),0)) AS Dec23, Sum(IIf((SELECT SUM(Jan24) FROM qryTotalSum WHERE Jan24 > 0),([MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Jan24, Sum(IIf((SELECT SUM(Feb24) FROM qryTotalSum WHERE Feb24 > 0),([MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Feb24, Sum(IIf((SELECT SUM(Mar24) FROM qryTotalSum WHERE Mar24 > 0),([MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Mar24, Sum(IIf((SELECT SUM(Apr24) FROM qryTotalSum WHERE Apr24 > 0),([MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Apr24, Sum(IIf((SELECT SUM(May24) FROM qryTotalSum WHERE May24 > 0),([MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS May24, Sum(IIf((SELECT SUM(Jun24) FROM qryTotalSum WHERE Jun24 > 0),([MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Jun24, Sum(IIf((SELECT SUM(Jul24) FROM qryTotalSum WHERE Jul24 > 0),([MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Jul24, Sum(IIf((SELECT SUM(Aug24) FROM qryTotalSum WHERE Aug24 > 0),([MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Aug24, Sum(IIf((SELECT SUM(Sep24) FROM qryTotalSum WHERE Sep24 > 0),([MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Sep24, Sum(IIf((SELECT SUM(Oct24) FROM qryTotalSum WHERE Oct24 > 0),([MITCLI_TB].[Oct24]+[MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Oct24, Sum(IIf((SELECT SUM(Nov24) FROM qryTotalSum WHERE Nov24 > 0),([MITCLI_TB].[Nov24]+[MITCLI_TB].[Oct24]+[MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Nov24, Sum(IIf((SELECT SUM(Dec24) FROM qryTotalSum WHERE Dec24 > 0),([MITCLI_TB].[Dec24]+[MITCLI_TB].[Nov24]+[MITCLI_TB].[Oct24]+[MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Dec24, Sum(IIf((SELECT SUM(Dec24) FROM qryTotalSum WHERE Dec24 > 0),([MITCLI_TB].[Dec24]+[MITCLI_TB].[Nov24]+[MITCLI_TB].[Oct24]+[MITCLI_TB].[Sep24]+[MITCLI_TB].[Aug24]+[MITCLI_TB].[Jul24]+[MITCLI_TB].[Jun24]+[MITCLI_TB].[May24]+[MITCLI_TB].[Apr24]+[MITCLI_TB].[Mar24]+[MITCLI_TB].[Feb24]+[MITCLI_TB].[Jan24]+[MITCLI_TB].[Dec23]),0)) AS Total, [Jan24] - [Dec23] AS Jan_24_Var, [Feb24] - [Jan24] AS Feb24_Var, [Mar24] - [Feb24] AS Mar24_Var, [Apr24] - [Mar24] AS Apr24_Var, [May24] - [Apr24] AS May24_Var, [Jun24] - [May24] AS Jun24_Var, [Jul24] - [Jun24] AS Jul24_Var, [Aug24] - [Jul24] AS Aug24_Var, [Sep24] - [Aug24] AS Sep24_Var, [Oct24] - [Sep24] AS Oct24_Var, [Nov24] - [Oct24] AS Nov24_Var, [Dec24] - [Nov24] AS Dec24_Var
FROM Chart_Of_Acounts INNER JOIN MITCLI_TB ON Chart_Of_Acounts.[No] = MITCLI_TB.[No]
GROUP BY MITCLI_TB.[No], MITCLI_TB.Name, Chart_Of_Acounts.Cat3
HAVING (((Chart_Of_Acounts.Cat3)='Deferred tax assets'));

[June7]

Did you mix up statements?

In first statement you say "works" but then "need this one to be working".

In second statement you say "does not work" but then "still can see it"

Exactly what was changed?

[mike60smart]

Can you upload a zipped copy of the Db?

[CarlettoFed]

Access not Excel.

[mbouaici@hotmail.com]

Hey, I could not zip it but find a link. This is just my db. It is pretty simple. I just can not see the queries in excel, the two I need.

Guys, whether you help me or not, appreciate the support.

M.

https://www.transfernow.net/dl/20241...Jy6u9/3Mbdm9VI

[mbouaici@hotmail.com]

Hey,

Here is the db It is just an extract. I did not know you could go from 5 M to 310 K by zipping. Long time I have not used this

This is a simple db but I can not see the queries that matters, the assets and the liabilities. This is just to have some accounting codes one side or the other depending on the value whether positive or negative.

Let me know if the file was attached correctly.

THank you gents.
M.Test.zip

[mike60smart]

Hi
Your Db file uploaded ok but the structure of your tables is all wrong.

You should not have 1 table for Each Month of a Specific Year.

You should have all records in a Transaction Table with a field to indicate the DateOfTransaction.

[mbouaici@hotmail.com]

Hey,

i wanted to have all the information in one year but then I reached to a point where it was telling me it was too complicated to process. This is just a company but in my real file, there are three companies that end up consolidated. Everything in Access works more or less fine now but my only issue is getting these two tables out in Excel.

I still do not understand why I can not get it from Excel. I can get and refresh all my tables but these two, still can not even see them in power query editor... Does the strcuture of the table or queries have anything to do with import to Excel ?

THank you for your help.
M.

[mike60smart]

Which of the named queries are you trying to view in Excel?

[mike60smart]

Hi M

Your "qryTotalSum" Views in Excel as in screenshot attached.

[mbouaici@hotmail.com]

Hey Mike,

Just trying to get the ones named " MITCLI_TB_Non_Current_Assets" and "MITCLI_TB_Non_Current_Liabilities"

The below is what I need and the second print screen is what I see when importing.

Thanks Mate for your help and time.

[mbouaici@hotmail.com]

Hey Mike,

The Qry Total sum works but it is created just to get the total per month and then populate the two others depending on the total of the month. I do not need this one. what kills me is the SQL in th eone I need is not that complicated. I am sure there is way more developped by anyone on this forum

Thank you

[mike60smart]

Hi

Those 2 queries do not show up when trying to Get Data using Excel.

However, using the Export To process from Access they show up just fine.

As shown in screenshot.

[mbouaici@hotmail.com]

Hey Mike,

I noticed you can export them but for me the goal was to open Excel once my month is in Access and everything refreshes in Excel. what I do not get is the fact that I created a query of the query thinking I then just have numbers but same thing happens, does not load even if it is just en empty query with just a select...

I am lost honestly...

Thank you for your time on a Sunday.
M.

[madpiet]

If you're just analyzing this in Excel, you don't need Access at all. Just use PowerQuery to transform the table if you need to and then append all the separate tables to a single final table in a new workbook. Then if you put all the Excel files in the same folder, you can use a folder source to loop over the files in PowerQuery and do that. Then all you have to do is hit Refresh and you're off to the races. I guess if you really were dead set on appending to Access, you could, but why?

To answer your question, though, once you have ONE table with all the data in it, this is just stupid easy. You just have a table of (ID, No, Amount, Company, and then maybe a date. Then you can (if you want) create a calendar table and add grouping columns. It's typical Data warehouse design - so scales well.

Here's the PowerQuery to get all the data out of Access and union all that together:

let    Source = Access.Database(File.Contents("C:\Users\User\OneDrive\Desktop\Test.accdb"), [CreateNavigationProperties=true]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Table")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Item], "3_Entities_")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"ID", "No", "Amount", "Month", "Year", "Period", "Company"}, {"Data.ID", "Data.No", "Data.Amount", "Data.Month", "Data.Year", "Data.Period", "Data.Company"})
in
    #"Expanded Data"

(And it looks like you have [Month-Year] as column names... you can use PowerQuery to unpivot all that too.)