Crosstab Query With Null Values (1 Viewer)

Uncle Ned

Registered User.
Local time
Today, 13:32
Joined
Sep 26, 2019
Messages
45
There is probably a simple answer to this, but I can't find it right now.

I have a cross tab query that takes the quantity of windows installed and tabulates them into quantities produced by week. The problem is that on Monday morning the first week column (20-32 in example) shos the total for the previous week rather than this week (20-33) with zero as the total. Once the first window is produced then everthign clicks into place.

The problem is that this produces live production data for another section and they want to see zero for the week if zero have been produced.

Thanks
 

Attachments

  • crosstab query.jpg
    crosstab query.jpg
    123.6 KB · Views: 164

Gasman

Enthusiastic Amateur
Local time
Today, 13:32
Joined
Sep 21, 2011
Messages
14,052
I have no idea where Nulls are coming in to this, but you could use the NZ() function to replace null with 0 (zero).?
 

Uncle Ned

Registered User.
Local time
Today, 13:32
Joined
Sep 26, 2019
Messages
45
I have no idea where Nulls are coming in to this, but you could use the NZ() function to replace null with 0 (zero).?

The problem is that until sombody enters the completion of a Window on a Monday morning, there isn't anything to count. So the data for last week appears under the heading for this week.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:32
Joined
Sep 21, 2011
Messages
14,052
Well you can use the NZ() function to replace Null with whatever you need to get your query correct.
 

Uncle Ned

Registered User.
Local time
Today, 13:32
Joined
Sep 26, 2019
Messages
45
Well you can use the NZ() function to replace Null with whatever you need to get your query correct.
But to use NZ do I not require to have an entry with a Null value? In this case, I don't have any enteries
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:32
Joined
Sep 21, 2011
Messages
14,052
So why is that in the title?
I *thought* you were indicating that a non complete window would not have a date and hence still be Null?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:32
Joined
May 7, 2009
Messages
19,175
you need to introduce another Table/Query on your Crosstab.
image_1.png
 

Uncle Ned

Registered User.
Local time
Today, 13:32
Joined
Sep 26, 2019
Messages
45
you need to introduce another Table/Query on your Crosstab.

Hi Arnelgp, just gettting back to this issue. I tired your suggestion and added an extra query, you haven't suggested differently, so I used the same data source for the second query and changed the date format from days to weeks. However, the results that I'm getting back are not correct - see below.

Access Query SS.jpg
 

Minty

AWF VIP
Local time
Today, 13:32
Joined
Jul 26, 2013
Messages
10,355
In your crosstab you have used the Process field from your original data not the Date generator query, therefore it's multiplying it all out.
 

Uncle Ned

Registered User.
Local time
Today, 13:32
Joined
Sep 26, 2019
Messages
45
In your crosstab you have used the Process field from your original data not the Date generator query, therefore it's multiplying it all out.

I missed that, but still when I make those changes I'm getting the inflated production numbers

What way have you built the second query?

1598611468664.png
 

Users who are viewing this thread

Top Bottom