Check for Duplicate entry over two fields

K

Kiwibirdsong

I hope I can explain this clearly. I am new to this group and hope yo
can help me.

First things first. I am creating a database that records training o
members. This has been on going for about 3 yrs.

Most of the database is up and running well however I have somethin
that needs help.

-The problem is this.- I have two tables. One holding information o
courses held and the other holding information of those who attended.
What I want to do is try and prevent someone from being entered int
the same course twice without some sort of confirmation that this i
what is wanted, thus having a confirmation message "Yes/No" that thi
duplicate entry is correct.

If a duplicate is required then the entry is accepted if it is no
required then the entry is cleared or deleted.

-Now to explain my skills.- I have had lots of help with this databas
and having been my first I have learnt lots. However in saying this
know I have gaps in my knowledge, thebiggest one being in my knowledg
of VB and SQL. I get there but with many hours to trying. Please i
you can help me could you keep it simple.

Many thanks KIW
 
D

Douglas J. Steele

If all you have is 2 tables, you won't be able to achieve what you want.
You're describing a many-to-many scenario, which requires 3 tables: one for
the Courses, one for the Attendees, and one that represents a list of which
people attended which course.

The third table would need to have at least the following three fields: the
Id from the Courses table, the Id from the Attendees table and a date field.
(The combination of these three fields would constitute the primary key of
the table)

To check whether or not a person has already attended a particular course,
you can query this third table using only the Course Id and Attendee Id
(Either the DCount or DLookup functions will be useful in this regard) Do
this lookup in the form's BeforeInsert event, so that you can cancel the
insertion if they don't want to attend a subsequent time.
 
K

Kiwibirdsong

Thanks Doug. When I said I only have two tables. I was misleading yo
a bit. To give you a bit more info I'll try and make it clearer. Eac
Course then has a Module which then may have a Unit (Each has it's on
table linking back to the main one, Course) . Now There are tables
one for Course Registration which is linked to another table for th
Attendees. The Course Date is in the registration table while th
attendees details are in the Attendees table, The attendees table als
holds the information for each attendee showing if they completed o
withdraw from the course including the date. My understanding of you
message is that I need 3 tables, as you can see I do have more than
tables. (Course, Module, Unit, Registration, Attendees) Does this mak
this doable?? Please advise if you need more clarification. I greatl
appreciate your reply and assistance.

KIW
 
D

Douglas J. Steele

Sorry, but you're not really giving enough details for me to comment.

What are the fields in each of the tables? Which field(s) constitute the
primary key of each of the tables?

Take a look at
http://www.databaseanswers.org/data_models/student_assignments/index.htm It
represents something like what you're trying to do (although it goes even
further, since it models assignments for each course as well. Ignore the
bottom 4 tables).
 
Top