Hi Access world,
I have a complicated query (well it is to me!
) that I have written with some help along the way. I want to stream line it, and I am quite new to access, been using it for about 3 months or so.
It's purpose is to provide an overview of information in continuous forms, that displays pre advised deliveries that are due to arrive, it then unions this against another query that displays deliveries that have arrived and are in the warehouse.
These two queries that are union'd take their information from other queries and I believe the correct term for this is 'stacked queries' ?
The code for my query is below. Takes about 2.5 secs to load if i count out loud. How do I measure this through access?
Also, on the form itself, I have a few textboxes that have Dlookup's to just look up info, and a few IIf statements.
Also, I have some code for the On Load event of the form, which doesn't seem to work 100% of the time (it positions the form to a certain position.) If this fails, all the Dlookup's and IIf statements fail, and show the #Name? error. So I'm assuming this is to do with the VBA not working, as when I load the form on its own, I get the error 'The expression On Load you entered as the event property setting produced the following error: Return without GoSub.' Is this a question for another topic?
Any help, advice I received, I am very grateful in advance.
Query Code:
I have a complicated query (well it is to me!
) that I have written with some help along the way. I want to stream line it, and I am quite new to access, been using it for about 3 months or so.It's purpose is to provide an overview of information in continuous forms, that displays pre advised deliveries that are due to arrive, it then unions this against another query that displays deliveries that have arrived and are in the warehouse.
These two queries that are union'd take their information from other queries and I believe the correct term for this is 'stacked queries' ?
The code for my query is below. Takes about 2.5 secs to load if i count out loud. How do I measure this through access?
Also, on the form itself, I have a few textboxes that have Dlookup's to just look up info, and a few IIf statements.
Also, I have some code for the On Load event of the form, which doesn't seem to work 100% of the time (it positions the form to a certain position.) If this fails, all the Dlookup's and IIf statements fail, and show the #Name? error. So I'm assuming this is to do with the VBA not working, as when I load the form on its own, I get the error 'The expression On Load you entered as the event property setting produced the following error: Return without GoSub.' Is this a question for another topic?
Any help, advice I received, I am very grateful in advance.
Query Code:
Code:
SELECT [AGLGRNextended].[AGL99], [AGLGRNextended].CODE,[AGLGRNextended].[AGLGRN],[AGLGRNextended].[GS1SKU],[AGLGRNextended].[Balance], [AGLGRNextended].[GRNID],[AGLGRNextended].[GRN], [AGLGRNextended].[CUSTOMERPO], [AGLGRNextended].[SUPPLIERPO], [AGLGRNextended].[COMPANYID], [AGLGRNextended].[SCANINID], [AGLGRNextended].[Packages],[AGLGRNextended].[Location], [AGLGRNextended].[ProdAGLGRN], [AGLGRNextended].[GROSSWGT], [AGLGRNextended].[NETWGT], [AGLGRNextended].[CBM],[AGLGRNextended].[PROD], [AGLGRNextended].[BBD], [AGLGRNextended].[GRNDATE], ([tblPRODUCT].[productSUPPLIERSKU]&" - "&[tblPRODUCT].[productDESCRIPTION]) AS Description, tblPRODUCT.[productSUPPLIERNAME], tblAGLGRN.[aglgrnCREATED], tblSCANIN.[scaninUSER], tblSCANIN.[scaninUSERTIME], tblUser.[UserName], tblUser.[UserFIRSTNAME], Val(AGLGRNextended.[PROD]) AS PROD1, Format(tblAGLGRN.aglgrnDATEEXPECTED,'Short Date') AS DateExpected, tblAGLGRN.aglgrnUSER AS AGLGRNUSER,tblUser.UserFIRSTNAME AS AGLGRNFIRSTNAMEUSER, 2 AS DeliveryType, tblAGLGRN.aglgrnADVISEDQTY
FROM ((((((AGLGRNextended LEFT JOIN tblPRODUCT ON [tblPRODUCT].[productGS1SKU]=[AGLGRNextended].[GS1SKU]) LEFT JOIN tblAGLGRN ON [AGLGRNextended].[AGLGRN]=Format(tblAGLGRN.AGLGRN,'00000')) LEFT JOIN tblSCANIN ON [tblSCANIN].[ID]=[AGLGRNextended].[SCANINID]) LEFT JOIN tblUser ON [tblUser].[UserLogin]=[tblAGLGRN].[aglgrnUSER])) LEFT JOIN SupplierAndCustomerRefs ON Format([SupplierAndCustomerRefs].[aglgrnID],'00000') = Format(tblAGLGRN.AGLGRN,'00000')) LEFT JOIN OnHoldSSCCqry ON Format([OnHoldSSCCqry].[aglGRNID],'00000') = [AGLGRNextended].[AGLGRN]
UNION SELECT Null AS Field2, tblCOMPANYDIRECTORY.companyCODE AS Field3, Format(tblAGLGRN.AGLGRN,'00000') AS AGLGRN, Null AS Field4, Null AS Field5, Null AS Field6, Null AS Field7, tblAGLGRN.aglgrnCUSTOMERREF,tblAGLGRN.aglgrnSUPPLIERREF,tblAGLGRN.companyID, Null AS Field9, PackageCountqry.PackageQty AS Packages, Null AS Field10, Null AS Field11, Null AS Field12, Null AS Field13, Null AS Field14, Null AS Field15, Null AS Field16, Null AS Field17, Null AS Field18, tblAGLGRN.aglgrnSUPPLIER AS Field19, tblAGLGRN.aglgrnCREATED AS Field20, Null AS Field21, Null AS Field22, Null AS Field23, Null AS Field24, Null AS Field25, tblAGLGRN.aglgrnDATEEXPECTED AS DateExpected, tblAGLGRN.aglgrnUSER AS Field26, tblUser.UserFIRSTNAME AS AGLGRNFIRSTNAMEUSER, 1 AS DeliveryType, tblAGLGRN.aglgrnADVISEDQTY
FROM (((((tblAGLGRN LEFT JOIN GS1DECODEextended ON Format(tblAGLGRN.AGLGRN,'00000') = Format(GS1DECODEextended.[AGL GRN],'00000')) LEFT JOIN tblPACKAGES ON Format(tblAGLGRN.AGLGRN,'00000') = Format(tblPACKAGES.aglgrnID,'00000')) LEFT JOIN PackageCountqry ON Format(tblAGLGRN.AGLGRN,'00000') = Format(PackageCountqry.AGLGRNPACKAGE,'00000')) LEFT JOIN tblUser ON tblUser.UserLogin = tblAGLGRN.aglgrnUSER) LEFT JOIN tblCOMPANYDIRECTORY ON tblAGLGRN.companyID = tblCOMPANYDIRECTORY.ID) LEFT JOIN SupplierAndCustomerRefs ON Format([SupplierAndCustomerRefs].[aglgrnID],'00000') = Format(tblAGLGRN.AGLGRN,'00000')
WHERE Format(tblAGLGRN.AGLGRN,'00000') <> Format(GS1DECODEextended.[AGL GRN],'00000')
ORDER BY AGLGRNextended.[AGLGRN] DESC;
Last edited by a moderator:
