Chicken and Egg situation, which comes first? (1 Viewer)

joeyd11ph

Registered User.
Local time
Today, 02:43
Joined
Jul 16, 2019
Messages
38
Thanks for cleaning up tip Doc, It will certainly make my code easy to follow and debug...

Code:
SELECT 
    tblType.Type, 
    [COLOR="Red"]IIf([qrySummaryPrevWeekly].[Vehicle Received Previous Week] Is Null,0,   
        [qrySummaryPrevWeekly].[Vehicle Received Previous Week]) AS [Received PW][/COLOR], 
    IIf([qrySummaryPrevWeekly].[Vehicles Repaired Previous Week] Is Null,0,
        [qrySummaryPrevWeekly].[Vehicles Repaired Previous Week]) AS [Repaired PW], 
    [Bal for Nxt Wk]-[Repaired last Wk] AS [Bal Prev Week], 
    IIf([qrySummaryCurWeekly].[Vehicle Received Current week] Is Null,0,
        [qrySummaryCurWeekly].[Vehicle Received Current week]) AS [Received CW], 
    IIf([qrySummaryCurWeekly].[Vehicle Repaired current week] Is Null,0,
        [qrySummaryCurWeekly].[Vehicle Repaired current week]) AS [Repaired CW], 
    qrySummaryCurWeekly.[Bal This Wk], 
    [Find duplicates for Job Card Count].[Repaired last Wk], [Bal This Wk]+[Bal Prev 
        Week] AS [Bal for Nxt Wk]
FROM 
    (((tblType LEFT JOIN [Find duplicates for Job Card] ON tblType.Type = [Find 
        duplicates for Job Card].Type) 
     LEFT JOIN qrySummaryCurWeekly ON tblType.Type = qrySummaryCurWeekly.Type) 
     LEFT JOIN qrySummaryPrevWeekly ON tblType.Type = qrySummaryPrevWeekly.Type) 
     LEFT JOIN [Find duplicates for Job Card Count] ON tblType.Type = [Find duplicates 
        for Job Card Count].Type
GROUP BY tblType.Type, 
    IIf([qrySummaryPrevWeekly].[Vehicle Received Previous Week] Is Null,0,
        [qrySummaryPrevWeekly].[Vehicle Received Previous Week]), 
    IIf([qrySummaryPrevWeekly].[Vehicles Repaired Previous Week] Is Null,0,
        [qrySummaryPrevWeekly].[Vehicles Repaired Previous Week]), 
    [Bal for Nxt Wk]-[Repaired last Wk], 
    IIf([qrySummaryCurWeekly].[Vehicle Received Current week] Is Null,0,
        [qrySummaryCurWeekly].[Vehicle Received Current week]), 
    IIf([qrySummaryCurWeekly].[Vehicle Repaired current week] Is Null,0,
        [qrySummaryCurWeekly].[Vehicle Repaired current week]), 
    qrySummaryCurWeekly.[Bal This Wk], 
    [Find duplicates for Job Card Count].[Repaired last Wk], 
    [Bal This Wk]+[Bal Prev Week];

Where to begin? OK, suggestion #1: Shorten this code with the NZ function:
Code:
IIf([qrySummaryPrevWeekly].[Vehicle Received Previous Week] Is Null,0,   
        [qrySummaryPrevWeekly].[Vehicle Received Previous Week]) AS [Received PW]

becomes

Code:
NZ(qrySummaryPrevWeekly][Vehicle Received Previous Week], 0 ) AS [Received PW]

and you have at least four other cases where the same thing would work. And it is less to type and easier to read.

Then, you have IIF's in a GROUP BY statement. I suppose that would work but if those are already fields with aliases in your query, just group by the Alias name.

I think you MIGHT benefit from using a "divide and conquer" approach to this. Build a query that gathers everything together, tests your fields that could be Null to supply 0 where appropriate, and supply aliases for everything that doesn't already have a unique name. THEN build a second query that looks at the result and does your computed fields and ordering - where you don't need ANY IIF code. (Or NZ code, for that matter.)

I see you have computed fields in your ORDER BY clause. I'm not going to say you can't do an order-by on a computed field, but it is so much harder to read as you wrote it.

One last "gotcha" is that "TYPE" is a reserved word so isn't really a good choice for a field name. Because all Access objects (fields, forms, reports, tables, queries...) have an inherent "TYPE" property, a reference such as tblType.Type could mean the field named Type OR it could mean the type of object that tblType happens to be. Access gets confused in such situations and often gives strange answers.

As to why the circular reference? I would suggest splitting out the data gathering from the data ordering and see which one of them retains the error. That would help you narrow down the search.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:43
Joined
Oct 29, 2018
Messages
21,358
Hi Joey. I hope Doc was able to give you what you were looking for. Something must have happened to the website yesterday, because I couldn't download your file last night.
 

joeyd11ph

Registered User.
Local time
Today, 02:43
Joined
Jul 16, 2019
Messages
38
HI DBguy

Yes me too cant access the forum yesterday.

I still have the same problem.

thanks

joey
 

joeyd11ph

Registered User.
Local time
Today, 02:43
Joined
Jul 16, 2019
Messages
38
To All Gurus

Finally, I found out that my test data is corrupted.

Thanks to all, Cheers

Joey
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:43
Joined
Oct 29, 2018
Messages
21,358
To All Gurus

Finally, I found out that my test data is corrupted.

Thanks to all, Cheers

Joey
Hi Joey. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom