If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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. |
#2
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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. "Lee Ann" wrote in message ... 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. |
#3
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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. "Jeff Boyce" wrote: 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. "Lee Ann" wrote in message ... 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. . |
#4
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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. "Lee Ann" wrote in message ... 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. "Jeff Boyce" wrote: 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. "Lee Ann" wrote in message ... 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. . |
#5
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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. Lee Ann wrote: 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] . -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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 Lee Ann wrote: 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] . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201004/1 |
#7
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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! "BruceM via AccessMonster.com" wrote: 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. Lee Ann wrote: 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] . -- Message posted via http://www.accessmonster.com . |
#8
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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. "KenSheridan via AccessMonster.com" u51882@uwe wrote in message news:a61f8ca748409@uwe... 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 Lee Ann wrote: 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] . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201004/1 |
#9
|
|||
|
|||
Query duplicates results due to many-to-many relationships
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 Lee Ann wrote: 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] . -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201004/1 |
Thread Tools | |
Display Modes | |
|
|