Unwanted duplicates in query

[allyn44]

Hello--I have 2 tables (illness,event) that a need to query and create a recordset
The key fields are personId and description (text field) in each table. I also have other needed
fields in the query but these are the 2 that join the 2 tables. I also have unique id's for each
table (autoID, SeqNum). It runs ok except one issue--I have some unwanted duplicates in the result--
For each ID in either table--the description may not be unique

for instance:
Illness table:
PatientID AutoID Description
113 242 UTI
113 347 UTI

Event table:
PatientID SeqNum Description
113 1237 UTI
113 2898 UTI

I then get this in the query:
PatientID AutoID SeqNum Illness.Description Event.description
113 242 1237 UTI UTI
113 242 2898 UTI UTI
113 347 1237 UTI UTI
113 347 2898 UTI UTI

For any records where there is not more than one similar value for the description field within
each patient id--the query runs fine. For the case above they do not. There is not other field
common to both tables that I can filter by. Does anyoine know why this is happening and how I can
solve it? The data is correct in both tables.

Thank you

[Larry Linson]

Make sure you have joined the tables... looks like a "Cartesian Product",
maybe, resulting from having two tables as data sources in a query, but not
joined.

Copy and paste the SQL property of your Query -- then we'll have a better
idea.

Larry Linson
Microsoft Access MVP

<al*****@cox.net> wrote in message news:3f*************@news.west.cox.net...

Hello--I have 2 tables (illness,event) that a need to query and create a recordset The key fields are personId and description (text field) in each table. I also have other needed fields in the query but these are the 2 that join the 2 tables. I also have unique id's for each table (autoID, SeqNum). It runs ok except one issue--I have some unwanted duplicates in the result-- For each ID in either table--the description may not be unique

for instance:
Illness table:
PatientID AutoID Description
113 242 UTI
113 347 UTI

Event table:
PatientID SeqNum Description
113 1237 UTI
113 2898 UTI

I then get this in the query:
PatientID AutoID SeqNum Illness.Description Event.description
113 242 1237 UTI UTI
113 242 2898 UTI UTI
113 347 1237 UTI UTI
113 347 2898 UTI UTI

For any records where there is not more than one similar value for the description field within each patient id--the query runs fine. For the case above they do not. There is not other field common to both tables that I can filter by. Does anyoine know why this is happening and how I can solve it? The data is correct in both tables.

Thank you

[allyn44]

Thanks for responding==the SQL is below:

SELECT Illness.PatientID, Illness.Description, Event.AutoID, Event.Description, Illness.SeqNum
FROM Illness INNER JOIN Event ON (Illness.Description = Event.Description) AND (Illness.PatientID =
Event.PatientID);

On Fri, 10 Oct 2003 01:57:09 GMT, "Larry Linson" <bo*****@localhost.not> wrote:

Make sure you have joined the tables... looks like a "Cartesian Product",
maybe, resulting from having two tables as data sources in a query, but not
joined.

Copy and paste the SQL property of your Query -- then we'll have a better
idea.

Larry Linson
Microsoft Access MVP

<al*****@cox.net> wrote in message news:3f*************@news.west.cox.net...

Hello--I have 2 tables (illness,event) that a need to query and create a

recordset

The key fields are personId and description (text field) in each table. I

also have other needed

fields in the query but these are the 2 that join the 2 tables. I also

have unique id's for each

table (autoID, SeqNum). It runs ok except one issue--I have some unwanted

duplicates in the result--

For each ID in either table--the description may not be unique

for instance:
Illness table:
PatientID AutoID Description
113 242 UTI
113 347 UTI

Event table:
PatientID SeqNum Description
113 1237 UTI
113 2898 UTI

I then get this in the query:
PatientID AutoID SeqNum Illness.Description Event.description
113 242 1237 UTI UTI
113 242 2898 UTI UTI
113 347 1237 UTI UTI
113 347 2898 UTI UTI

For any records where there is not more than one similar value for the

description field within

each patient id--the query runs fine. For the case above they do not.

There is not other field

common to both tables that I can filter by. Does anyoine know why this is

happening and how I can

solve it? The data is correct in both tables.

Thank you