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

joeyd11ph

Registered User.
Local time
Today, 02:30
Joined
Jul 16, 2019
Messages
38
Hello Gurus,

Please enlighten me on the query to handle:

1st week
Field A = Field A + Field B

2nd week
Field A = Field A + Field B

and so on...

I have an error message of "Circular Reference caused by alias" upon running the query.

Please help with the right formula and syntax.

Thanks in advance to all.

Joey
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:30
Joined
Oct 29, 2018
Messages
21,357
Hi Joey. I don't understand the question. Can you please post the SQL statement for your query?
 

June7

AWF VIP
Local time
Today, 01:30
Joined
Mar 9, 2014
Messages
5,423
That calc really makes no sense to me.

Show sample data and desired output.
 

joeyd11ph

Registered User.
Local time
Today, 02:30
Joined
Jul 16, 2019
Messages
38
Hi DBGuy

Below is my SQL and attached is my screenshot and noted the expected result.

SELECT tblType.Type, IIf([qrySummaryPrevWeekly].[Vehicle Received Previous Week] Is Null,0,[qrySummaryPrevWeekly].[Vehicle Received Previous Week]) AS [Received PW], 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];


Appreciate very much your kind help.

Joey
 

Attachments

  • screen 2.PNG
    screen 2.PNG
    51.4 KB · Views: 102
  • Chicken and Egg.jpg
    Chicken and Egg.jpg
    85.6 KB · Views: 99
Last edited:

joeyd11ph

Registered User.
Local time
Today, 02:30
Joined
Jul 16, 2019
Messages
38
Hi Isaladogs

Yes it should be A = (C-D) + (E - D)

I will try your link.

Thanks much

cheers

Joey
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:30
Joined
Oct 29, 2018
Messages
21,357
Hi Isladogs and others

pardon me for my slowness in dealing with SQL.

Please I appreciate very much if anybody can translate the link given by Isladog

http://allenbrowne.com/subquery-01.html#AnotherRecord (Get the value in another record) in Access query only.

Thanks, much gurus...


joey
Hi Joey. Are you able to post a sample copy of your db with some test data? It might be easier if we could show you how to use the subquery.
 

joeyd11ph

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

attached is my DB
password for Admin is QAF Tafteesh

go to "REPORT" chooe "WEEKLY"


Thanks and appreciate very much your kind help



joey
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:30
Joined
Oct 29, 2018
Messages
21,357
Hi DBguy

attached is my DB
password for Admin is QAF Tafteesh

go to "REPORT" chooe "WEEKLY"

Thanks and appreciate very much your kind help

joey
Hi Joey. I don't see an attachment. Try to zip it up first before attaching.
 

joeyd11ph

Registered User.
Local time
Today, 02:30
Joined
Jul 16, 2019
Messages
38
hi BDguy

I'm having error uploading both Zip and ACCDB file

pls see attached screenshot

Can I send the link in my Cloud?

thanks

joey
 

Attachments

  • error upload DB.PNG
    error upload DB.PNG
    53.7 KB · Views: 100

theDBguy

I’m here to help
Staff member
Local time
Today, 02:30
Joined
Oct 29, 2018
Messages
21,357
hi BDguy

I'm having error uploading both Zip and ACCDB file

pls see attached screenshot

Can I send the link in my Cloud?

thanks

joey
Maybe there's a problem with the forum. You could post a link for a download, but I would still suggest trying to upload it here later when/if possible.
 

joeyd11ph

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

Take your time.

Thank you very much for your kind prompt reply.

Cheers

Joey
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:30
Joined
Oct 29, 2018
Messages
21,357
HI DBguy

Take your time.

Thank you very much for your kind prompt reply.

Cheers

Joey
Okay. I'll let you know when I get to it. (Unless somebody else beats me to it.) Cheers!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:30
Joined
Feb 28, 2001
Messages
26,996
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:30
Joined
Oct 29, 2018
Messages
21,357
Hi Doc. Thanks for the assist.
 

Users who are viewing this thread

Top Bottom