Find records that have 2 matching fields within the same table

[KingOf206]

Here is the data base. My tables are all "linked" tables so I don't think you will be able to pull any data from them. Let me know if I need to do anything else.AltBallotFormatTrackingAPP_Reporting - Copy.zip

[June7]

I can't test query without data. You would have to import to Access tables.

[KingOf206]

OK I have finally got a table that has data in it. Although some other tables are linked, the major table is the one I am trying to use for this particular query. Again thank you so much for your patience and assistance.

AltBallotFormatTrackingAPP_Reporting - Copy.zip

[KingOf206]

Bump - Haven't heard anything back from you. Everything ok?

[June7]

Lost track of this.

I was trying to avoid domain aggregate but tried it to compare:

SELECT dbo_kc_alt_tracking.voter_id, dbo_kc_alt_tracking.election_id, dbo_kc_alt_tracking.TransmissionType, dbo_kc_alt_tracking.TrackingNumber, DCount("[election_id]","[dbo_kc_alt_tracking]"," [TransmissionType]='" & [TransmissionType] & "' AND [TrackingNumber]=" & [TrackingNumber]) AS MatchCount
FROM dbo_kc_alt_tracking
WHERE (((DCount("[election_id]","[dbo_kc_alt_tracking]"," [TransmissionType]='" & [TransmissionType] & "' AND [TrackingNumber]=" & [TrackingNumber]))>0))
ORDER BY dbo_kc_alt_tracking.TransmissionType, dbo_kc_alt_tracking.TrackingNumber;

The data in db does not reconcile with the example in post 3.

voter_id	election_id	TransmissionType	TrackingNumber	MatchCount
40196366	1259	Email	101	2
991065134	1261	Email	101	2

There are MANY records that meet the criteria you defined. A partial list:

voter_id	election_id	TransmissionType	TrackingNumber	MatchCount
40196366	1259	Email	101	2
991065134	1261	Email	101	2
760620971	1261	Email	102	2
40196854	1259	Email	102	2
980489213	1261	Email	103	2
940519152	1259	Email	103	2
970463275	1259	Email	104	2
991359666	1261	Email	104	2
970463267	1259	Email	105	2
910298968	1261	Email	105	2
20366117	1261	Email	106	2
274496	1259	Email	106	2

[KingOf206]

I apologize for not being clear. When I look thru the records. I have a code that I use that pulls the most recent "Election ID" so in this case the most current "Election id" is going to be 1261. The code I use in all my queries is (select election_id from dbo_election where default_election ="y") This code just pulls the election that I work in.

Does that makes sense? So for the above examples, Right now the current election is the "election id" with a value of 1261 (it goes up 1 for every election that we have).

[June7]

That field not in the db posted. Without the field, more complicated because need to get the Max election_id with another subquery or DMax().

Try adding it as criteria in the suggested queries. It would have to be included in the DCount() expression.

[KingOf206]

I dont get that. Because if you just put in the code that i put above in the query (this code would go in the criteria box under "election_id" then it would automatically just pull the most current records using only 1 election id. Are you able to do that?

[June7]

SELECT dbo_kc_alt_tracking.voter_id, dbo_kc_alt_tracking.election_id, dbo_kc_alt_tracking.TransmissionType, dbo_kc_alt_tracking.TrackingNumber, DCount("*","[dbo_kc_alt_tracking]","election_id=" & [election_id] & " AND [TransmissionType]='" & [TransmissionType] & "' AND [TrackingNumber]=" & [TrackingNumber]) AS MatchCount
FROM dbo_kc_alt_tracking
WHERE (((dbo_kc_alt_tracking.election_id)=1261) AND ((DCount("*","[dbo_kc_alt_tracking]","election_id=" & [election_id] & " AND [TransmissionType]='" & [TransmissionType] & "' AND [TrackingNumber]=" & [TrackingNumber]))>1))
ORDER BY dbo_kc_alt_tracking.TransmissionType, dbo_kc_alt_tracking.TrackingNumber;

voter_id	election_id	TransmissionType	TrackingNumber	MatchCount
970303332	1261	NS Mail	10	2
840471659	1261	NS Mail	10	2

[KingOf206]

Wow!!

I would have never gotten that without your help! That is great!! Thank you so much for your patience and all your help with this code!

[June7]

Be aware domain aggregate can be very slow. Here is the other approach, corrected for the election_id. I should have seen this before, did cross my mind the election_id could be important but didn't follow through.

SELECT dbo_kc_alt_tracking.voter_id, dbo_kc_alt_tracking.election_id, dbo_kc_alt_tracking.TransmissionType, dbo_kc_alt_tracking.TrackingNumber
FROM dbo_kc_alt_tracking AS dbo_kc_alt_tracking_1 INNER JOIN dbo_kc_alt_tracking ON (dbo_kc_alt_tracking.election_id = dbo_kc_alt_tracking_1.election_id) AND (dbo_kc_alt_tracking_1.TransmissionType = dbo_kc_alt_tracking.TransmissionType) AND (dbo_kc_alt_tracking_1.TrackingNumber = dbo_kc_alt_tracking.TrackingNumber)
WHERE (((dbo_kc_alt_tracking.voter_id)<>[dbo_kc_alt_tracking_1].[voter_id]) AND ((dbo_kc_alt_tracking.election_id)<>[dbo_kc_alt_tracking_1].[av_election_id]));

voter_id	election_id	TransmissionType	TrackingNumber
840471659	1261	NS Mail	10
970303332	1261	NS Mail	10