UNION QUERY How to include to count NULL Values

[DaveT99]

I created a UNION query to put the Time Since Last Issue (TSLI) for line items in the warehouse. I counted the number in ranges 0-1 year, 2-3 etc

I used the SQL format that i had used in the past, but I noted for the 2 of the ranges it gave the wrong count. I then created a simple BETWEEN query which gave the correct answer, so I then copied the WHERE clause and it all worked

My 1st question is "is my original Union Query format incorrect
and
My 2nd Question is how to include a count for the NULL values in the Union Query, there are 14 values ie with no recorded issue

I have documented the problem as a pdf, I can also upload the access file if needed

Many Thanks
Dave

[Welshgasman]

Use NZ() function?

[ano]

1st i would try group by with iif to group years to gether.
2nd between is same as <= and >=
3rd is null is not hte best way to treat null use function isnull

[Micron]

Please just copy data from a table/query/spreadsheet and paste into a post. Paste code/sql withing code tags. If anyone wants to test query edits, they can't do much with pictures. Besides, with your link we end up downloading files we don't want or need.

[June7]

Provide data as a table in post (copy/paste or use Go Advanced editor menu) or attach file.

Avoid UNION when possible as can slow performance. I don't think need UNION for this calculation. Use Switch() function or a custom VBA function.

Switch(TSLlyr >= 5, "5 to 10", TSLlyr >= 3, "3 to 4.999", TSLlyr >= 2, "2 to 2.999", TSLlyr >= 1, "1 to 1.999", True, "0 to 0.999") & " yrs" AS Rng

[DaveT99]

Provide data as a table in post (copy/paste or use Go Advanced editor menu) or attach file.

Avoid UNION when possible as can slow performance. I don't think need UNION for this calculation. Use Switch() function or a custom VBA function.

Switch(TSLlyr >= 5, "5 to 10", TSLlyr >= 3, "3 to 4.999", TSLlyr >= 2, "2 to 2.999", TSLlyr >= 1, "1 to 1.999", True, "0 to 0.999") & " yrs" AS Rng

Thank you for all replies, especially June7 for the TIP about not using attachments, for the future and the expression. the Switch function is NEW to me, will google this

Can I ask what do I enter for the CODE and [/CODE][/QUOTE] in the above switch, I tried TSLIyr for code but obviously got an error. Also how would I include NULL values in the Switch Function

I have attached the dB as a ZIP to keep the size within the limit

Many Thanks for the help

[ano]

SELECT STOCK_NO, LAST_ISSUE2, DL_DATE, DL_DATE-LAST_ISSUE2 AS TSLI, TSLI/365 AS TSLIyr, Switch(TSLIyr >= 5,5, TSLIyr >= 3, 4, TSLIyr >= 2, 3, TSLIyr >= 1,2, True, 1) AS rng
FROM ISSUE_DATA;

SELECT  Switch(rng = 5, "5 to 10", rng = 4, "3 to 4.999", rng=3, "2 to 2.999",rng=2, "1 to 1.999", True, "0 to 0.999") & " yrs" as rang,Count(TSLIyr) AS CountOfTSLIyrFROM grouping
group by rng ;

[DaveT99]

SELECT STOCK_NO, LAST_ISSUE2, DL_DATE, DL_DATE-LAST_ISSUE2 AS TSLI, TSLI/365 AS TSLIyr, Switch(TSLIyr >= 5,5, TSLIyr >= 3, 4, TSLIyr >= 2, 3, TSLIyr >= 1,2, True, 1) AS rng
FROM ISSUE_DATA;

SELECT  Switch(rng = 5, "5 to 10", rng = 4, "3 to 4.999", rng=3, "2 to 2.999",rng=2, "1 to 1.999", True, "0 to 0.999") & " yrs" as rang,Count(TSLIyr) AS CountOfTSLIyrFROM grouping
group by rng ;

many thanks for your response

For the 2nd SQL statement i get this error message

"The SELECT statement includes a reserved word or an argument name that is misspelled, or the punctuation is incorrect"

[ano]

from has no spACE

[DaveT99]

DO YOU MEAN AS CountOfTSLIyrFROMgrouping AS YOURS WAS AS CountOfTSLIyrFROM grouping

[ano]

btw june7 did not include the null , easy bec not mention in switch the last (true) should be null if the rest is correct.
btw advanced shuld include recognize the sql structure and recognize thr from clause

[DaveT99]

btw june7 did not include the null , easy bec not mention in switch the last (true) should be null if the rest is correct.
btw advanced shuld include recognize the sql structure and recognize thr from clause

Many Thanks
Dave

[June7]

Need space in front of FROM

AS CountOfTSLIyr FROM grouping

And yes, any record not captured by the explicit range categories will fall into the last parameter.

[DaveT99]

need space in front of from

as countoftsliyr from grouping

and yes, any record not captured by the explicit range categories will fall into the last parameter.

many thanks june

[ano]

first after looking at all , i wonder if dl_date in the table should not be date function or form field
i have optimized the switch from jun7 (perfect option)
i have changed 1 dl_date lower then the last_issue2

SELECT 
STOCK_NO, 
LAST_ISSUE2, 
DL_DATE, 
DL_DATE-LAST_ISSUE2 AS TSLI, 
TSLI/365 AS TSLIyr, 
Switch( 
TSLIyr >= 5,5, 
TSLIyr >= 3, 4, 
TSLIyr >= 2, 3, 
TSLIyr >= 1,2, 
TSLIyr >= 0,1, 
isnull(TSLIyr),-1, 
True, 0) AS rng
FROM ISSUE_DATA ;

SELECT Switch( 
rng = 5, "5 to 10", 
rng = 4, "3 to 4.999", 
rng = 3, "2 to 2.999", 
rng = 2, "1 to 1.999", 
rng = 1, "0 to 0.999", 
rng = 0," ???", 
true,"null") & " yrs" AS rang, Count(*) AS CountOfTSLIyr
FROM grouping 
where rng < 6 
GROUP BY rng ;