Chicken and Egg situation, which comes first?

J

joeyd11ph

Registered User.
Local time
Today, 15:52
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:
Hi Joey. I don't understand the question. Can you please post the SQL statement for your query?
 
That calc really makes no sense to me.

Show sample data and desired output.
 
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: 163
  • Chicken and Egg.jpg
    Chicken and Egg.jpg
    85.6 KB · Views: 162
Last edited:
Hi Isaladogs

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

I will try your link.

Thanks much

cheers

Joey
 
joeyd11ph said:
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
Click to expand...
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.
 
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
 
joeyd11ph said:
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
Click to expand...
Hi Joey. I don't see an attachment. Try to zip it up first before attaching.
 
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: 184
joeyd11ph said:
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
Click to expand...
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.
 
HI DBguy

Take your time.

Thank you very much for your kind prompt reply.

Cheers

Joey
 
joeyd11ph said:
HI DBguy

Take your time.

Thank you very much for your kind prompt reply.

Cheers

Joey
Click to expand...
Okay. I'll let you know when I get to it. (Unless somebody else beats me to it.) Cheers!
 
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.
 
Hi Doc. Thanks for the assist.
 
You must log in or register to reply here.

Similar threads

B
Update query doesn't like value formatted for a different region.
2
Replies
21
Views
974
Josef P.
J
mloucel
OpenArgs issue..
Replies
5
Views
363
mloucel
mloucel
K
#Error in text field on report
Replies
6
Views
608
kevin_maher
K
Uncle Gizmo
How to Simplify MS Access Form Searches Using Class Modules and Collections to Pair Text Boxes and Option Groups for Efficient SQL Query Generation
2
Replies
25
Views
1,431
MarkK
MarkK
mloucel
Combo's [Edit from Record] help
2
Replies
26
Views
832
mloucel
mloucel

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom