No Duplicates when Viewing Query

[zashaikh]

Hello.

I am trying to setup a query between two tables (tblAlpha and tblBeta). These tables share an IncidentIDNum.



tblAlpha can only have one record per IncidentIDNum

However, tblBeta can have up to two records per IncidentIDNum. This is because tblBeta can have an incident where the value of cboCategory can be both "Fake" and "Smuggled"

When I run my query now, I can have the IncidentIDNum repeated twice because tblBeta has both "Fake" and "Smuggled" records.

When I parse my data however, "Fake" always supercedes "Smuggled". Therefore, I would like my query to do as follows: {Wherever a tblBeta has both "Fake" and "Smuggled" linked to a single incident, only return "Fake" in the query.)

I really hope that makes sense...

Thanks!

[aytee111]

Query 1 - all the "Fake".
Query 2 - all the "Smuggled" where the ID does not exist in query 1 (non-matching query)
Query 3 - UNION both queries together

[zashaikh]

My database keeps freezing when I try to do that. I also do not thing that is going to work...

Number of Incident	Date of Incident	Activity	City
20	14-Jan-16	Fake	New York
35	11-Feb-16	Fake	New York
35	11-Feb-16	Smuggled	New York
41	01-May-16	Fake	New York
42	27-Jan-16	Smuggled	New York

In the image above, I ran a query to find all incidents involved New York occurring in 2016. "Number of Incident" shared between two tables. "Date" is in tblAlpha. "Activity" and "City" are in tblBeta.

Incident#35 appears twice in my query because it was listed as a "Fake" and "Smuggled" incident. I want my query to ignore the second "Incident#35" with the 'Smuggled' value.

[aytee111]

Query 2 (see above)

[zashaikh]

I must not be doing "Query 2" correctly. Can you please give me a little more information

[aytee111]

Go to query wizard, select unmatched query. First will be query1, second will be the table/query that you used in query1.
Then select which field is the matching field (incident?). This query will show all the records in that table that have only smuggled.

[zashaikh]

second will be the table/query that you used in query1.

I used two tables to make the Query1.

[aytee111]

Use the table that has the incident and the smuggled, compare that to query 1.