Find records that have 2 matching fields within the same table

[KingOf206]

I have a table that is called "dbo_kc_alt_tracking". Within that table I want to be able to pull out records that have 2 fields that match. The 2 fields I am looking for (both to match) are "TransmissionType" and "TrackingNumber". How can I go about finding these records?

Example:

My Table looks like this:

F-Name.....L-Name.....TransmissionType....TrackingNumber.....ID ....Date
Joe...........Smith ....... Email ..................101. ...................1.....7-22-2014
Ben...........Hable.........Fax................... ..200......................2.....7-22-2014
Jane..........Doeth........Fax.................... .100......................3......7-22-2014
Tim............Kerry........Email................. ..101......................4......7-22-2014


Abe...........Grimm........Email.................. .102......................5......7-22-2014


In this example the records that have matching fields of "TransmissionType" and "TrackingNumber" are Joe Smith and Tim Kerry, both have a transmisison type of email and both have a trackingnumber of 101, so my query would produce the following results:


F-Name.....L-Name.....TransmissionType....TrackingNumber.....ID ....Date
Joe...........Smith........Email.................. ...101.....................1.....7-22-2014
Tim...........Kerry.........Email................. ...101.....................4......7-22-2014




How can I create this type of query?

(I would like to prevent this type of data from even being entered in, but I don't have control of the front end with regards to table design. I can only design queries)

[June7]

This is not easy. Never is when record filter criteria is dependent on values in other records of the same table. Probably 4 ways to approach. Try this one:

SELECT dbo_kc_alt_tracking.ID, dbo_kc_alt_tracking.[F-Name], dbo_kc_alt_tracking.[L-Name], dbo_kc_alt_tracking.TransmissionType, dbo_kc_alt_tracking.TrackingNumber, dbo_kc_alt_tracking.Date
FROM dbo_kc_alt_tracking AS dbo_kc_alt_tracking_1 INNER JOIN dbo_kc_alt_tracking ON (dbo_kc_alt_tracking_1.TrackingNumber = dbo_kc_alt_tracking.TrackingNumber) AND (dbo_kc_alt_tracking_1.TransmissionType = dbo_kc_alt_tracking.TransmissionType)
WHERE (((dbo_kc_alt_tracking.[F-Name])<>[dbo_kc_alt_tracking_1].[F-Name]) AND ((dbo_kc_alt_tracking.[L-Name])<>[dbo_kc_alt_tracking_1].[L-Name]) AND ((dbo_kc_alt_tracking.TransmissionType)=[dbo_kc_alt_tracking_1].[TransmissionType]) AND ((dbo_kc_alt_tracking.TrackingNumber)=[dbo_kc_alt_tracking_1].[TrackingNumber]));


BTW, should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be F_Name or FirstName. Also, should not use reserved words as names. Date is a reserved word.

[KingOf206]

This is not easy. Never is when record filter criteria is dependent on values in other records of the same table. Probably 4 ways to approach. Try this one:

SELECT dbo_kc_alt_tracking.ID, dbo_kc_alt_tracking.[F-Name], dbo_kc_alt_tracking.[L-Name], dbo_kc_alt_tracking.TransmissionType, dbo_kc_alt_tracking.TrackingNumber, dbo_kc_alt_tracking.Date
FROM dbo_kc_alt_tracking AS dbo_kc_alt_tracking_1 INNER JOIN dbo_kc_alt_tracking ON (dbo_kc_alt_tracking_1.TrackingNumber = dbo_kc_alt_tracking.TrackingNumber) AND (dbo_kc_alt_tracking_1.TransmissionType = dbo_kc_alt_tracking.TransmissionType)
WHERE (((dbo_kc_alt_tracking.[F-Name])<>[dbo_kc_alt_tracking_1].[F-Name]) AND ((dbo_kc_alt_tracking.[L-Name])<>[dbo_kc_alt_tracking_1].[L-Name]) AND ((dbo_kc_alt_tracking.TransmissionType)=[dbo_kc_alt_tracking_1].[TransmissionType]) AND ((dbo_kc_alt_tracking.TrackingNumber)=[dbo_kc_alt_tracking_1].[TrackingNumber]));


BTW, should avoid spaces and special characters/punctuation (underscore is exception) in naming convention. Better would be F_Name or FirstName. Also, should not use reserved words as names. Date is a reserved word.

THANK YOU FOR YOUR REPLY !!

However I think i should have been a bit more clear.... I understand about punctuation, I acutally just wrote in some data for an example. let me put in example with the exact fields that I have...

Example:

My Table looks like this:

voter.id.....av_election_id...........Transmission Type....TrackingNumber....
11111...........10101010 ............. Email ..................101. ...................
22222...........20202020...............Fax........ ........... ..200.....................
33333...........30303030...............Fax........ ............ .100....................
44444...........40404040...............Email...... ........... ..101.....................
55555...........50505050...............Email...... ............ .102....................


In this example the records that have matching fields of "TransmissionType" and "TrackingNumber" are 11111 and 44444, both have a transmisison type of email and both have a trackingnumber of 101, so my query would produce the following results:


voter.id.....av_election_id...........Transmission Type....TrackingNumber....
11111...........10101010 ............. Email ..................101. ...................
44444...........40404040...............Email...... ........... ..101.....................




********************** I actually tried using the code you wrote above (and I just switched out the field names) but i kept getting an error. The error was "the SELECT statement includes a reserved word or argument.... punctuation is incorrect.




What should I try now?

[June7]

Don't know why it errors. The query worked for me.

Why does voter.id have a period?

[KingOf206]

Don't know why it errors. The query worked for me.

Why does voter.id have a period?

It doesn't, it has an underscore. (my mistake)

[KingOf206]

Don't know why it errors. The query worked for me.

Why does voter.id have a period?

How would the SQL look with the ACTUAL Fields that I put in in my 2nd post?

[June7]

Did you just do copy/paste of the sql into SQL View? Try building the query from scratch in the designer.

Post your attempted sql statement.

Transmission Type now shows a space in field name - as that the actual case?

[KingOf206]

Did you just do copy/paste of the sql into SQL View? Try building the query from scratch in the designer.

Post your attempted sql statement.

Transmission Type now shows a space in field name - as that the actual case?

Ok I iwll try and translate the SQL into desginer.. (but not very good at it)

Here is the SQL i used (after replacing and taking out some fields)

Let me know wha tyou think..


SELECT dbo_kc_alt_tracking.[voter_id], dbo_kc_alt_tracking.[av_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_1.TrackingNumber = dbo_kc_alt_tracking.TrackingNumber) AND (dbo_kc_alt_tracking_1.TransmissionType = dbo_kc_alt_tracking.TransmissionType)
WHERE (((dbo_kc_alt_tracking.[voter_id])<>[dbo_kc_alt_tracking_1].voter.id]) AND ((dbo_kc_alt_tracking.[av_election_id])<>[dbo_kc_alt_tracking_1].[av_election_id]) AND ((dbo_kc_alt_tracking.TransmissionType)=[dbo_kc_alt_tracking_1].[TransmissionType]) AND ((dbo_kc_alt_tracking.TrackingNumber)=[dbo_kc_alt_tracking_1].[TrackingNumber]));

[KingOf206]

lol ok I took a look at the SQL and realized I have never done an "inner join" and I know that im not translating it right... (i hate being a nube)

[June7]

There is unnecessary comma following TrackingNumber in the SELECT clause.

Actually, the criteria in WHERE clause for TransmissionType and TrackingNumber are not needed.

Try:

SELECT dbo_kc_alt_tracking.[voter_id], dbo_kc_alt_tracking.[av_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_1.TrackingNumber = dbo_kc_alt_tracking.TrackingNumber) AND (dbo_kc_alt_tracking_1.TransmissionType = dbo_kc_alt_tracking.TransmissionType)
WHERE (((dbo_kc_alt_tracking.[voter_id])<>[dbo_kc_alt_tracking_1].voter.id]) AND ((dbo_kc_alt_tracking.[av_election_id])<>[dbo_kc_alt_tracking_1].[av_election_id]);

[KingOf206]

There is unnecessary comma following TrackingNumber in the SELECT clause.

Actually, the criteria in WHERE clause for TransmissionType and TrackingNumber are not needed.

Try:

SELECT dbo_kc_alt_tracking.[voter_id], dbo_kc_alt_tracking.[av_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_1.TrackingNumber = dbo_kc_alt_tracking.TrackingNumber) AND (dbo_kc_alt_tracking_1.TransmissionType = dbo_kc_alt_tracking.TransmissionType)
WHERE (((dbo_kc_alt_tracking.[voter_id])<>[dbo_kc_alt_tracking_1].voter.id]) AND ((dbo_kc_alt_tracking.[av_election_id])<>[dbo_kc_alt_tracking_1].[av_election_id]);

ok I tried it and now this is the error message i get...::

[June7]

Sorry, I left unpaired paren when I deleted the criteria. So here I delete all parens from the WHERE clause. Access will throw them back in appropriately.

Also a missing bracket around voter.id. And shouldn't that be an underscore instead of period?

SELECT dbo_kc_alt_tracking.[voter_id], dbo_kc_alt_tracking.[av_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_1.TrackingNumber = dbo_kc_alt_tracking.TrackingNumber) AND (dbo_kc_alt_tracking_1.TransmissionType = dbo_kc_alt_tracking.TransmissionType)
WHERE [dbo_kc_alt_tracking].[voter_id]<>[dbo_kc_alt_tracking_1].[voter_id] AND [dbo_kc_alt_tracking].[av_election_id]<>[dbo_kc_alt_tracking_1].[av_election_id];

Access should also throw in brackets around the other field references as long as it can recognize them as field names. This is why spaces and special characters/punctuation and reserved words should be avoided (which you appear to have done).

The query designer can really help to avoid these pitfalls.

[KingOf206]

ok it works but the results yeild almost every record in my database. I only have 2 records that have the same Transmissiontype and Trackingnumber and so I konw what I should expect to get, but im not getting it.

Your SQL seems to pull in about 90% of all the records.

[June7]

Well, it worked for the sample dataset you posted.

If you want to provide complete dataset, follow instructions at bottom of my post.

[KingOf206]

Well, it worked for the sample dataset you posted.

If you want to provide complete dataset, follow instructions at bottom of my post.

Ok i will work on that now and get it to you. I will have to use a database that has different table names than what I will be using, but it shouldnt matter because when I get it back i can just swap on the table names. Right?