unique records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nancy - 07 May 2005 02:46 GMT

My conference program database includes presenters and presentations. Some
presenters do more than one presentation and some presentations have more
than one presenter. I can select unique records by setting the query
property for unique value to yes. It works for mailing labels and does not
include more than one label for each presenter. However, when I attempt to
print a report that provides more information, some of the presenters are
listed twice. I don't understand why some duplicates are removed as expected
but a few of the presenters have duplicate listings in the report. I have
checked other fields but do not see a pattern. The Presenter ID and
Presentation ID are primary keys set to a one-to-many relationship. Can
someone help?

Signature

nhb -- nc

Reply to this Message

Van T. Dinh - 07 May 2005 10:52 GMT

"... The Presenter ID and Presentation ID are primary keys set to a
one-to-many relationship. ..."

From your description, this should be a Many-to-Many relationship, not
One-to-Many.

Obviously, there are differences in the Query for mailing labels and the
Query for the Report.

You need to describe the relevant details of the 2 Tables, PKs & FKs and
post the SQL Strings of the 2 Queries so that potential respondents can see
what you need, the differences of the 2 queries to suggest possible
solutions.

Signature

HTH
Van T. Dinh
MVP (Access)

> My conference program database includes presenters and presentations. Some
> presenters do more than one presentation and some presentations have more
[quoted text clipped - 7 lines]
> Presentation ID are primary keys set to a one-to-many relationship. Can
> someone help?

Reply to this Message

Nancy - 07 May 2005 14:55 GMT

Thanks for responding. I know you mean "primary key" by PK, but I don't know
what you mean by "FK." Also, I agree that I really need a many-to-many
relationship but I have not been able to set that up. I know I need a join
table or something but all my efforts have failed. Can you help?

Signature

nhb -- nc

> "... The Presenter ID and Presentation ID are primary keys set to a
> one-to-many relationship. ..."
[quoted text clipped - 25 lines]
> > Presentation ID are primary keys set to a one-to-many relationship. Can
> > someone help?

Reply to this Message

smk23 - 07 May 2005 19:09 GMT

Nancy:
FK is foreign key. For example

Table: Presenters
PresenterID (primary key)
Name
AnotherField

Table: Presentations
PresentationID (primary key)
NameofPresentation
AnotherField

Join Table:
PresenterID
PresentationID

Change your tables to look something like this or post your table
definitions. You don't need to post all the fields in each table, just the
primary keys and the first couple of fields in each table.

HTH,
Sam

> Thanks for responding. I know you mean "primary key" by PK, but I don't know
> what you mean by "FK." Also, I agree that I really need a many-to-many
[quoted text clipped - 30 lines]
> > > Presentation ID are primary keys set to a one-to-many relationship. Can
> > > someone help?

Reply to this Message

John Vinson - 07 May 2005 23:48 GMT

>My conference program database includes presenters and presentations. Some
>presenters do more than one presentation and some presentations have more
[quoted text clipped - 7 lines]
>Presentation ID are primary keys set to a one-to-many relationship. Can
>someone help?

Please post the SQL view of the query which is displaying multiple
records. You're probably including some field (such as a join ID?)
which causes the records to be "different" when to you they should be
considered the same.

John W. Vinson[MVP]

Reply to this Message

Nancy - 09 May 2005 14:10 GMT

OK -- the SQL statement:

SELECT DISTINCT [ConfTBL--Presenters].PresenterID,
[ConfTBL--Presenters].Last_Name, [ConfTBL--Presenters].First_Name,
[ConfTBL--Presenters].Middle_Name, [First_Name] & " " & [Middle_Name] & " " &
[Last_Name] AS Name, [ConfTBL--Presentations].Presenter2,
[ConfTBL--Presentations].Presentation_Title, [ConfTBL--Presenters].WkPhone,
[ConfTBL--Presenters].Extension, [ConfTBL--Presenters].HmPhone,
[ConfTBL--Presenters].CellPhone, [ConfTBL--Presenters].[Fax Number],
[ConfTBL--Presenters].Email, [ConfTBL--Presentations].ConferenceYear
FROM [ConfTBL--Presenters] INNER JOIN [ConfTBL--Presentations] ON
[ConfTBL--Presenters].PresenterID = [ConfTBL--Presentations].Pres1ID
WHERE ((([ConfTBL--Presentations].ConferenceYear)=[What Year?]))
ORDER BY [ConfTBL--Presenters].Last_Name, [ConfTBL--Presenters].First_Name,
[ConfTBL--Presenters].Middle_Name;

Signature

nhb -- nc

> >My conference program database includes presenters and presentations. Some
> >presenters do more than one presentation and some presentations have more
[quoted text clipped - 14 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 09 May 2005 18:10 GMT

>OK -- the SQL statement:
>
[quoted text clipped - 11 lines]
>ORDER BY [ConfTBL--Presenters].Last_Name, [ConfTBL--Presenters].First_Name,
>[ConfTBL--Presenters].Middle_Name;

Well, you're including fields Presentation_Titls, Presenter2, and
ConfernceYear from the ConfTBL--Presentations table in the query. If a
given presenter gives four different presentations, this Query will
(correctly) have four records, one for each presentation.

What would you want to see? An arbitrary one of the presentations? I
think you're getting exactly what you should expect to get!

John W. Vinson[MVP]

Reply to this Message

Nancy - 11 May 2005 17:50 GMT

OK - thanks. I was afraid that might be the case. I wanted to produce a
"directory" of presenters and needed only limited info for the user but some
of it was in the presentations table. Oh well. Many thanks for your help.

Signature

nhb -- nc

> >OK -- the SQL statement:
> >
[quoted text clipped - 21 lines]
>
> John W. Vinson[MVP]

Reply to this Message