unique records

[Nancy]

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?

 

[Van T. Dinh]

"... 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.

 

[Nancy]

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?

 

[smk23]

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

 

[John Vinson]

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?

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]

 

[Nancy]

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;

 

[John Vinson]

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;

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]

 

[Nancy]

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.
--
nhb -- nc

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;

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]