The error..."The query cannot be completed. Either the size of the query result is larger than the maximum size of the database (2GB), or there is not enough temporary storage space on the disk to store the query result." I have tried to run it on a 3 GB ram laptop and a 4 GB ram desktop both running windows 7 and the database is only 16.3mbs.
I think I know what is wrong...the problem is I am not sure how to fix it. I saw this coming but thought it wouldn't happen before we archived old data in another year. I have a union query that combines another query and a table for two types of payroll into one query and then another query that runs the equations off the union set of info. The problem is the info that the union query is running through is ALL the info in the database. I have a date criteria in the last query that pulls info from the union query, but I am pretty sure at this point that info needs to be passed down to the union query also. Problem is I can see the problem but haven't a clue how to fix it. By the way it has worked perfectly and as intended for the last year...it has been a year and 2 weeks since it was implemented and it quit.
The first query TEST...I know naming convention but by the time I was sure it would work it was already too embeded with this name for me to change
SELECT Driverqry.intDRIVER_ID, BOLdriver.BOL, BOLdriver.milecode, BOLdriver.dtdate, testchrgcd.Charge, Driverqry.nmbDRPAYRATE, [charge]*[nmbdrpayrate] AS Expr1
FROM tblBOLS RIGHT JOIN ((Driverqry INNER JOIN BOLdriver ON Driverqry.intDRIVER_ID = BOLdriver.Driverid) INNER JOIN testchrgcd ON BOLdriver.milecode = testchrgcd.milecode) ON tblBOLS.lngBOLNUMBER = BOLdriver.BOL
ORDER BY BOLdriver.dtdate;
the union query called PAYROLLUNIONQRY
SELECT
TEST.INTDRIVER_ID, TEST.BOL AS [BOL STOCK NUMBER],"BOL" AS PAYTYPE, TEST.MILECODE, TEST.dtdate, TEST.CHARGE,TEST.NMBDRPAYRATE,TEST.EXPR1 FROM TEST
UNION SELECT
DRIVERSTOCKQRY.INTDRIVER_ID,DRIVERSTOCKQRY.STOCK AS [BOL STOCK NUMBER],"STOCK" AS PAYTYPE,DRIVERSTOCKQRY.MILEAGECODE,DRIVERSTOCKQRY.DTDATE,
DRIVERSTOCKQRY.CHARGE,DRIVERSTOCKQRY.NMBDRPAYRATE,DRIVERSTOCKQRY.EXPR1 FROM DRIVERSTOCKQRY
ORDER BY INTDRIVER_ID;
the query that runs off the union query named PAYROLLUNIONQRY Query1
SELECT PAYROLLUNIONQRY.INTDRIVER_ID, PAYROLLUNIONQRY.[BOL STOCK NUMBER], PAYROLLUNIONQRY.PAYTYPE, PAYROLLUNIONQRY.MILECODE, PAYROLLUNIONQRY.dtdate AS Expr2, PAYROLLUNIONQRY.CHARGE, PAYROLLUNIONQRY.NMBDRPAYRATE, PAYROLLUNIONQRY.EXPR1
FROM PAYROLLUNIONQRY
WHERE (((PAYROLLUNIONQRY.dtdate) Between [Enter first Date] And [enter ending date]));
Then all this great info is populated into a pleasing form that one retrieves through a button.
Any help is appreciated, but your help may elicit more questions, please have patience with me. Thanks
I think I know what is wrong...the problem is I am not sure how to fix it. I saw this coming but thought it wouldn't happen before we archived old data in another year. I have a union query that combines another query and a table for two types of payroll into one query and then another query that runs the equations off the union set of info. The problem is the info that the union query is running through is ALL the info in the database. I have a date criteria in the last query that pulls info from the union query, but I am pretty sure at this point that info needs to be passed down to the union query also. Problem is I can see the problem but haven't a clue how to fix it. By the way it has worked perfectly and as intended for the last year...it has been a year and 2 weeks since it was implemented and it quit.
The first query TEST...I know naming convention but by the time I was sure it would work it was already too embeded with this name for me to change
SELECT Driverqry.intDRIVER_ID, BOLdriver.BOL, BOLdriver.milecode, BOLdriver.dtdate, testchrgcd.Charge, Driverqry.nmbDRPAYRATE, [charge]*[nmbdrpayrate] AS Expr1
FROM tblBOLS RIGHT JOIN ((Driverqry INNER JOIN BOLdriver ON Driverqry.intDRIVER_ID = BOLdriver.Driverid) INNER JOIN testchrgcd ON BOLdriver.milecode = testchrgcd.milecode) ON tblBOLS.lngBOLNUMBER = BOLdriver.BOL
ORDER BY BOLdriver.dtdate;
the union query called PAYROLLUNIONQRY
SELECT
TEST.INTDRIVER_ID, TEST.BOL AS [BOL STOCK NUMBER],"BOL" AS PAYTYPE, TEST.MILECODE, TEST.dtdate, TEST.CHARGE,TEST.NMBDRPAYRATE,TEST.EXPR1 FROM TEST
UNION SELECT
DRIVERSTOCKQRY.INTDRIVER_ID,DRIVERSTOCKQRY.STOCK AS [BOL STOCK NUMBER],"STOCK" AS PAYTYPE,DRIVERSTOCKQRY.MILEAGECODE,DRIVERSTOCKQRY.DTDATE,
DRIVERSTOCKQRY.CHARGE,DRIVERSTOCKQRY.NMBDRPAYRATE,DRIVERSTOCKQRY.EXPR1 FROM DRIVERSTOCKQRY
ORDER BY INTDRIVER_ID;
the query that runs off the union query named PAYROLLUNIONQRY Query1
SELECT PAYROLLUNIONQRY.INTDRIVER_ID, PAYROLLUNIONQRY.[BOL STOCK NUMBER], PAYROLLUNIONQRY.PAYTYPE, PAYROLLUNIONQRY.MILECODE, PAYROLLUNIONQRY.dtdate AS Expr2, PAYROLLUNIONQRY.CHARGE, PAYROLLUNIONQRY.NMBDRPAYRATE, PAYROLLUNIONQRY.EXPR1
FROM PAYROLLUNIONQRY
WHERE (((PAYROLLUNIONQRY.dtdate) Between [Enter first Date] And [enter ending date]));
Then all this great info is populated into a pleasing form that one retrieves through a button.
Any help is appreciated, but your help may elicit more questions, please have patience with me. Thanks
