Chicken and Egg situation, which comes first?

[joeyd11ph]

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

[theDBguy]

Hi Joey. I don't understand the question. Can you please post the SQL statement for your query?

[June7]

That calc really makes no sense to me.

Show sample data and desired output.

[joeyd11ph]

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

[isladogs]

I think its the middle of the night in San Diego and DBG is offline

From your screenshot A=(B-C)+(D-E) but that would give a negative value with your data.

If you want to use a value from a previous record this example using a subquery may help http://allenbrowne.com/subquery-01.html#AnotherRecord

[joeyd11ph]

Hi Isaladogs

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

I will try your link.

Thanks much

cheers

Joey

[joeyd11ph]

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

[theDBguy]

Originally Posted by joeyd11ph

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]

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]

Originally Posted by joeyd11ph

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]

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

[theDBguy]

Originally Posted by joeyd11ph

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]

Hi DBguy

here is the link


https://1drv.ms/u/s!Aik1iwXemtr6jyXE...1TFOT?e=vUuA1U

thanks

joey

[theDBguy]

Originally Posted by joeyd11ph

Hi DBguy

here is the link


https://1drv.ms/u/s!Aik1iwXemtr6jyXE...1TFOT?e=vUuA1U

thanks

joey

Hi Joey. Would you mind zipping it up. It's too big!

[joeyd11ph]

Hi DBguy

zip link below


https://1drv.ms/u/s!Aik1iwXemtr6jyDy...tWHXl?e=MleOQt

thanks

Joey