Prevent duplication of entries from different tables?

[Lostguy]

Hello!

tblEmployee with EmployeeIDpk and FName (holds employee data)
tblEventType with EventTypeIDpk and EventName (holds event data)
tblEmployeeEvent with EmployeeIDfk and EventWhenIDfk (holds which
employees were at which events)
tblEventWhen with EventWhenIDpk, EventTypeIDfk, EventDate,
EventLocation (holds when, where and what type of event happened)

I don't want the same employee to do the same thing twice on the same
day.

So I don't want two Smith-Bowling-19Jan2009 entries.

AFIAK, setting the Indexed property for these two fields will prevent
duplicates within the fields, but isn't where I need to go. And the
Multiple-Field Primary Key method only works within one table.

How do I prevent duplication of the same EmployeeID-EventTypeID combo
when those are in different tables?

VR/

Still Lost

 

[Arvin Meyer [MVP]]

If the tables are joined you can run a DLookup, or DCount on the query to
check for the presence of an existing combination. If they aren't joined, I
don't think there's any way to run a query without a Cartesian Product (the
multiplying of 1 set of records by the other)

 

[Jerry Whittle]

Open up the query in SQL view and put the word DISTINCT behind SELECT as in
SELECT DISTINCT ....

If that doesn't work, change the query to a Totals query. Group By the
fields that you don't want duplicated. For the rest use something like First,
Last, Min, or Max.

 

[Lostguy]

All,

Here's the final setup (I am leaving out some of the supporting lookup
tables):

40 employees (30 civilian, 10 military) divided among 4 sections.
Setup:
tblEmployee: EmployeeIDpk(1), LName
(Data for each employee)

tblEventType: EventTypeIDpk (1), EventName, EventPeriodicity,
ApplicableTo, CivMilBoth
(Data for each eventtype: Flu Shot, 365 (days), all (sections), Both
(Civilian, Military, or Both))

tblEVentWhen: EventWhenIDpk (join), EventTypeIDfk (many), EventDate,
EventRemarks
(When each event took place)

tblEmpEvent: EventWhenIDfk (join), EmployeeIDfk (many), EmpEventIDpk
(Which employees did which events)

All relationships are ref int with Join Type 1 (With the 1's and
many's shown above), except for the two "joins" shown above that I had
to uncheck ref integrity because I kept getting the error that I could
not delete a record because it had a related record....??

The frmEVentEntry (based on tblEventWhen) with fields EventDate,
EventTypeIDfk (combo), and EventRemarks
The continuous subform is from tblEmpEVent with EmployeeIDfk (combo)

Problem: I only need the last time an event was done, not the past
occurrences. So, for this, I need to prevent the combination EventType
(what) and EmployeeID (who) and EventWhen (when) from duplicating.
Since two of these are in one table and one is in another, I can't do
a multifield index. But I can do a query. Can you do indexes on a
query? To do the DLookup thing, I would need to change the
RecordSource of the forms to be off the query rather than the table?
(Sorry. I just don't know enough to know how to set this up. The user
should not be able to enter duplicate data in this combination, and if
they try, they should get a message that says "This combination of
event elements was entered on XX/XX/XXXX. Please update the older date
to the new.)

??

VR/Lost

 

[John Spencer]

Maybe I don't understand, but it seems to me that you need a multi-field
index on tblEmpEvent on EventWhenIDFK, EmployeeIDfk.

prevent the combination EventType
(what) and EmployeeID (who) and EventWhen (when) from duplicating

That stops an employee from being assigned to the same event on the same
date more than once

Perhaps what you want is to prevent duplicates for an employee and an
event type - that is an employee can only "attend" an event once.

You could change the tblEmpEvent to include the EventTypeID. Then you
would be able to build your desired index. Your subform would be a bit
more complicated but ...
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================