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. |
Thread Tools | |
Display Modes | |
|
|