S
Shanin
I believe I may need to set my structure up different, but maybe not. We
wanted to build a database that is basically just data entry of a written
event form that is filled out. There are several 1 to many relationships, so
I broke these all out in tables. The main table, tblEventReport has the
primary key as an autonumber and is EventID. All the other tables are linked
to this table by the EventID, but also have their own primary key which is
another autonumber, for instance the table tblIncidentType, has IncidentID as
it's primary key and has EventID as it's link to the tblEventReport.
To the query part. If I run a query where I want to pull one Event, I'll
get numerous results of the same stuff since each related table may have
multiple entries relating to that one event. Hers is the SQL:
SELECT DISTINCT [LastName] & ", " & [FirstName] AS Individual,
tblEventReport.*, tblPersonsInvolved.*, tblEventIncident.*,
tblInjuredBodyParts.*, tblInjuryDescription.*, [tblPerson/AgenciesNotified].*
FROM tblIndividuals INNER JOIN (((((tblEventReport LEFT JOIN
tblEventIncident ON tblEventReport.EventID = tblEventIncident.EventID) LEFT
JOIN tblInjuredBodyParts ON tblEventReport.EventID =
tblInjuredBodyParts.EventID) LEFT JOIN tblInjuryDescription ON
tblEventReport.EventID = tblInjuryDescription.EventID) LEFT JOIN
[tblPerson/AgenciesNotified] ON tblEventReport.EventID =
[tblPerson/AgenciesNotified].EventID) LEFT JOIN tblPersonsInvolved ON
tblEventReport.EventID = tblPersonsInvolved.EventID) ON tblIndividuals.[DMH#]
= tblEventReport.[DMHStateID#]
WHERE (((tblEventReport.[DMHStateID#]) Like
[Forms]![frmLookupEvent]![IndividualLookup] & "*") AND
((tblEventReport.EventDate) Like [Forms]![frmLookupEvent]![DateLookup] & "*"))
ORDER BY [LastName] & ", " & [FirstName]
WITH OWNERACCESS OPTION;
For the one entry I entered as a test, my query pulled back 48 entries. I
only want the information from the tblEventReport to be listed once, but need
all the data from the other tables that is related to that event report to be
included.
I'm sure this is simple, but I can't seem to get it to work right now, maybe
because it's about quiting time.
wanted to build a database that is basically just data entry of a written
event form that is filled out. There are several 1 to many relationships, so
I broke these all out in tables. The main table, tblEventReport has the
primary key as an autonumber and is EventID. All the other tables are linked
to this table by the EventID, but also have their own primary key which is
another autonumber, for instance the table tblIncidentType, has IncidentID as
it's primary key and has EventID as it's link to the tblEventReport.
To the query part. If I run a query where I want to pull one Event, I'll
get numerous results of the same stuff since each related table may have
multiple entries relating to that one event. Hers is the SQL:
SELECT DISTINCT [LastName] & ", " & [FirstName] AS Individual,
tblEventReport.*, tblPersonsInvolved.*, tblEventIncident.*,
tblInjuredBodyParts.*, tblInjuryDescription.*, [tblPerson/AgenciesNotified].*
FROM tblIndividuals INNER JOIN (((((tblEventReport LEFT JOIN
tblEventIncident ON tblEventReport.EventID = tblEventIncident.EventID) LEFT
JOIN tblInjuredBodyParts ON tblEventReport.EventID =
tblInjuredBodyParts.EventID) LEFT JOIN tblInjuryDescription ON
tblEventReport.EventID = tblInjuryDescription.EventID) LEFT JOIN
[tblPerson/AgenciesNotified] ON tblEventReport.EventID =
[tblPerson/AgenciesNotified].EventID) LEFT JOIN tblPersonsInvolved ON
tblEventReport.EventID = tblPersonsInvolved.EventID) ON tblIndividuals.[DMH#]
= tblEventReport.[DMHStateID#]
WHERE (((tblEventReport.[DMHStateID#]) Like
[Forms]![frmLookupEvent]![IndividualLookup] & "*") AND
((tblEventReport.EventDate) Like [Forms]![frmLookupEvent]![DateLookup] & "*"))
ORDER BY [LastName] & ", " & [FirstName]
WITH OWNERACCESS OPTION;
For the one entry I entered as a test, my query pulled back 48 entries. I
only want the information from the tblEventReport to be listed once, but need
all the data from the other tables that is related to that event report to be
included.
I'm sure this is simple, but I can't seem to get it to work right now, maybe
because it's about quiting time.