Solved Force Zero Result for Week in Query (1 Viewer)

Uncle Ned

Registered User.
Local time
Today, 20:39
Joined
Sep 26, 2019
Messages
45
Looking for help on this one, hoping it's a simple solution for the more experienced users.

I have a crosstab query that is part of a production report screen and we review this data on a week by week basis. From the Screen Dump in the attachment you will see that the query returns a count of thiese products for all weeks except wk 38. We didn't produce anything that week, so I need to show a zero instead of ignoring the week - I've highlighted weeks 39 to 37 in the image.

Here is the SQL used

TRANSFORM Count(qryDataFrames.Moulded) AS CountOfMoulded
SELECT qryDataFrames.Process, Count(qryDataFrames.Moulded) AS Total
FROM qryDataFrames
WHERE (((qryDataFrames.MouldedOn) Between (Date()-28) And Date()))
GROUP BY qryDataFrames.Process
ORDER BY Format([Date],"yy-ww") DESC
PIVOT Format([Date],"yy-ww");

Hope you can help?
 

Attachments

  • Query SD.jpg
    Query SD.jpg
    130.3 KB · Views: 242

plog

Banishment Pending
Local time
Today, 15:39
Joined
May 11, 2011
Messages
11,611
You can't manufacture records with values in a query when the underlying datasources don't have that value. So, you need to add a datasource to your query that has the value you want in your query.

In this specific instance, since you want all the weeks, you need a table with all the weeks you want the query to show. You would then make that table the table in the FROM and LEFT JOIN your qryDataFrames to it. That way it will show all values from the weeks table no matter if any data was in qryDataFrames.
 

Uncle Ned

Registered User.
Local time
Today, 20:39
Joined
Sep 26, 2019
Messages
45
I've thought about using another datasource to create the table headings, but...

1. I'm not entirley sure how to do this
2. Would such a datasource require manually inputing each week with a new week number? I'm trying to avoid as much intervention as possible so that this system can run smoothly when I am not in the office

Thanks
 

plog

Banishment Pending
Local time
Today, 15:39
Joined
May 11, 2011
Messages
11,611
Make a weeks table with 54 records:

tblWeek
1
2
3
...
52
53
54

You don't need dates, just numbers.
 

Uncle Ned

Registered User.
Local time
Today, 20:39
Joined
Sep 26, 2019
Messages
45
I've never used LEFT JOIN before, I'll give it a go

Thanks
 

Uncle Ned

Registered User.
Local time
Today, 20:39
Joined
Sep 26, 2019
Messages
45
Having difficulty with this one, can't get the columns to match up - I think it is becuase the week number from the original table is an actual date formated "ww", where as the week number from the new source is a number.

Any idea how to convert the date into a number?
 

plog

Banishment Pending
Local time
Today, 15:39
Joined
May 11, 2011
Messages
11,611
Use DatePart to get the weeknumber of a date:

 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:39
Joined
May 7, 2009
Messages
19,169
see the "dummy" tables, dd, mm, yy, zz.
see qryAllProcess.
see how the dummy tables and qryAllProcess are joined together to create Query1.
Query1 then is joined to your query (on my sample i made it as table) qryDataFrames.

query2 is your final crosstab query.
 

Attachments

  • fakeDates.accdb
    2.8 MB · Views: 460

Uncle Ned

Registered User.
Local time
Today, 20:39
Joined
Sep 26, 2019
Messages
45
see the "dummy" tables, dd, mm, yy, zz.
see qryAllProcess.
see how the dummy tables and qryAllProcess are joined together to create Query1.
Query1 then is joined to your query (on my sample i made it as table) qryDataFrames.

query2 is your final crosstab query.
I really appreciate all the effort you went to here and your qryDataFrames table is almost right. The difference being on my production system, each frame being produced is setup in the office and when it's produced gets a tick in a yes/no field.

I probably should have done this in the first place....

I've used your fake database and added an example of the actual frame data collected during production, the query that I use to modify the data (qryFrameData) and then the crosstab query.

Maybe you can have a look and let me know what you think?

Many thanks
 

Attachments

  • fakeDates.accdb
    2.8 MB · Views: 395

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:39
Joined
May 7, 2009
Messages
19,169
see Query3.
 

Attachments

  • fakeDates.accdb
    4.5 MB · Views: 437

Uncle Ned

Registered User.
Local time
Today, 20:39
Joined
Sep 26, 2019
Messages
45
see Query3.
That has hit the nail on the head, apart from having to adjust when the Week 1 begins in the year, I was able to transfer this no problem into my main system.

Now I just have to repeat everything for frames accross the different product lines we are monitoring

Thanks to all for you help and suggestions
 

Users who are viewing this thread

Top Bottom