Duplicate Records

[Sondra]

Please Help!!!

I've got three seperate tables in my database.

Table #1
Document#(PK)---------|
Document Title |
|
Table #2 |
Document#-------------|
Document Version
CC#-------------------|
|
Table #3 |
TS#(PK) |
TSEffectiveDate |
CC#(PK)---------------|

(PK) = Primary Key.

I created a query using:

DocumentID(Table #1)
Document Title (Table #1)
Document Version (Table #2)
CC#(Table #2)
TSEffectiveDate (Table #3)

My problem is:

I'm getting duplicate records and I don't know why. There
are no duplicates in any of the tables creating the
query. If I take the option TSEffectiveDate(Table #3) off
the query I get no duplicates. As you can see what
connects Table #2 to Table #3 is the CC#. I need the
TSEffectiveDate in my end result.

Can someone advise.

Thanks in advance.

Sondra
American Red Cross

 

[Jeff Boyce]

Sondra

You described the tables, but left out the relationships. From your table
descriptions, I'm guessing that one document (table1, Document#) can have
one-to-many ?versions (table2).

This would be one explanation for why you get multiple rows per Document#.

 

[Sondra]

Jeff:

Here are my relationships:

Table #1
The ultimate idea is that the Document# (Table 1) only is
entered once but their is multiple versions of the same
document (table #2). The TS#EffectiveDate identifies the
date for all document versions created by the TS#. Since
multiple documents and document versions can be created by
the TS# and associated CC# (Table 3). Table #2 can only
have one document version per TS# and associated CC#.

Hope that makes sense.

Thanks again.

Sondra

-----Original Message-----
Sondra

You described the tables, but left out the

relationships. From your table

 

[Jeff Boyce]

Sondra

If I'm understanding your description, you have a one-to-many relationship.
You are running a query that joins the one-side table to the many-side
table. I would EXPECT to see multiple rows per one-side row.

Jeff Boyce
<Access MVP>