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
|
|||
|
|||
Prevent duplication of entries from different tables?
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 |
#2
|
|||
|
|||
Prevent duplication of entries from different tables?
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) -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Lostguy" wrote in message ... 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 |
#3
|
|||
|
|||
Prevent duplication of entries from different tables?
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Lostguy" wrote: 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 |
#4
|
|||
|
|||
Prevent duplication of entries from different tables?
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 |
Thread Tools | |
Display Modes | |
|
|