Display zero values in query

[Emma35]

Hi All,
I've got a database running which is doing fine but i want to make a small change. I have a button on a form which generates a report (based on a query) to display how many entries each user has made to the database. It works fine but i've been asked to also display the users who have zero entries. The SQL for the query is below....can anyone suggest a way to modify the code/query to display the users who have zero entries ? Thanks

SELECT tbl_MainRecsBOS.ObserverName, Count(tbl_MainRecsBOS.ObserverName) AS CountOfObserverName
FROM tbl_MainRecsBOS
GROUP BY tbl_MainRecsBOS.ObserverName
ORDER BY Count(tbl_MainRecsBOS.ObserverName) DESC;

 

[Ranman256]

make a Union query.
Q1 is the one above that counts those > 0
Q2 is the one for those who have zero only.
union qry = :
select * from Q1
union
select * from Q2

 

[plog]

Do you have a list of everyone you want to report on? If so, you use the query you posted initially as a subquery. Let's call the list of everyone Observers and the above query you posted sub1.

You would bring both Observers and sub1 into a query, LEFT JOIN them to show all from Observers and link them via ObserverName fields. In the bottom portion you would show the observer name from the Observers table and then use this calculated field to get their total count:

Records: Nz(CountOfObserverName, 0)

 

[Emma35]

Thanks guys...

I tried both ways but i don't really know much about union queries ranman
Plog....i have the query working but i need to get the results in descending order. For some reason Access is ranking them by first digit only eg: 4 is higher than 39 ??. Any idea why ?

 

[XelaIrodavlas]

For some reason Access is ranking them by first digit only eg: 4 is higher than 39 ??. Any idea why ?

Hi Emma,

I had the same issue I believe it's because Access reads the number as a string, and rates it alphabetically one letter at a time, so if 1 is less than 4, 100 comes before 40.... so clever (slow sarcastic applause for MS)

Can't remember exactly, but I think I used the val() function to fix it.
if you wrap your count something like Val(CountofObservName) and then sort that field, it should do what you want...

You may need to play about with it...

Good luck!

 

[Emma35]

Hi Alex that worked perfectly. Thank you for your help, and thanks also to ranman and plog.....i can always rely on you guys to dig me out