Query generating duplicate (1 Viewer)

ielamrani

Registered User.
Local time
Today, 07:09
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:

Addyman

Registered User.
Local time
Today, 04:09
Joined
Dec 29, 2011
Messages
90
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
 

plog

Banishment Pending
Local time
Today, 06:09
Joined
May 11, 2011
Messages
11,611
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?
 

RainLover

VIP From a land downunder
Local time
Today, 22:09
Joined
Jan 5, 2009
Messages
5,041
You need to understand the Principals to do with Relationships.

Suggest you do some Googling and Reading.
 

ielamrani

Registered User.
Local time
Today, 07:09
Joined
May 6, 2008
Messages
51
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

Top Bottom