Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Queries (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=8)
-   -   Chicken and Egg situation, which comes first? (https://www.access-programmers.co.uk/forums/showthread.php?t=307884)

joeyd11ph 11-11-2019 09:51 PM

Chicken and Egg situation, which comes first?
 
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 11-11-2019 09:55 PM

Re: Chicken and Egg situation, which comes first?
 
Hi Joey. I don't understand the question. Can you please post the SQL statement for your query?

June7 11-11-2019 09:57 PM

Re: Chicken and Egg situation, which comes first?
 
That calc really makes no sense to me.

Show sample data and desired output.

joeyd11ph 11-12-2019 12:05 AM

Re: Chicken and Egg situation, which comes first?
 
2 Attachment(s)
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 11-12-2019 12:13 AM

Re: Chicken and Egg situation, which comes first?
 
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 11-12-2019 12:36 AM

Re: Chicken and Egg situation, which comes first?
 
Hi Isaladogs

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

I will try your link.

Thanks much

cheers

Joey

joeyd11ph 11-12-2019 06:56 AM

Re: Chicken and Egg situation, which comes first?
 
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 11-12-2019 07:38 AM

Re: Chicken and Egg situation, which comes first?
 
Quote:

Originally Posted by joeyd11ph (Post 1650524)
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 11-12-2019 07:58 AM

Re: Chicken and Egg situation, which comes first?
 
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 11-12-2019 08:01 AM

Re: Chicken and Egg situation, which comes first?
 
Quote:

Originally Posted by joeyd11ph (Post 1650541)
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 11-12-2019 08:21 AM

Re: Chicken and Egg situation, which comes first?
 
1 Attachment(s)
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 11-12-2019 08:30 AM

Re: Chicken and Egg situation, which comes first?
 
Quote:

Originally Posted by joeyd11ph (Post 1650546)
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 11-12-2019 08:41 AM

Re: Chicken and Egg situation, which comes first?
 
Hi DBguy

here is the link


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

thanks

joey

theDBguy 11-12-2019 08:45 AM

Re: Chicken and Egg situation, which comes first?
 
Quote:

Originally Posted by joeyd11ph (Post 1650552)
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 11-12-2019 08:50 AM

Re: Chicken and Egg situation, which comes first?
 
Hi DBguy

zip link below


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

thanks

Joey


All times are GMT -8. The time now is 10:40 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World