Query duplicates results due to many-to-many relationships

[Lee Ann]

I have a query with fields from different tables. Two or three of these
field may have one or more entries. When I run the query, if it displays a
record where there are multiple entries for a particular field, it will list
the record multiple times. An example: This DB involves officers using
force - if there is more than one officer involved, each one will be listed
with the applicable level of force and if the subject is arrested, there may
be one or more charges enterer - assuming there are 2 officers with 3
charges, that record may be listed 5 or 6 times, but the multiple information
will be correct (i.e., different officers are listed with the different
charges).

I'm not sure how to go about only showing one record with the multiple
information pertinent to the record - if this should be corrected in the
query or the way the results would be shown on a report based on the query.

Here is the SQL information:

SELECT TblIncident.DateTimeofIncident, TblIncident.NatureofIncident,
TblLocationType.LocationType, TblCity.City, TblIncident.Sector,
[DeputyLastName] & "," & [DeputyFirstName] AS Expr1, TblShift.Shift,
TblTypeofForce.TypeofForce, TblSuspect.SuspectAge,
TblNationality.Nationality, TblDemeanorType.DemeanorType,
TblDisposition.Disposition, TblCharges.Charges
FROM TblShift INNER JOIN (TblTypeofForce INNER JOIN ((TblNationality INNER
JOIN (TblDemeanorType INNER JOIN TblSuspect ON TblDemeanorType.DemeanorTypeID
= TblSuspect.DemeanorTypeID) ON TblNationality.NationalityID =
TblSuspect.NationalityID) INNER JOIN (TblLocationType INNER JOIN (((TblCity
INNER JOIN TblIncident ON TblCity.CityID = TblIncident.CityID) INNER JOIN
(TblDeputy INNER JOIN TblIncidentDeputy ON TblDeputy.DeputyBadgeNumberID =
TblIncidentDeputy.DeputyBadgeNumberID) ON TblIncident.IncidentNumberID =
TblIncidentDeputy.IncidentNumberID) INNER JOIN ((TblDisposition INNER JOIN
TblIncidentSuspect ON TblDisposition.DispositionID =
TblIncidentSuspect.DispositionID) INNER JOIN (TblCharges INNER JOIN
TblIncidentSuspectCharges ON TblCharges.ChargesID =
TblIncidentSuspectCharges.ChargesID) ON TblIncidentSuspect.IncidentSuspectID
= TblIncidentSuspectCharges.IncidentSuspectID) ON
TblIncident.IncidentNumberID = TblIncidentSuspect.IncidentNumberID) ON
TblLocationType.LocationTypeID = TblIncident.LocationTypeID) ON
TblSuspect.SuspectID = TblIncidentSuspect.SuspectID) ON
TblTypeofForce.TypeofForceID = TblIncidentDeputy.TypeofForceID) ON
TblShift.ShiftID = TblIncidentDeputy.ShiftID;

Thanks in advance for any assistance.

 

[Jeff Boyce]

Lee Ann

It really depends on what you are trying to show ... and be aware that
queries "grab" the data, but reports (or forms) display it.

So it really shouldn't matter if some of the fields are duplicated in the
query results because you can use report features to "hide duplicates", or
use the Sorting/Grouping feature in reports to create a "GroupBy" section,
with details beneath it.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a query with fields from different tables. Two or three of these
field may have one or more entries. When I run the query, if it displays
a
record where there are multiple entries for a particular field, it will
list
the record multiple times. An example: This DB involves officers using
force - if there is more than one officer involved, each one will be
listed
with the applicable level of force and if the subject is arrested, there
may
be one or more charges enterer - assuming there are 2 officers with 3
charges, that record may be listed 5 or 6 times, but the multiple
information
will be correct (i.e., different officers are listed with the different
charges).

I'm not sure how to go about only showing one record with the multiple
information pertinent to the record - if this should be corrected in the
query or the way the results would be shown on a report based on the
query.

Here is the SQL information:

SELECT TblIncident.DateTimeofIncident, TblIncident.NatureofIncident,
TblLocationType.LocationType, TblCity.City, TblIncident.Sector,
[DeputyLastName] & "," & [DeputyFirstName] AS Expr1, TblShift.Shift,
TblTypeofForce.TypeofForce, TblSuspect.SuspectAge,
TblNationality.Nationality, TblDemeanorType.DemeanorType,
TblDisposition.Disposition, TblCharges.Charges
FROM TblShift INNER JOIN (TblTypeofForce INNER JOIN ((TblNationality INNER
JOIN (TblDemeanorType INNER JOIN TblSuspect ON
TblDemeanorType.DemeanorTypeID
= TblSuspect.DemeanorTypeID) ON TblNationality.NationalityID =
TblSuspect.NationalityID) INNER JOIN (TblLocationType INNER JOIN
(((TblCity
INNER JOIN TblIncident ON TblCity.CityID = TblIncident.CityID) INNER JOIN
(TblDeputy INNER JOIN TblIncidentDeputy ON TblDeputy.DeputyBadgeNumberID =
TblIncidentDeputy.DeputyBadgeNumberID) ON TblIncident.IncidentNumberID =
TblIncidentDeputy.IncidentNumberID) INNER JOIN ((TblDisposition INNER JOIN
TblIncidentSuspect ON TblDisposition.DispositionID =
TblIncidentSuspect.DispositionID) INNER JOIN (TblCharges INNER JOIN
TblIncidentSuspectCharges ON TblCharges.ChargesID =
TblIncidentSuspectCharges.ChargesID) ON
TblIncidentSuspect.IncidentSuspectID
= TblIncidentSuspectCharges.IncidentSuspectID) ON
TblIncident.IncidentNumberID = TblIncidentSuspect.IncidentNumberID) ON
TblLocationType.LocationTypeID = TblIncident.LocationTypeID) ON
TblSuspect.SuspectID = TblIncidentSuspect.SuspectID) ON
TblTypeofForce.TypeofForceID = TblIncidentDeputy.TypeofForceID) ON
TblShift.ShiftID = TblIncidentDeputy.ShiftID;

Thanks in advance for any assistance.

 

[Lee Ann]

Jeff,

I've looked at the grouping/sorting and hide duplicates that you've
suggested. I don't know that either of these is what I'm looking for (unless
I'm not doing something correctly). As an quick example of what I now see:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
05/05/2007 Bar/Nightclub Threats
05/05/2007 Bar/Nightclub Harassment
10/14/2007 Highway None


and the way I'd like to see it listed on a report:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
Threats
Harassment
10/14/2007 Highway None


If there are multiple "entries" (arrest charge), I'd rather see the date,
location (or other fields) listed only one.

Lee Ann

It really depends on what you are trying to show ... and be aware that
queries "grab" the data, but reports (or forms) display it.

So it really shouldn't matter if some of the fields are duplicated in the
query results because you can use report features to "hide duplicates", or
use the Sorting/Grouping feature in reports to create a "GroupBy" section,
with details beneath it.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a query with fields from different tables. Two or three of these
field may have one or more entries. When I run the query, if it displays
a
record where there are multiple entries for a particular field, it will
list
the record multiple times. An example: This DB involves officers using
force - if there is more than one officer involved, each one will be
listed
with the applicable level of force and if the subject is arrested, there
may
be one or more charges enterer - assuming there are 2 officers with 3
charges, that record may be listed 5 or 6 times, but the multiple
information
will be correct (i.e., different officers are listed with the different
charges).

I'm not sure how to go about only showing one record with the multiple
information pertinent to the record - if this should be corrected in the
query or the way the results would be shown on a report based on the
query.

Here is the SQL information:

SELECT TblIncident.DateTimeofIncident, TblIncident.NatureofIncident,
TblLocationType.LocationType, TblCity.City, TblIncident.Sector,
[DeputyLastName] & "," & [DeputyFirstName] AS Expr1, TblShift.Shift,
TblTypeofForce.TypeofForce, TblSuspect.SuspectAge,
TblNationality.Nationality, TblDemeanorType.DemeanorType,
TblDisposition.Disposition, TblCharges.Charges
FROM TblShift INNER JOIN (TblTypeofForce INNER JOIN ((TblNationality INNER
JOIN (TblDemeanorType INNER JOIN TblSuspect ON
TblDemeanorType.DemeanorTypeID
= TblSuspect.DemeanorTypeID) ON TblNationality.NationalityID =
TblSuspect.NationalityID) INNER JOIN (TblLocationType INNER JOIN
(((TblCity
INNER JOIN TblIncident ON TblCity.CityID = TblIncident.CityID) INNER JOIN
(TblDeputy INNER JOIN TblIncidentDeputy ON TblDeputy.DeputyBadgeNumberID =
TblIncidentDeputy.DeputyBadgeNumberID) ON TblIncident.IncidentNumberID =
TblIncidentDeputy.IncidentNumberID) INNER JOIN ((TblDisposition INNER JOIN
TblIncidentSuspect ON TblDisposition.DispositionID =
TblIncidentSuspect.DispositionID) INNER JOIN (TblCharges INNER JOIN
TblIncidentSuspectCharges ON TblCharges.ChargesID =
TblIncidentSuspectCharges.ChargesID) ON
TblIncidentSuspect.IncidentSuspectID
= TblIncidentSuspectCharges.IncidentSuspectID) ON
TblIncident.IncidentNumberID = TblIncidentSuspect.IncidentNumberID) ON
TblLocationType.LocationTypeID = TblIncident.LocationTypeID) ON
TblSuspect.SuspectID = TblIncidentSuspect.SuspectID) ON
TblTypeofForce.TypeofForceID = TblIncidentDeputy.TypeofForceID) ON
TblShift.ShiftID = TblIncidentDeputy.ShiftID;

Thanks in advance for any assistance.

.

 

[Jeff Boyce]

Open the report definition in design view.

Click on the Date control (by the way, "Date" is a reserved word in Access).
Open the properties window for that control. Find the Hide Duplicates
property and make it "Yes".

Do the same for the "Location" control.

Try it now.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff,

I've looked at the grouping/sorting and hide duplicates that you've
suggested. I don't know that either of these is what I'm looking for
(unless
I'm not doing something correctly). As an quick example of what I now
see:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
05/05/2007 Bar/Nightclub Threats
05/05/2007 Bar/Nightclub Harassment
10/14/2007 Highway None


and the way I'd like to see it listed on a report:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
Threats

Harassment
10/14/2007 Highway None


If there are multiple "entries" (arrest charge), I'd rather see the date,
location (or other fields) listed only one.

Lee Ann

It really depends on what you are trying to show ... and be aware that
queries "grab" the data, but reports (or forms) display it.

So it really shouldn't matter if some of the fields are duplicated in the
query results because you can use report features to "hide duplicates",
or
use the Sorting/Grouping feature in reports to create a "GroupBy"
section,
with details beneath it.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a query with fields from different tables. Two or three of these
field may have one or more entries. When I run the query, if it
displays
a
record where there are multiple entries for a particular field, it will
list
the record multiple times. An example: This DB involves officers
using
force - if there is more than one officer involved, each one will be
listed
with the applicable level of force and if the subject is arrested,
there
may
be one or more charges enterer - assuming there are 2 officers with 3
charges, that record may be listed 5 or 6 times, but the multiple
information
will be correct (i.e., different officers are listed with the different
charges).

I'm not sure how to go about only showing one record with the multiple
information pertinent to the record - if this should be corrected in
the
query or the way the results would be shown on a report based on the
query.

Here is the SQL information:

SELECT TblIncident.DateTimeofIncident, TblIncident.NatureofIncident,
TblLocationType.LocationType, TblCity.City, TblIncident.Sector,
[DeputyLastName] & "," & [DeputyFirstName] AS Expr1, TblShift.Shift,
TblTypeofForce.TypeofForce, TblSuspect.SuspectAge,
TblNationality.Nationality, TblDemeanorType.DemeanorType,
TblDisposition.Disposition, TblCharges.Charges
FROM TblShift INNER JOIN (TblTypeofForce INNER JOIN ((TblNationality
INNER
JOIN (TblDemeanorType INNER JOIN TblSuspect ON
TblDemeanorType.DemeanorTypeID
= TblSuspect.DemeanorTypeID) ON TblNationality.NationalityID =
TblSuspect.NationalityID) INNER JOIN (TblLocationType INNER JOIN
(((TblCity
INNER JOIN TblIncident ON TblCity.CityID = TblIncident.CityID) INNER
JOIN
(TblDeputy INNER JOIN TblIncidentDeputy ON
TblDeputy.DeputyBadgeNumberID =
TblIncidentDeputy.DeputyBadgeNumberID) ON TblIncident.IncidentNumberID
=
TblIncidentDeputy.IncidentNumberID) INNER JOIN ((TblDisposition INNER
JOIN
TblIncidentSuspect ON TblDisposition.DispositionID =
TblIncidentSuspect.DispositionID) INNER JOIN (TblCharges INNER JOIN
TblIncidentSuspectCharges ON TblCharges.ChargesID =
TblIncidentSuspectCharges.ChargesID) ON
TblIncidentSuspect.IncidentSuspectID
= TblIncidentSuspectCharges.IncidentSuspectID) ON
TblIncident.IncidentNumberID = TblIncidentSuspect.IncidentNumberID) ON
TblLocationType.LocationTypeID = TblIncident.LocationTypeID) ON
TblSuspect.SuspectID = TblIncidentSuspect.SuspectID) ON
TblTypeofForce.TypeofForceID = TblIncidentDeputy.TypeofForceID) ON
TblShift.ShiftID = TblIncidentDeputy.ShiftID;

Thanks in advance for any assistance.

.

 

[BruceM via AccessMonster.com]

In the Report Sorting and Grouping dialog (View >> Sorting and Grouping),
group by Date, then Location. Create a group header for each. Place a text
box bound to the Date field (which you shouldn't call Date, by the way, as
Date is a reserved word and could cause problems in some cases. If you must
use it as a field name, always surround it with square brackets).

In the Location group header place a text box bound to the Location field.

Run the report and see if you are headed in the right direction.

Jeff,

I've looked at the grouping/sorting and hide duplicates that you've
suggested. I don't know that either of these is what I'm looking for (unless
I'm not doing something correctly). As an quick example of what I now see:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
05/05/2007 Bar/Nightclub Threats
05/05/2007 Bar/Nightclub Harassment
10/14/2007 Highway None

and the way I'd like to see it listed on a report:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
Threats
Harassment
10/14/2007 Highway None


If there are multiple "entries" (arrest charge), I'd rather see the date,
location (or other fields) listed only one.

Lee Ann

[quoted text clipped - 67 lines]

.

 

[KenSheridan via AccessMonster.com]

Lee Ann:

The drawback of using the HideDuplicates property is that if two incidents on
successive dates occur at the same location then, while the new date value
will be displayed, the location value will still be suppressed, which I doubt
you'd want.

A better option is to do as Bruce says and group the report first by date
then by location, giving each group a group header, Include the date and
location controls in their respective headers. In the Format event procedure
of each group header section put the following line of code;

MoveLayout = False

This will cause the group header controls to print on the same lines as the
first detail, giving you the desired layout, but showing the date and
location on the first line of each distinct date regardless of whether the
location differs or not.

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
Threats

Harassment
10/14/2007 Bar/Nightclub None

However, you might need to differentiate between two incidents on one date,
in which case you'll need to bring the incident table's primary key into play
and group the report first on the date, then on the primary key (IncidentID
say), and then on the location. In this case give only the IncidentID and
Location group's a group header and put the date control in the former. The
same code is used in the Format event procedures of the header sections as
before. You'd then get results like this:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
Threats

Harassment
05/05/2007 Bar/Nightclub GBH
10/14/2007 Highway None

Where the assault/threats/harassment on 05/05/2007 constitute one incident
and the GBH a separate incident on 05/05/2007.

Ken Sheridan
Stafford, England

Jeff,

I've looked at the grouping/sorting and hide duplicates that you've
suggested. I don't know that either of these is what I'm looking for (unless
I'm not doing something correctly). As an quick example of what I now see:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
05/05/2007 Bar/Nightclub Threats
05/05/2007 Bar/Nightclub Harassment
10/14/2007 Highway None

and the way I'd like to see it listed on a report:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
Threats
Harassment
10/14/2007 Highway None


If there are multiple "entries" (arrest charge), I'd rather see the date,
location (or other fields) listed only one.

Lee Ann

[quoted text clipped - 67 lines]

.

 

[Lee Ann]

The hide duplicates may work on a small scale, however it hides too much if I
have other records with the same information. The sorting/grouping also
doesn't seem to give me the format I'm looking for.

The use of the word Date was mentioned - this is a caption on the report,
but the field name is actually DateofIncident. Does the same rule apply in
this case?

As always, thanks for all the advice you guys give!

In the Report Sorting and Grouping dialog (View >> Sorting and Grouping),
group by Date, then Location. Create a group header for each. Place a text
box bound to the Date field (which you shouldn't call Date, by the way, as
Date is a reserved word and could cause problems in some cases. If you must
use it as a field name, always surround it with square brackets).

In the Location group header place a text box bound to the Location field.

Run the report and see if you are headed in the right direction.

Jeff,

I've looked at the grouping/sorting and hide duplicates that you've
suggested. I don't know that either of these is what I'm looking for (unless
I'm not doing something correctly). As an quick example of what I now see:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
05/05/2007 Bar/Nightclub Threats
05/05/2007 Bar/Nightclub Harassment
10/14/2007 Highway None

and the way I'd like to see it listed on a report:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
Threats
Harassment
10/14/2007 Highway None


If there are multiple "entries" (arrest charge), I'd rather see the date,
location (or other fields) listed only one.

Lee Ann

[quoted text clipped - 67 lines]

.

 

[Jeff Boyce]

I agree, Hide Duplicates is a shotgun, Grouping/Sorting gives you finer
control...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Lee Ann:

The drawback of using the HideDuplicates property is that if two incidents
on
successive dates occur at the same location then, while the new date value
will be displayed, the location value will still be suppressed, which I
doubt
you'd want.

A better option is to do as Bruce says and group the report first by date
then by location, giving each group a group header, Include the date and
location controls in their respective headers. In the Format event
procedure
of each group header section put the following line of code;

MoveLayout = False

This will cause the group header controls to print on the same lines as
the
first detail, giving you the desired layout, but showing the date and
location on the first line of each distinct date regardless of whether the
location differs or not.

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault

Threats

Harassment
10/14/2007 Bar/Nightclub None

However, you might need to differentiate between two incidents on one
date,
in which case you'll need to bring the incident table's primary key into
play
and group the report first on the date, then on the primary key
(IncidentID
say), and then on the location. In this case give only the IncidentID and
Location group's a group header and put the date control in the former.
The
same code is used in the Format event procedures of the header sections as
before. You'd then get results like this:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault

Threats

Harassment
05/05/2007 Bar/Nightclub GBH
10/14/2007 Highway None

Where the assault/threats/harassment on 05/05/2007 constitute one incident
and the GBH a separate incident on 05/05/2007.

Ken Sheridan
Stafford, England

Jeff,

I've looked at the grouping/sorting and hide duplicates that you've
suggested. I don't know that either of these is what I'm looking for
(unless
I'm not doing something correctly). As an quick example of what I now
see:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault
05/05/2007 Bar/Nightclub Threats
05/05/2007 Bar/Nightclub Harassment
10/14/2007 Highway None

and the way I'd like to see it listed on a report:

Date Location Arrest Charge
05/05/2007 Bar/Nightclub Assault

Threats

Harassment
10/14/2007 Highway None


If there are multiple "entries" (arrest charge), I'd rather see the date,
location (or other fields) listed only one.

Lee Ann

[quoted text clipped - 67 lines]

.

 

[KenSheridan via AccessMonster.com]

Date as a label's caption is fine; its only as an object name that it might
cause problems.

Have you tried combining grouping and setting the MoveLayout property to
False in code as described in my last post? That should work with the
simplified layout you posted, but if you need something more complex you
might be better off breaking it down into a parent report for the incidents
and subreports for the multiple values per incident. That would give you the
most flexibility as you can position the subreports anywhere you like in
relation to each detail row of the parent report.

Ken Sheridan
Stafford, England

The hide duplicates may work on a small scale, however it hides too much if I
have other records with the same information. The sorting/grouping also
doesn't seem to give me the format I'm looking for.

The use of the word Date was mentioned - this is a caption on the report,
but the field name is actually DateofIncident. Does the same rule apply in
this case?

As always, thanks for all the advice you guys give!

In the Report Sorting and Grouping dialog (View >> Sorting and Grouping),
group by Date, then Location. Create a group header for each. Place a text

[quoted text clipped - 34 lines]