Query generating duplicate

ielamrani

Registered User.
Local time
Today, 19:56
Joined
May 6, 2008
Messages
51
Hi,
Anyone has the answer to this issue please let me know. It sounds easy but I can't seem to solve it. I am using Access 2007.

Thanks in advance.

I have 2 tables with data same number of columns. when I linked them in a query I get duplicate data.


Table 1
Patient MRN DelTime Type Art
John 12 1/1/11 B 7.39
John 12 1/1/11 B 7.37

Table2
Patient MRN DelTime Type Ven
John 12 1/1/11 B 7.41
John 12 1/1/11 B 7.40


When I combine them in a query:


SELECT DISTINCT Table1.Patient, Table1.MRN, Table1.DelTime, Table1.Type, Table1.Art, Table2.Ven
FROM Table1 LEFT JOIN Table2 ON Table1.MRN = Table2.MRN;

the result is:
Patient MRN DelTime Type Art Ven
John 12 1/1/11 B 7.39 7.40
John 12 1/1/11 B 7.39 7.41
John 12 1/1/11 B 7.37 7.40
John 12 1/1/11 B 7.37 7.41


It create a duplicate as you see above. What I am looking for is 2 lines:

Patient MRN DelTime Type Art Ven
John 12 1/1/11 B 7.39 7.40
John 12 1/1/11 B 7.37 7.41


I have tried min and max but obviously that does not work.

If this can be done in access or sqol is great if Excel that's also ok
 
Last edited:
Code:
SELECT DISTINCT Table1.Patient, Table1.MRN, Table1.DelTime, Table1.Type, Table1.Art, DMax("Ven","Table2","MRN = '" & [Table1]![MRN] & "'") AS VenMax
FROM Table1 INNER JOIN Table2 ON Table1.MRN = Table2.MRN;

My Results:

Patient MRN DelTime Type Art VenMax
John 12 01/01/2011 B 7.37 7.41
John 12 01/01/2011 B 7.39 7.41
 
You're join isn't specific enough. Here's your data again, this time I added Record Numbers before each row to help uniquely identify them:

Table 1
RecordNo Patient MRN DelTime Type Art
1,John, 12, 1/1/11, B, 7.39
2,John, 12, 1/1/11, B, 7.37

Table2
RecordNo Patient MRN DelTime Type Ven
1, John, 12, 1/1/11, B, 7.41
2, John, 12, 1/1/11, B, 7.40

When you link by MRN both records in Table 1 find both records in Table 2. That's why its producing 4 records in your query (2 records in Table 1 multiplied by 2 records each found in Table 2).

You want Table 1 Record 1 to find Table 2 Record 2 and Table 1 Record 2 to find Table 2 Record 1. Your table doesn't contain enough information to allow you to do that. You need more criteria to limit your matches, but I don't see how with the data you have given.

What data do you have that will help you in this? Or how do you know Table 1 Record 1 is to match to Table 2 Record 2?
 
You need to understand the Principals to do with Relationships.

Suggest you do some Googling and Reading.
 
Thanks Andyman,
As you can see the code does not generate what I am looking for. It takes the max from table 2 I also need to show the other number "7.40"
 

Users who are viewing this thread

Back
Top Bottom