A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Prevent duplication of entries from different tables?



 
 
Thread Tools Display Modes
  #1  
Old August 12th, 2009, 01:02 AM posted to microsoft.public.access.gettingstarted
Lostguy
external usenet poster
 
Posts: 73
Default 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  
Old August 12th, 2009, 03:56 AM posted to microsoft.public.access.gettingstarted
Arvin Meyer [MVP][_2_]
external usenet poster
 
Posts: 2,310
Default 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  
Old August 12th, 2009, 03:45 PM posted to microsoft.public.access.gettingstarted
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old August 27th, 2009, 01:55 AM posted to microsoft.public.access.gettingstarted
Lostguy
external usenet poster
 
Posts: 73
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.