'Query is too complex' error

[student]

I have multiple queries brought together in a Union query (kudos to TexanInParis for assistance on that one) and instead of cutting through ways to import into Excel, created a simple query on the union and link to that from Excel instead.

The issue is, when I query on the Union, I get the 'Query is too complex' error. If I take the entire Union:

SELECT [week_sub-tot].*, [week_sub-tot].dept
FROM [week_sub-tot]
WHERE ((([week_sub-tot].dept)="40"));

...the query runs fine. However, if I try to take only certain fields:

SELECT [week_sub-tot].movement_date, [week_sub-tot].[Department Name], [week_sub-tot].prod_type, [week_sub-tot].product, [week_sub-tot].long_description, [week_sub-tot].[sheets/box], [week_sub-tot].Seq, [week_sub-tot].produced, [week_sub-tot].sheets, [week_sub-tot].unit_time, [week_sub-tot].total_time
FROM [week_sub-tot]
WHERE ((([week_sub-tot].dept)="40"));

...the error occurs.

Am I simply missing the point here? All the articles on this error that I can find relate to there being either a huge number of queries/records or more commonly point towards there being too many OR / AND conditions. I thought it might be nice and simple to just cut down on the number of fields being pulled across, but evidently not.

Any pointers would be appreciated.

Edit: Running Access 2007 in Win7

 

[vbaInet]

I don't want to take you back to something that has already been solved but why a union query? And how did you implement it? Maybe the URL of the thread will help.

I don't see anything obviously wrong with the SQL as it is.

 

[student]

The union was used as a way to provide subtotals per product type in the results of queries shoing production stats. thread is here:

http://www.access-programmers.co.uk/forums/showthread.php?t=217577

Final coding for union query is:

SELECT qryDetails.*, "" As Seq
FROM qryDetails
UNION ALL SELECT Null As movement_Date, Null As sales_Date, qryTotals.dept, "" As [Department Name], qryTotals.prod_type, "" As product, "" As long_description, 
"" As sub_grp, "" As alpha_code, "" As unit_code, "" As [sheets/box], qryTotals.TotalProduced, qryTotals.TotalSheets, qryTotals.TotalSales_Qty, qryTotals.TotalSheets_Sold,
qryTotals.TotalSales_Val, qryTotals.TotalCost, "" As instructions1, "" As instructions2, "" As instructions3, "" As instructions4, Null As unit_time, qryTotals.TotalTime, "Totals" As Seq
FROM qryTotals
ORDER BY prod_type, Seq;

I'm re-visiting the outputs to provide different data to different departments without confusing them (sales folk, eh? ;-) )

 

[vbaInet]

I haven't read the whole thread but based on your initial post you mentioned creating totals and sub-totals. Why didn't you just use a Report?

 

[student]

Honestly speaking, I went with the suggested course of action without really knowing of any better alternative. I also wanted to set up a customisable sheet, send round to the various people involved and let them get on with it with no further input from me, and them having no access to the DB. Would a report have been a better way of doing this for the users? Sorry for simplistic answers!

 

[vbaInet]

I would have recommended using a report because you can easily group by fields, perform sub-totals and grand totals too. Then you can filter the report based on the group and export the report to excel sheets.

What version of Access are you using?

Don't scrap your current method but try out this method and see what you come up with. These links should give you some pointers:

http://office.microsoft.com/en-us/access-help/summing-in-reports-HA001122444.aspx
http://www.databasedev.co.uk/access2007_summing.html

 

[student]

Thanks for response and sorry for delay in reply. Access 2007, and Win7. It would appear it's quite a common error in 2007 but nobody seems to mention it for this sort of issue.

I followed your advice on reports for another output that I need for someone else (I trust them to access my DB without destroying it!) but not sure on this occasion. Probably me being paranoid & not having the necessary security skills tbh.

Cheers again,

R

 

[vbaInet]

Upload a stripped down version of your db and tell me all the queries mentioned above.

Also, upload a xls doc just showing what are you trying to achieve.

 

[micheal_1983]

Hi ,

Please i have the same error message

this is my code

SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Four_Button_Keypad)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Four_Button_Keypad)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="4 Button Keypad"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Antenna_Board_Holder)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Antenna_Board_Holder)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Antenna Board Holder"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Back_Cover)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Back_Cover)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Back Cover"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Back_Cover_Screw)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Back_Cover_Screw)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Back Cover Screw"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Base_Station_Connector)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Base_Station_Connector)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Base Station Connector"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Big_Zebra_Rubber)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Big_Zebra_Rubber)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Big Zebra Rubber"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Buttom_Case)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Buttom_Case)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Buttom Case"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Cables_Cover)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Cables_Cover)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Cables Cover"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Charger)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Charger)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Charger"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Charger_Extension_Cable)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Charger_Extension_Cable)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Charger Extension Cable"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Chip_Card_Reader)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Chip_Card_Reader)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Chip Card Reader"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Chip_Card_Reader_Shield)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Chip_Card_Reader_Shield)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Chip Card Reader Shield"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Contactless_Coil)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Contactless_Coil)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Contactless Coil"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Contactless_Controller_Board)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.Contactless_Controller_Board)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="Contactless Controller Board"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.External_Battery)
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.External_Battery)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="External Battery"))
union
SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , 
tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.External_Battery_Connector_(inside))
FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails
WHERE (((tblTestPoints.External_Battery_Connector_(inside))="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and 
tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name 
and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & 
tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] )
GROUP BY  tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & 
tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, 
tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description
HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="External Battery Connector (inside)"));

i have access 2016 and windows 10

 

[JHB]

Have you tried to split it up in sections and run these one by one?
Spit it where you've the union, so the first section is the below.

SELECT tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] AS Expr1, tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL ,tblModelCost.Description , tblModelCost.part_number, tblModelCost.Cost, Count(tblTestPoints.Four_Button_Keypad) FROM tblModelCost, tblTestPoints,tblReceiptVoucherDetails WHERE (((tblTestPoints.Four_Button_Keypad)="Replace") AND ((tblModelCost.company_name)=[tblTestPoints]![COMPANY_NAME])and tblReceiptVoucherDetails.id = tblTestPoints.customer_id and tblReceiptVoucherDetails.company_name = tblModelCost.company_name and tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] = [Forms]![rptReports]![txt9] ) GROUP BY tblReceiptVoucherDetails.[COMPANY_NAME] & " " & tblReceiptVoucherDetails.[Date] & " " & tblReceiptVoucherDetails.[Quantity] , tblTestPoints.COMPANY_NAME, tblTestPoints.MODEL, tblModelCost.part_number, tblModelCost.Cost, tblModelCost.Model, tblModelCost.Description HAVING (((tblModelCost.Model)=[tblTestPoints]![MODEL]) AND ((tblModelCost.Description)="4 Button Keypad"))

 

[micheal_1983]

Thanks for your replay

i have solved the issue by making 4 query
each one must have at most 16 union

what i do is

SELECT *
FROM qryUnion1
union all
SELECT *
FROM qryUnion2
union all
SELECT *
FROM qryUnion3
union all
SELECT *
FROM qryUnion4;