Report Queries

[Zanstemic]

Being new to access and limited experience with SQL , I'm finding that the
execution of queries can be tricky. I'm looking for guidance on how to best
approach or solve a problem.

I created a simple Select Query that Joins multiple Tables together for the
Control Source of a Report. This populates the Field List in Reports nicely.
However, the data being returned by the query has duplicate entries. I'm not
sure if I'm approaching it incorrectly or just have a simple problem with my
query.

I've been reading about Calculted Field Misinterpretated and not sure if the
return from the query is a simple issue or one where I need to redesign. Any
suggestions?

SELECT TPhysicians.TFirstName, TPhysicians.TLastName, TPhysicians.TTitle,
Client.[Client Name], Attendees.AttendeeFirstName, Attendees.CaseNumber,
Registration.[Date Scheduled], TPhysicians.TZip
FROM ((TPhysicians INNER JOIN Attendees ON TPhysicians.TPhysicians =
Attendees.TPhysicians) INNER JOIN Client ON Attendees.ClientID =
Client.ClientID) INNER JOIN ((Registration INNER JOIN Events ON
Registration.EventID = Events.EventID) INNER JOIN RegistrationEventType ON
Events.EventID = RegistrationEventType.EventID) ON Attendees.AttendeeID =
Registration.AttendeeID;


Any references or suggestions are appreciated.

 

[Allen Browne]

Your query uses 5 tables, so if a person has multiple records in some of
those tables, they will turn up on multiple rows of the query, and so
multiple times in the report.

Typically, you want to group your report by some fields.
In report design view, open the Sorting And Grouping box (View menu.)
Select the field you want to group by, and in the lower pane of the dialog,
choose Yes for Group Header. Put those fields in the group header section,
and they will turn up just one, above the record that relate to that
grouping.

 

[Zanstemic]

I did the grouping but I'm not sure about the last step. "Put those fields in
the group header section, and they will turn up just one"

I did the sorting and grouping for one field "CaseNumber" and placed that
field in the CaseNumber header that shows in design view. The result appears
the same so most likely I'm missing something.

Your query uses 5 tables, so if a person has multiple records in some of
those tables, they will turn up on multiple rows of the query, and so
multiple times in the report.

Typically, you want to group your report by some fields.
In report design view, open the Sorting And Grouping box (View menu.)
Select the field you want to group by, and in the lower pane of the dialog,
choose Yes for Group Header. Put those fields in the group header section,
and they will turn up just one, above the record that relate to that
grouping.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Being new to access and limited experience with SQL , I'm finding that the
execution of queries can be tricky. I'm looking for guidance on how to
best
approach or solve a problem.

I created a simple Select Query that Joins multiple Tables together for
the
Control Source of a Report. This populates the Field List in Reports
nicely.
However, the data being returned by the query has duplicate entries. I'm
not
sure if I'm approaching it incorrectly or just have a simple problem with
my
query.

I've been reading about Calculted Field Misinterpretated and not sure if
the
return from the query is a simple issue or one where I need to redesign.
Any
suggestions?

SELECT TPhysicians.TFirstName, TPhysicians.TLastName, TPhysicians.TTitle,
Client.[Client Name], Attendees.AttendeeFirstName, Attendees.CaseNumber,
Registration.[Date Scheduled], TPhysicians.TZip
FROM ((TPhysicians INNER JOIN Attendees ON TPhysicians.TPhysicians =
Attendees.TPhysicians) INNER JOIN Client ON Attendees.ClientID =
Client.ClientID) INNER JOIN ((Registration INNER JOIN Events ON
Registration.EventID = Events.EventID) INNER JOIN RegistrationEventType ON
Events.EventID = RegistrationEventType.EventID) ON Attendees.AttendeeID =
Registration.AttendeeID;


Any references or suggestions are appreciated.

 

[Allen Browne]

In report design, you can now see a section headed by a grey bar named:
CaseNumber Header
If you put the case number field in there, it should turn up only once in
the report (unless you have other group headers above that.)

I notice that CaseNumber is not one of the fields that your query joins on.
Would it be feasible to use the AttendeeID in the group header?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I did the grouping but I'm not sure about the last step. "Put those fields
in
the group header section, and they will turn up just one"

I did the sorting and grouping for one field "CaseNumber" and placed that
field in the CaseNumber header that shows in design view. The result
appears
the same so most likely I'm missing something.

Your query uses 5 tables, so if a person has multiple records in some of
those tables, they will turn up on multiple rows of the query, and so
multiple times in the report.

Typically, you want to group your report by some fields.
In report design view, open the Sorting And Grouping box (View menu.)
Select the field you want to group by, and in the lower pane of the
dialog,
choose Yes for Group Header. Put those fields in the group header
section,
and they will turn up just one, above the record that relate to that
grouping.

Being new to access and limited experience with SQL , I'm finding that
the
execution of queries can be tricky. I'm looking for guidance on how to
best
approach or solve a problem.

I created a simple Select Query that Joins multiple Tables together for
the
Control Source of a Report. This populates the Field List in Reports
nicely.
However, the data being returned by the query has duplicate entries.
I'm
not
sure if I'm approaching it incorrectly or just have a simple problem
with
my
query.

I've been reading about Calculted Field Misinterpretated and not sure
if
the
return from the query is a simple issue or one where I need to
redesign.
Any
suggestions?

SELECT TPhysicians.TFirstName, TPhysicians.TLastName,
TPhysicians.TTitle,
Client.[Client Name], Attendees.AttendeeFirstName,
Attendees.CaseNumber,
Registration.[Date Scheduled], TPhysicians.TZip
FROM ((TPhysicians INNER JOIN Attendees ON TPhysicians.TPhysicians =
Attendees.TPhysicians) INNER JOIN Client ON Attendees.ClientID =
Client.ClientID) INNER JOIN ((Registration INNER JOIN Events ON
Registration.EventID = Events.EventID) INNER JOIN RegistrationEventType
ON
Events.EventID = RegistrationEventType.EventID) ON Attendees.AttendeeID
=
Registration.AttendeeID;

 

[Zanstemic]

Allen, thanks so much for taking the time. It works with AttendeeID.

Very well done. On to the next wall

In report design, you can now see a section headed by a grey bar named:
CaseNumber Header
If you put the case number field in there, it should turn up only once in
the report (unless you have other group headers above that.)

I notice that CaseNumber is not one of the fields that your query joins on.
Would it be feasible to use the AttendeeID in the group header?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I did the grouping but I'm not sure about the last step. "Put those fields
in
the group header section, and they will turn up just one"

I did the sorting and grouping for one field "CaseNumber" and placed that
field in the CaseNumber header that shows in design view. The result
appears
the same so most likely I'm missing something.

Your query uses 5 tables, so if a person has multiple records in some of
those tables, they will turn up on multiple rows of the query, and so
multiple times in the report.

Typically, you want to group your report by some fields.
In report design view, open the Sorting And Grouping box (View menu.)
Select the field you want to group by, and in the lower pane of the
dialog,
choose Yes for Group Header. Put those fields in the group header
section,
and they will turn up just one, above the record that relate to that
grouping.

Being new to access and limited experience with SQL , I'm finding that
the
execution of queries can be tricky. I'm looking for guidance on how to
best
approach or solve a problem.

I created a simple Select Query that Joins multiple Tables together for
the
Control Source of a Report. This populates the Field List in Reports
nicely.
However, the data being returned by the query has duplicate entries.
I'm
not
sure if I'm approaching it incorrectly or just have a simple problem
with
my
query.

I've been reading about Calculted Field Misinterpretated and not sure
if
the
return from the query is a simple issue or one where I need to
redesign.
Any
suggestions?

SELECT TPhysicians.TFirstName, TPhysicians.TLastName,
TPhysicians.TTitle,
Client.[Client Name], Attendees.AttendeeFirstName,
Attendees.CaseNumber,
Registration.[Date Scheduled], TPhysicians.TZip
FROM ((TPhysicians INNER JOIN Attendees ON TPhysicians.TPhysicians =
Attendees.TPhysicians) INNER JOIN Client ON Attendees.ClientID =
Client.ClientID) INNER JOIN ((Registration INNER JOIN Events ON
Registration.EventID = Events.EventID) INNER JOIN RegistrationEventType
ON
Events.EventID = RegistrationEventType.EventID) ON Attendees.AttendeeID
=
Registration.AttendeeID;

 

[Zanstemic]

Hi Allen,
After some initial testing, I started to get some mixed results. It appears
that the query is returning multiple values of AttendeeID and the report is
showing each page in the query. Actually, they become multple pages of the
same page since the AttendeeID is unique. It's based on the number of times
the item shows in the query.

I really appreciate the help ..........

Allen, thanks so much for taking the time. It works with AttendeeID.

Very well done. On to the next wall

In report design, you can now see a section headed by a grey bar named:
CaseNumber Header
If you put the case number field in there, it should turn up only once in
the report (unless you have other group headers above that.)

I notice that CaseNumber is not one of the fields that your query joins on.
Would it be feasible to use the AttendeeID in the group header?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I did the grouping but I'm not sure about the last step. "Put those fields
in
the group header section, and they will turn up just one"

I did the sorting and grouping for one field "CaseNumber" and placed that
field in the CaseNumber header that shows in design view. The result
appears
the same so most likely I'm missing something.

:

Your query uses 5 tables, so if a person has multiple records in some of
those tables, they will turn up on multiple rows of the query, and so
multiple times in the report.

Typically, you want to group your report by some fields.
In report design view, open the Sorting And Grouping box (View menu.)
Select the field you want to group by, and in the lower pane of the
dialog,
choose Yes for Group Header. Put those fields in the group header
section,
and they will turn up just one, above the record that relate to that
grouping.

Being new to access and limited experience with SQL , I'm finding that
the
execution of queries can be tricky. I'm looking for guidance on how to
best
approach or solve a problem.

I created a simple Select Query that Joins multiple Tables together for
the
Control Source of a Report. This populates the Field List in Reports
nicely.
However, the data being returned by the query has duplicate entries.
I'm
not
sure if I'm approaching it incorrectly or just have a simple problem
with
my
query.

I've been reading about Calculted Field Misinterpretated and not sure
if
the
return from the query is a simple issue or one where I need to
redesign.
Any
suggestions?

SELECT TPhysicians.TFirstName, TPhysicians.TLastName,
TPhysicians.TTitle,
Client.[Client Name], Attendees.AttendeeFirstName,
Attendees.CaseNumber,
Registration.[Date Scheduled], TPhysicians.TZip
FROM ((TPhysicians INNER JOIN Attendees ON TPhysicians.TPhysicians =
Attendees.TPhysicians) INNER JOIN Client ON Attendees.ClientID =
Client.ClientID) INNER JOIN ((Registration INNER JOIN Events ON
Registration.EventID = Events.EventID) INNER JOIN RegistrationEventType
ON
Events.EventID = RegistrationEventType.EventID) ON Attendees.AttendeeID
=
Registration.AttendeeID;

 

[Zanstemic]

Your initial instructions solved the problem. I must have been a little tired.

By moving all the fields into the header area, it allows for the grouping to
work logically.

Thanks so much

Hi Allen,
After some initial testing, I started to get some mixed results. It appears
that the query is returning multiple values of AttendeeID and the report is
showing each page in the query. Actually, they become multple pages of the
same page since the AttendeeID is unique. It's based on the number of times
the item shows in the query.

I really appreciate the help ..........

Allen, thanks so much for taking the time. It works with AttendeeID.

Very well done. On to the next wall

In report design, you can now see a section headed by a grey bar named:
CaseNumber Header
If you put the case number field in there, it should turn up only once in
the report (unless you have other group headers above that.)

I notice that CaseNumber is not one of the fields that your query joins on.
Would it be feasible to use the AttendeeID in the group header?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I did the grouping but I'm not sure about the last step. "Put those fields
in
the group header section, and they will turn up just one"

I did the sorting and grouping for one field "CaseNumber" and placed that
field in the CaseNumber header that shows in design view. The result
appears
the same so most likely I'm missing something.

:

Your query uses 5 tables, so if a person has multiple records in some of
those tables, they will turn up on multiple rows of the query, and so
multiple times in the report.

Typically, you want to group your report by some fields.
In report design view, open the Sorting And Grouping box (View menu.)
Select the field you want to group by, and in the lower pane of the
dialog,
choose Yes for Group Header. Put those fields in the group header
section,
and they will turn up just one, above the record that relate to that
grouping.

Being new to access and limited experience with SQL , I'm finding that
the
execution of queries can be tricky. I'm looking for guidance on how to
best
approach or solve a problem.

I created a simple Select Query that Joins multiple Tables together for
the
Control Source of a Report. This populates the Field List in Reports
nicely.
However, the data being returned by the query has duplicate entries.
I'm
not
sure if I'm approaching it incorrectly or just have a simple problem
with
my
query.

I've been reading about Calculted Field Misinterpretated and not sure
if
the
return from the query is a simple issue or one where I need to
redesign.
Any
suggestions?

SELECT TPhysicians.TFirstName, TPhysicians.TLastName,
TPhysicians.TTitle,
Client.[Client Name], Attendees.AttendeeFirstName,
Attendees.CaseNumber,
Registration.[Date Scheduled], TPhysicians.TZip
FROM ((TPhysicians INNER JOIN Attendees ON TPhysicians.TPhysicians =
Attendees.TPhysicians) INNER JOIN Client ON Attendees.ClientID =
Client.ClientID) INNER JOIN ((Registration INNER JOIN Events ON
Registration.EventID = Events.EventID) INNER JOIN RegistrationEventType
ON
Events.EventID = RegistrationEventType.EventID) ON Attendees.AttendeeID
=
Registration.AttendeeID;

 

[sumthin_Missin]

Having a similar problem with duplicate rows turning up on a report.
I have a main report with subreports under each main report row.
under the first main report row, I get three subreports, without duplicates.
Okay.

Under the second main report row, I get 4 subreports where there should be
only 2 -- there are 2 duplcates, even though no duplicates exist in the
table.

Tried grouping subreport on the linked field. Tried putting all subreport
fields into the group header. no luck.
Main report is already grouped on a different field and all fields are in
this group header. Only the subreport is in the detail section.
Any ideas?

 

[Lord Kelvan]

on the query that the report is based on go into it and goto sql view
and type select distinct instead of select

 

[sumthin_Missin]

Thanks for the tip but that seemed to make the duplicates worse.