Query does not show all users

  • Thread starter Ivan R via AccessMonster.com
  • Start date
I

Ivan R via AccessMonster.com

I have a query that obtains the username(detective) and calculates totals.
The query works great, however it does not identify all detectives. It only
lists detectives that have any record of action for example.

Main
Detective Felony Misdemeanor Felony Misdemeanor Search K T1
Consensua Property
Barnum 0 1 0 0 0 0 0 0
Thomas 0 0 0 0 0 0 0 0
Williams 0 0 0 1 0 0 0 0

However there are two mor detectives that don't have this type of cases. I
would like for them to appear on the query results as well, with all zeros.
Is this possible?

My sql code is as follows

SELECT Main.Detective, nz(([Felony_Arrest]),0) AS [Feloney Arrest], nz((
[Misdemeanor_Arrest]),0) AS [Misdemeanor Arrests], nz(([Felony_Warrant]),0)
AS [Felony Warrant], nz(([Misdemeanor_Warrant]),0) AS [Misdemeanor Warrants],
nz(([Search_Warrant]),0) AS [Search Warrants], nz(([K_T1]),0) AS [K T1], nz((
[Consensual_Search]),0) AS [Consensual Searches], nz(([Property_Confiscated]),
0) AS [Property Confiscated]
FROM Main INNER JOIN Criminal ON Main.SafeCaseID = Criminal.SafeCaseID;

Any help would be appreciated
 
J

John Spencer (MVP)

CHange the join from an inner join to a left join

SELECT Main.Detective, nz(([Felony_Arrest]),0) AS [Feloney Arrest],
nz(([Misdemeanor_Arrest]),0) AS [Misdemeanor Arrests],
nz(([Felony_Warrant]),0) AS [Felony Warrant],
nz(([Misdemeanor_Warrant]),0) AS [Misdemeanor Warrants],
nz(([Search_Warrant]),0) AS [Search Warrants],
nz(([K_T1]),0) AS [K T1],
nz(([Consensual_Search]),0) AS [Consensual Searches],
nz(([Property_Confiscated]),0) AS [Property Confiscated]
FROM Main LEFT JOIN Criminal
 
I

Ivan R via AccessMonster.com

John said:
CHange the join from an inner join to a left join

SELECT Main.Detective, nz(([Felony_Arrest]),0) AS [Feloney Arrest],
nz(([Misdemeanor_Arrest]),0) AS [Misdemeanor Arrests],
nz(([Felony_Warrant]),0) AS [Felony Warrant],
nz(([Misdemeanor_Warrant]),0) AS [Misdemeanor Warrants],
nz(([Search_Warrant]),0) AS [Search Warrants],
nz(([K_T1]),0) AS [K T1],
nz(([Consensual_Search]),0) AS [Consensual Searches],
nz(([Property_Confiscated]),0) AS [Property Confiscated]
FROM Main LEFT JOIN Criminal
ON Main.SafeCaseID = Criminal.SafeCaseID;
I have a query that obtains the username(detective) and calculates totals.
The query works great, however it does not identify all detectives. It only
Click to expand...
[quoted text clipped - 22 lines]
Any help would be appreciated
Click to expand...
Click to expand...
 
Top