Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-11-2019, 09:51 PM   #1
joeyd11ph
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 18
Thanked 0 Times in 0 Posts
joeyd11ph is on a distinguished road
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


Last edited by joeyd11ph; 11-11-2019 at 10:30 PM.
joeyd11ph is offline   Reply With Quote
Old 11-11-2019, 09:55 PM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
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?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-11-2019, 09:57 PM   #3
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,476
Thanks: 0
Thanked 577 Times in 573 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Chicken and Egg situation, which comes first?

That calc really makes no sense to me.

Show sample data and desired output.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-12-2019, 12:05 AM   #4
joeyd11ph
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 18
Thanked 0 Times in 0 Posts
joeyd11ph is on a distinguished road
Re: Chicken and Egg situation, which comes first?

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
Attached Images
File Type: png screen 2.PNG (51.4 KB, 10 views)
File Type: jpg Chicken and Egg.jpg (85.6 KB, 5 views)

Last edited by joeyd11ph; 11-12-2019 at 10:24 AM.
joeyd11ph is offline   Reply With Quote
Old 11-12-2019, 12:13 AM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,369
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-12-2019, 12:36 AM   #6
joeyd11ph
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 18
Thanked 0 Times in 0 Posts
joeyd11ph is on a distinguished road
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 is offline   Reply With Quote
Old 11-12-2019, 06:56 AM   #7
joeyd11ph
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 18
Thanked 0 Times in 0 Posts
joeyd11ph is on a distinguished road
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

joeyd11ph is offline   Reply With Quote
Old 11-12-2019, 07:38 AM   #8
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Chicken and Egg situation, which comes first?

Quote:
Originally Posted by joeyd11ph View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-12-2019, 07:58 AM   #9
joeyd11ph
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 18
Thanked 0 Times in 0 Posts
joeyd11ph is on a distinguished road
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
joeyd11ph is offline   Reply With Quote
Old 11-12-2019, 08:01 AM   #10
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Chicken and Egg situation, which comes first?

Quote:
Originally Posted by joeyd11ph View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-12-2019, 08:21 AM   #11
joeyd11ph
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 18
Thanked 0 Times in 0 Posts
joeyd11ph is on a distinguished road
Re: Chicken and Egg situation, which comes first?

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
Attached Images
File Type: png error upload DB.PNG (53.7 KB, 6 views)
joeyd11ph is offline   Reply With Quote
Old 11-12-2019, 08:30 AM   #12
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Chicken and Egg situation, which comes first?

Quote:
Originally Posted by joeyd11ph View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-12-2019, 08:41 AM   #13
joeyd11ph
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 18
Thanked 0 Times in 0 Posts
joeyd11ph is on a distinguished road
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
joeyd11ph is offline   Reply With Quote
Old 11-12-2019, 08:45 AM   #14
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,653
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Chicken and Egg situation, which comes first?

Quote:
Originally Posted by joeyd11ph View Post
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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-12-2019, 08:50 AM   #15
joeyd11ph
Newly Registered User
 
Join Date: Jul 2019
Posts: 32
Thanks: 18
Thanked 0 Times in 0 Posts
joeyd11ph is on a distinguished road
Re: Chicken and Egg situation, which comes first?

Hi DBguy

zip link below


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

thanks

Joey

joeyd11ph is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
What's your opinions on the IS situation? ConnorGiles The Watercooler 33 10-08-2014 05:40 AM
chicken surprise !!!! GaryPanic The Watercooler 6 05-15-2007 05:26 AM
[SOLVED] My Situation! Future CFE General 2 11-28-2005 12:55 AM
Where are we with this situation? raskew Site Suggestions 4 11-22-2005 09:15 AM
The Chicken or The Egg Nursery Ed General 3 05-28-2004 12:31 AM




All times are GMT -8. The time now is 05:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


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