Making everything appear

[UT227]

I'm making a query that takes care of contacts with all races. I have a table [tblRaceList]. I have all races that we're going to use listed. When a contact is made, I need to keep track of it. I need all the information of each race even if no contact was made. I tried using a left join and a right join. Neither worked. Any advice?

[isladogs]

You can't count by RaceID / group by race as those fields will be null if no contact made.
Instead
1. Add Nz expression to the Race field e.g. ContactRace: Nz(Race,"unknown") and group by that
2. Count the field PublicContactID
3. I don't think you need Abs in your two Sum fields as I assume both fields are numbers and will never be negative

If that still doesn't work, a two step approach may be needed

[June7]

I suspect the two fields being summed are Yes/No type.

1. Remove tblRaceList from the query.
2. Building another query that joins filtered aggregate query to tblRaceList. If you want the null fields populated, can use Nz() function, like:

SELECT Nz([CampusID], Forms!frmTier1DataSearch!cboCampusID) AS CID, Nz([ChkYear], Forms!frmTier1DataSearch!txtYear) AS CY, Nz([CountOfRace],0) AS COR, Searches, CustodyArrests, tblRaceList.Race
FROM tblRaceList LEFT JOIN Query1 ON tblRaceList.RaceID = Query1.Race;

[UT227]

I put up the wrong query. It was a long night. Here is the correct query.

[UT227]

Ridders52,
I tried putting Nz([Race],"unknown"). It still didn't do what I wanted. I did change it to count [PublicContactID]. That will work much better. Not sure what I was thinking to count the [RaceID].

[isladogs]

Post your query in SQL view or post the relevant parts of your db After removing any confidential data

[UT227]

June7,
Sorry, I didn't understand what you were trying to tell me. What is Query1?

[UT227]

June7,
Sorry, I didn't understand what you were trying to tell me. What is Query1?

[UT227]

In SQL:
SELECT tblRaceList.Race, tblPublicContact.CampusID, Year([ContactDate]) AS ChkYear, tblPublicContact.PublicContactID, IIf([SearchConducted],1,0) AS Search, IIf([ReasonForSearchID]=1,1,0) AS Consent, IIf([ReasonForSearchID]=3,1,0) AS PC, IIf([ArrestMade],1,0) AS Arrest
FROM tblPublicContact LEFT JOIN tblRaceList ON tblPublicContact.RaceID = tblRaceList.RaceID
WHERE (((tblPublicContact.CampusID)=[Forms]![frmTier1DataSearch]![cboCampusID]) AND ((Year([ContactDate]))=[Forms]![frmTier1DataSearch]![txtYear]));

[UT227]

What kind of relevant data?

[isladogs]

We need the relevant parts are the query plus the two tables and form referenced by the query.
Check that works correctly as a standalone db then upload it.

[June7]

Query1 is the query saved in step 1 of post 3. I think you use the name qryTier1Data1.

[UT227]

I'm trying to attach the database. I'm not sure if this will go through.

Tier1Data.zip

[isladogs]

Hi

Just had a quick look at this.
I altered the Race field in the last 2 records to null (unknown) - both are for El Paso
I then created a global query qryAll adding the field ContactRace: Nz(Race,"unknown") as mentioned before
I also made the same change in each of your 3 queries

You'll now see that Unknown appears for both of those records in each query

You could also add an extra record to the tblRaceList table: 6 = unknown
That would have the same effect as the Nz code

Tier1Data-CR.zip

[UT227]

It didn't work. Now it shows Hispanic Or Latino and Unknown. But the other races are not showing. I need all of the races showing regardless if there is any entry for them or not.