Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-12-2019, 08:53 AM   #16
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,240
Thanks: 63
Thanked 1,678 Times in 1,633 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

zip link below


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

thanks

Joey
Hi Joey. Thanks. Unfortunately, it's still too big for me right now, since I'm using my data plan. I'll have to wait until I get home to download it.

__________________
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:56 AM   #17
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

Take your time.

Thank you very much for your kind prompt reply.

Cheers

Joey
joeyd11ph is offline   Reply With Quote
Old 11-12-2019, 08:57 AM   #18
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,240
Thanks: 63
Thanked 1,678 Times in 1,633 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

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!

__________________
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, 10:59 AM   #19
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,068
Thanks: 100
Thanked 1,890 Times in 1,726 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Chicken and Egg situation, which comes first?

Code:
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];
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
joeyd11ph (11-12-2019)
Old 11-12-2019, 11:30 AM   #20
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,240
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Chicken and Egg situation, which comes first?

Hi Doc. Thanks for the assist.
__________________
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:49 PM   #21
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?

Thanks for cleaning up tip Doc, It will certainly make my code easy to follow and debug...

Quote:
Originally Posted by The_Doc_Man View Post
Code:
  
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];
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.
joeyd11ph is offline   Reply With Quote
Old 11-13-2019, 08:18 AM   #22
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,240
Thanks: 63
Thanked 1,678 Times in 1,633 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Chicken and Egg situation, which comes first?

Hi Joey. I hope Doc was able to give you what you were looking for. Something must have happened to the website yesterday, because I couldn't download your file last night.

__________________
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-13-2019, 08:42 AM   #23
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

Yes me too cant access the forum yesterday.

I still have the same problem.

thanks

joey
joeyd11ph is offline   Reply With Quote
Old 11-17-2019, 06:30 AM   #24
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?

To All Gurus

Finally, I found out that my test data is corrupted.

Thanks to all, Cheers

Joey
joeyd11ph is offline   Reply With Quote
Old 11-17-2019, 07:45 AM   #25
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 7,240
Thanks: 63
Thanked 1,678 Times in 1,633 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
To All Gurus

Finally, I found out that my test data is corrupted.

Thanks to all, Cheers

Joey
Hi Joey. Glad to hear you got it sorted out. Good luck with your project.
__________________
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-17-2019, 09:44 AM   #26
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 497
Thanks: 23
Thanked 93 Times in 92 Posts
mike60smart will become famous soon enough
Re: Chicken and Egg situation, which comes first?

Hi

What is the full name of the Report to Open??

mike60smart 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 03:28 AM.


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 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World