Access report "Overflow" error on export to HTML (PDF works fine) (2 Viewers)

[CJ_London]

can't really help without seeing the data/queries - and not clear what your final report is supposed to look like with 6 subreports.

Your sql would appear to not have enough joins

Would also help if you aliased your queries and not use spaces in table names - makes the sql much easier to read. For example

SELECT * FROM 2024_ORG_ST_Summary_ABCD AS M INNER JOIN Sub_Report_qrySource1 AS R1 ON M.StateName = R1.StateName AND M.TstNum = R1.TstNum

 

[ramendog]

can't really help without seeing the data/queries - and not clear what your final report is supposed to look like with 6 subreports.

Your sql would appear to not have enough joins

Would also help if you aliased your queries and not use spaces in table names - makes the sql much easier to read. For example

SELECT * FROM 2024_ORG_ST_Summary_ABCD AS M INNER JOIN Sub_Report_qrySource1 AS R1 ON M.StateName = R1.StateName AND M.TstNum = R1.TstNum

Thank you. I'm trying to figure out the syntax myself, but having multiple inner join statements is tricky. I'm trying to follow the answer outlined on this page (sorry, I don't have permission to post links), stackoverflowdotcom /questions/7854969/sql-multiple-join-statement

It would be the same fields as above [StateName] and [TstNum] for each of the additional five tables I want to add to the record source. So for two tables I think it would be something like:

SELECT * FROM ((2024_ORG_ST_Summary_ABCD AS M INNER JOIN Sub_Report_qrySource1 AS R1 ON (M.StateName = R1.StateName AND M.TstNum = R1.TstNum)) INNER JOIN Sub_Report_qrySource2 AS R2 ON (M.StateName = R2.StateName AND M.TstNum = R2.TstNum)))

but I get the error 'JOIN statement not supported'

 

[CJ_London]

Build it in the QBE to get the bracketing correct

Based on your original query you don’t need tstnum in your join as statename is unique

 

[ramendog]

I was able to use your suggestion to build the SQL query to add all the tables to the report source. However this just seemed to confirm for me that the method used in report #1 will not work when there is more than one sub-report in the details section of the main report because of the grouping needed.

It seems like I'm now back at square one with this second report. Interestingly the "Overflow" error occurs right around the same page number (150) as the first report even though this one has six sub reports. I don't know access well enough to speculate about the source of the error (beyond it has something to do with an integer value). I've attached a new db that has both reports. Report 'rpt2024_TstNum_EFG_summary_html_export' is the one I'm attempting to troubleshoot.

 

[CJ_London]

A quick review - now I can see what you are trying to do, I think the solution is to use a union query - you will need 2, 3 or 4 for each table. one for the data, one for the total and perhaps one for the heading e.g. 'Grouping Title for Sub-report 1:'. (Which seems pretty meaningless to me, let me know if it means something to you or it's just a placeholder for something more meaningful. You may also need one for the column headings as well since they repeat. The query should only select the fields you require and probably calculate the values there rather than in the report (e.g. IIf([LMNO_TY]='61','Urban County','Metro City') AS GeoType

I assume you have a reason for having 6 virtually identical tables, but would be a lot easier as a single table - which is what the union query will achieve

 

[CJ_London]

As I thought, would have been a lot easier with a single table with additional columns to manage grouping at lower levels. As it is, this is the sql to the report recordsource called qry2024_TstNum_EFG_summary:

SELECT DISTINCT [State Name], TstNum, 1 as Grp, 0 as lvl, 0 as lvl1, "Grouping Title for Sub-report 1:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from ABCD_E_with_coterminus_EFG
UNION SELECT DISTINCT [State Name], TstNum, 1, 0, 1 ,null, "IDCode", "Jurisdiction Name", "Geography Type", "EFG Amount" FROM ABCD_E_with_coterminus_EFG
UNION SELECT [State Name], TstNum, 1, 1, 0, null, IIf([LMNOKEY]='339011',[LMNOKEY] & "*",[LMNOKEY]), LMNONAME, IIf([LMNO_TY]='61','Urban County','Metro City'), "$" & EFGAmount from ABCD_E_with_coterminus_EFG
UNION SELECT [State Name], TstNum, 1, 2, 0, null, "Total",null, null, "$" & sum(efgAmount) from ABCD_E_with_coterminus_EFG GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 1, 2, 1, null, null, null, null, null from ABCD_E_with_coterminus_EFG

UNION SELECT DISTINCT [State Name], TstNum, 2 as Grp, 0 as lvl, 0 as lvl1, "Grouping Title for Sub-report 2:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from ABCD_with_EFG_bounced_to_ST
UNION SELECT DISTINCT [State Name], TstNum, 2, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM ABCD_with_EFG_bounced_to_ST
UNION SELECT [State Name], TstNum, 2, 1 ,0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from ABCD_with_EFG_bounced_to_ST
UNION SELECT [State Name], TstNum, 2, 2, 0, null, "Total", null, null,"$" & sum([efg Amount]) from ABCD_with_EFG_bounced_to_ST GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 2, 2, 1, null, null, null, null, null from ABCD_with_EFG_bounced_to_ST

UNION SELECT DISTINCT [State Name], TstNum, 3 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 3:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from ABCD_UC_EFG_bounce_to_state
UNION SELECT DISTINCT [State Name], TstNum, 3, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM ABCD_UC_EFG_bounce_to_state
UNION SELECT [State Name], TstNum, 3, 1, 0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from ABCD_UC_EFG_bounce_to_state
UNION SELECT [State Name], TstNum,3, 2, 0, null, "Total", null, null, "$" & sum([efg Amount]) from ABCD_UC_EFG_bounce_to_state GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 3, 2, 1, null, null, null, null, null from ABCD_UC_EFG_bounce_to_state

UNION SELECT DISTINCT [State Name], TstNum, 4 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 4:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from NE_counties_with_EFG_bounce
UNION SELECT DISTINCT [State Name], TstNum, 4, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM NE_counties_with_EFG_bounce
UNION SELECT [State Name], TstNum, 4, 1, 0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from NE_counties_with_EFG_bounce
UNION SELECT [State Name], TstNum,4, 2, 0, null, "Total", null, null,"$" & sum([efg Amount]) from NE_counties_with_EFG_bounce GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 4, 2, 1, null, null, null, null, null from NE_counties_with_EFG_bounce

UNION SELECT DISTINCT [State Name], TstNum, 5 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 5:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from LMNO_NE_with_EFG_Bounce
UNION SELECT DISTINCT [State Name], TstNum, 5, 0, 1,null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM LMNO_NE_with_EFG_Bounce
UNION SELECT [State Name], TstNum, 5, 1, 0, null, IIf([NameKEY]='9999',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFGAmount] from LMNO_NE_with_EFG_Bounce
UNION SELECT [State Name], TstNum,5, 2, 0, null, "Total", null, null, "$" & sum([efgAmount]) from LMNO_NE_with_EFG_Bounce GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 5, 2, 1, null, null, null, null, null from LMNO_NE_with_EFG_Bounce

UNION SELECT DISTINCT [State Name], TstNum, 6 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 6:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from LMNO_UC_with_EFG_to_UC
UNION SELECT DISTINCT [State Name], TstNum, 6, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM LMNO_UC_with_EFG_to_UC
UNION SELECT [State Name], TstNum, 6, 1, 0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from LMNO_UC_with_EFG_to_UC
UNION SELECT [State Name], TstNum, 6, 2, 0, null, "Total", null, null, "$" & sum([efg Amount]) from LMNO_UC_with_EFG_to_UC GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 6, 2, 1, null, null, null, null, null from LMNO_UC_with_EFG_to_UC;

I've shown it here because if this was displayed with the Monaco editor, it would have been half a mile long!

this is what each line does:
1. Define the grouping title: UNION SELECT DISTINCT [State Name], TstNum, 6 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 6:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from LMNO_UC_with_EFG_to_UC
2. Define the column headings: UNION SELECT DISTINCT [State Name], TstNum, 6, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM LMNO_UC_with_EFG_to_UC
3. Get the data: UNION SELECT [State Name], TstNum, 6, 1, 0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from LMNO_UC_with_EFG_to_UC
4. Determine the totals: UNION SELECT [State Name], TstNum, 6, 2, 0, null, "Total", null, null, "$" & sum([efg Amount]) from LMNO_UC_with_EFG_to_UC GROUP BY [State Name], TstNum
5. Provide a blank line after total line: UNION SELECT DISTINCT [State Name], TstNum, 6, 2, 1, null, null, null, null, null from LMNO_UC_with_EFG_to_UC;

Grp=identifier for the source table
lvl =defines the order of records
lvl1=defines a sublevel within lvl

Pretty sure you could have a simpler union query to to combine the tables and additional fields and do lower levels of grouping and sorting, but I don't have the time to investigate further.

I've attached the output - 744 pages - and your file with the modified report - note there is some code to highlight the total lines, rather than the line you had (it won't appear in the html anyway - you would need to create a line of chars e.g. __________________________

I've also used conditional formatting for the column header and totals

You will need to check the validity of the data

Hope it meets your requirement

 

[ramendog]

As I thought, would have been a lot easier with a single table with additional columns to manage grouping at lower levels. As it is, this is the sql to the report recordsource called qry2024_TstNum_EFG_summary:

SELECT DISTINCT [State Name], TstNum, 1 as Grp, 0 as lvl, 0 as lvl1, "Grouping Title for Sub-report 1:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from ABCD_E_with_coterminus_EFG
UNION SELECT DISTINCT [State Name], TstNum, 1, 0, 1 ,null, "IDCode", "Jurisdiction Name", "Geography Type", "EFG Amount" FROM ABCD_E_with_coterminus_EFG
UNION SELECT [State Name], TstNum, 1, 1, 0, null, IIf([LMNOKEY]='339011',[LMNOKEY] & "*",[LMNOKEY]), LMNONAME, IIf([LMNO_TY]='61','Urban County','Metro City'), "$" & EFGAmount from ABCD_E_with_coterminus_EFG
UNION SELECT [State Name], TstNum, 1, 2, 0, null, "Total",null, null, "$" & sum(efgAmount) from ABCD_E_with_coterminus_EFG GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 1, 2, 1, null, null, null, null, null from ABCD_E_with_coterminus_EFG

UNION SELECT DISTINCT [State Name], TstNum, 2 as Grp, 0 as lvl, 0 as lvl1, "Grouping Title for Sub-report 2:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from ABCD_with_EFG_bounced_to_ST
UNION SELECT DISTINCT [State Name], TstNum, 2, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM ABCD_with_EFG_bounced_to_ST
UNION SELECT [State Name], TstNum, 2, 1 ,0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from ABCD_with_EFG_bounced_to_ST
UNION SELECT [State Name], TstNum, 2, 2, 0, null, "Total", null, null,"$" & sum([efg Amount]) from ABCD_with_EFG_bounced_to_ST GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 2, 2, 1, null, null, null, null, null from ABCD_with_EFG_bounced_to_ST

UNION SELECT DISTINCT [State Name], TstNum, 3 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 3:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from ABCD_UC_EFG_bounce_to_state
UNION SELECT DISTINCT [State Name], TstNum, 3, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM ABCD_UC_EFG_bounce_to_state
UNION SELECT [State Name], TstNum, 3, 1, 0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from ABCD_UC_EFG_bounce_to_state
UNION SELECT [State Name], TstNum,3, 2, 0, null, "Total", null, null, "$" & sum([efg Amount]) from ABCD_UC_EFG_bounce_to_state GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 3, 2, 1, null, null, null, null, null from ABCD_UC_EFG_bounce_to_state

UNION SELECT DISTINCT [State Name], TstNum, 4 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 4:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from NE_counties_with_EFG_bounce
UNION SELECT DISTINCT [State Name], TstNum, 4, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM NE_counties_with_EFG_bounce
UNION SELECT [State Name], TstNum, 4, 1, 0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from NE_counties_with_EFG_bounce
UNION SELECT [State Name], TstNum,4, 2, 0, null, "Total", null, null,"$" & sum([efg Amount]) from NE_counties_with_EFG_bounce GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 4, 2, 1, null, null, null, null, null from NE_counties_with_EFG_bounce

UNION SELECT DISTINCT [State Name], TstNum, 5 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 5:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from LMNO_NE_with_EFG_Bounce
UNION SELECT DISTINCT [State Name], TstNum, 5, 0, 1,null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM LMNO_NE_with_EFG_Bounce
UNION SELECT [State Name], TstNum, 5, 1, 0, null, IIf([NameKEY]='9999',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFGAmount] from LMNO_NE_with_EFG_Bounce
UNION SELECT [State Name], TstNum,5, 2, 0, null, "Total", null, null, "$" & sum([efgAmount]) from LMNO_NE_with_EFG_Bounce GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 5, 2, 1, null, null, null, null, null from LMNO_NE_with_EFG_Bounce

UNION SELECT DISTINCT [State Name], TstNum, 6 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 6:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from LMNO_UC_with_EFG_to_UC
UNION SELECT DISTINCT [State Name], TstNum, 6, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM LMNO_UC_with_EFG_to_UC
UNION SELECT [State Name], TstNum, 6, 1, 0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from LMNO_UC_with_EFG_to_UC
UNION SELECT [State Name], TstNum, 6, 2, 0, null, "Total", null, null, "$" & sum([efg Amount]) from LMNO_UC_with_EFG_to_UC GROUP BY [State Name], TstNum
UNION SELECT DISTINCT [State Name], TstNum, 6, 2, 1, null, null, null, null, null from LMNO_UC_with_EFG_to_UC;

I've shown it here because if this was displayed with the Monaco editor, it would have been half a mile long!

this is what each line does:
1. Define the grouping title: UNION SELECT DISTINCT [State Name], TstNum, 6 as Grp, 0 as lvl, 0, "Grouping Title for Sub-report 6:" as GrpHdr, null as IDCode, null as JName, null as GType, null as EFGAmt from LMNO_UC_with_EFG_to_UC
2. Define the column headings: UNION SELECT DISTINCT [State Name], TstNum, 6, 0, 1, null, "IDCode", "Jurisdiction Name", null, "EFG Amount" FROM LMNO_UC_with_EFG_to_UC
3. Get the data: UNION SELECT [State Name], TstNum, 6, 1, 0, null, IIf([NameKEY]='339011',[NameKEY] & "*",[NameKEY]), GeoNAME, null, "$" & [EFG Amount] from LMNO_UC_with_EFG_to_UC
4. Determine the totals: UNION SELECT [State Name], TstNum, 6, 2, 0, null, "Total", null, null, "$" & sum([efg Amount]) from LMNO_UC_with_EFG_to_UC GROUP BY [State Name], TstNum
5. Provide a blank line after total line: UNION SELECT DISTINCT [State Name], TstNum, 6, 2, 1, null, null, null, null, null from LMNO_UC_with_EFG_to_UC;

Grp=identifier for the source table
lvl =defines the order of records
lvl1=defines a sublevel within lvl

Pretty sure you could have a simpler union query to to combine the tables and additional fields and do lower levels of grouping and sorting, but I don't have the time to investigate further.

I've attached the output - 744 pages - and your file with the modified report - note there is some code to highlight the total lines, rather than the line you had (it won't appear in the html anyway - you would need to create a line of chars e.g. __________________________

I've also used conditional formatting for the column header and totals

You will need to check the validity of the data

Hope it meets your requirement

This is really helpful, thank you for taking the time to create this tutorial for getting rid of the sub-reports. It will take me some time to digest this new construction, exciting that I get to learn something new (to me) and useful. I will think about how to combine the data into a single table for additional reports that follow this same general structure (of multiple sub-reports).

 

[Gasman]

Use an append query to take each table and populate the new table.
When doing so, give the field that describes which table it came from it's correct value.

Job done.

However you might have issues with your autonumbers, as each table can have the same number, not so for the single table. So take that into consideration.