What's so special about table name and field name you had to obscure them?
SearchablePublic has records with no DistrictCode or OfficeCode - why?
Is UserID unique in tbl_Complete?
Why a compound primary key in SearchablePublic? Is CellPhone also a field in tbl_Complete?
Apply filter criteria to DistrictCode: Not Is Null
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Answering your questions:
1. For business reasons
2. This is a table linked to another db; not mine to fix
3. Yes, UserID is unique in Tbl_complete
4. see answer in #2
5. That returns 1577 records; because 200 records in the linked table does not have district code or office code.
Anyway suggestions around that?
Apply filter criteria to OfficeCode also: Not Is Null
Answer to Question 4 doesn't address the cellphone part and I will assume correct response is No unless indicated otherwise.
According to the example data posted, that criteria should remove all duplicate UserID. If it doesn't then the data posted is not representative.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
But without data I can't figure out why you are getting duplicates.
Try setting JOIN type to "Include all records from tbl_Complete ..." and then maybe a GROUP BY on your original query.
Or maybe you better do Query1:
SELECT UserID, DistrictCode, OfficeCode WHERE DistrictCode Not Is Null;
If that still returns dups, trying GROUP BY or DISTINCT.
Then join that query to tbl_Complete with the JOIN type "Include all records from tbl_Complete ..."
Suggest you set UserID in tbl_Complete as primary key.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.