Query to Count records with & without a null value in a date field

[KingOf206]

Ok so I have 2 tables.

1 is called “dbo_kc_alt_tracking” (this table has about 250 records, and is growing) and the 2^nd table is called “dbo_av_election” (this table has about 1.2 million records).
The tables are connected by 2 primary key fields “voter_id” and “election_id”.

I am trying to do make 2 query’s:
1. I am trying to put together a query to count the number of records that are in the “dbo_kc_alt_tracking” table that have a “null” value in the field “date_returned” field which is in the “dbo_av_election”.
2. In addition I want to make the exact same query but I would like it to count the counts that DO have (any) value in the field “date_returned” which is in the “dbo_av_election”.

Currently when I try to make this (what I thought would be a simple query) it keeps pulling way too many records. I know that since the table “dbo_kc_alt_tracking” only has about 250 records right now the query results should NEVER be higher than that, but it is. Somehow I am counting too many records.


Can anyone help me with putting this together?

I attached an image of what I have, but it does not work at all (brings back over 1,000 records which should not happen when i know the table I am trying to pull from has only 250).


(the code in the criteria box is just having the records that have the most recent Election id, Every 3 months the election ID gets changed so I only want to pull the most recent records)

I would like some navigation on how to put this together. Any guidance would be very helpful!

[June7]

Try a compound linking on the electionID and voterID fields.

[KingOf206]

Try a compound linking on the electionID and voterID fields.

What is that? Never heard of it.

[June7]

Both tables have unique identifier defined by multiple fields. Both involve the voter_id and election_id fields. Need to link on both pairs.

[KingOf206]

Both tables have unique identifier defined by multiple fields. Both involve the voter_id and election_id fields. Need to link on both pairs.

So i just need to add another relationship?

[June7]

Can establish link in the Relationships window if you want.

Must establish link between fields in the query builder.

[JoeM]

See: http://office.microsoft.com/en-us/ac...003084061.aspx

[KingOf206]

Ok, great I got it working. Thank you!