SQL Multi Column Primary Key Problems

[mel_palmeruk]

Hi

I am having trouble with ms-access and trying to determind whether or
not a row already exisitng in a particular table.

I have 3 tables namly Projects, ProjectsAttence dByContact,
ContactDetails
Defined as below.

Projects ProjectsAttende dByContact ContactDetails
------------ -------------------------- ------------------
*ProjectID *ProjectID *RefrenceNumber
ProjectName *RefrenceNumber Name
ThisProjectYes/No AttendedYes/No Surname
etc. etc.

What I am trying to do is allow a contact to attend many projects.

I have a form which has the Refrence number on it. So when I move to
the next record on the form I'd like to check if the combination of
ProjectID and RefrenceNumber exists in the ProjectsAttende dByContact
table - and if not to insert this combination into the table.

(I should have noted that the field onthe Projects table called
ThisProjectYes/No denotes which onthe list of projects we are currently
dealing with)

The SQL that I have been working on looks like this::
SELECT 5 AS Expr1, Projects.Projec tID AS Projects_Projec tID,
Projects.Projec tName, Projects.ThisPr oject
FROM Projects
WHERE (((5) Not In (select RefrenceNumber from
ProjectsAttende dByContact)) AND ((Projects.Proj ectID)<>Exists (select
ProjectID from ProjectsAttende dByContact)) AND
((Projects.This Project)=Yes));

WHERE 5 would be the RefrenceNumber on the form
ANY HELP WITH THIS WOULD BE TRUELY APPRICIATED!!
Thanks in advance.
Melissa

[Justin Hoffman]

<me**********@y ahoo.co.uk> wrote in message
news:11******** ************@g4 9g2000cwa.googl egroups.com...

Hi

I am having trouble with ms-access and trying to determind whether or
not a row already exisitng in a particular table.

I have 3 tables namly Projects, ProjectsAttence dByContact,
ContactDetails
Defined as below.

Projects ProjectsAttende dByContact ContactDetails
------------ -------------------------- ------------------
*ProjectID *ProjectID *RefrenceNumber
ProjectName *RefrenceNumber Name
ThisProjectYes/No AttendedYes/No Surname
etc. etc.

What I am trying to do is allow a contact to attend many projects.

I have a form which has the Refrence number on it. So when I move to
the next record on the form I'd like to check if the combination of
ProjectID and RefrenceNumber exists in the ProjectsAttende dByContact
table - and if not to insert this combination into the table.

(I should have noted that the field onthe Projects table called
ThisProjectYes/No denotes which onthe list of projects we are currently
dealing with)

The SQL that I have been working on looks like this::
SELECT 5 AS Expr1, Projects.Projec tID AS Projects_Projec tID,
Projects.Projec tName, Projects.ThisPr oject
FROM Projects
WHERE (((5) Not In (select RefrenceNumber from
ProjectsAttende dByContact)) AND ((Projects.Proj ectID)<>Exists (select
ProjectID from ProjectsAttende dByContact)) AND
((Projects.This Project)=Yes));

WHERE 5 would be the RefrenceNumber on the form
ANY HELP WITH THIS WOULD BE TRUELY APPRICIATED!!
Thanks in advance.
Melissa

The table ProjectsAttende dByContact (sometimes called a junction table)
should indicate which contact attended which project. Often a structure
like this would not need a AttendedYes/No field since you can often assume
that if there is no matching record for the project/contact combination this
is the same as having a 'no' in the field. The only time where the yes/no
field would be important is if you wanted to show three options:
Contact attended project (record in junction table marked yes)
Contact did not attend project (record in junction table marked no)
Not sure whether contact attended project (no record in junction table)
Is this really a requirement or, as the yes/no field indicates, do you just
need two possibilities?

Additional comments:
Don't give your field names reserved words like 'Name'
Don't mis-spell 'reference' as 'refrence' - it will cause you (or future
developers) frustration

If you need further help, post back letting us know what the purpose of the
forms are, e.g. this is a form bound to the contacts table so I can look up
a contact, see what projects he attended ... etc

[mel_palmeruk]

Hi Justin

Thank you very much for your help and comments.
I am basically using these tables in the exact manor you have asked
about.

I am setting up a DB where only the ContactDetails table has common
data between Projects. For each project then relating data such as
eventsAttended, commentsRespons es etc. is compeletly diffrent. So what
i have done is to create a base DB with common tables for DATA. Then
multiple other DB files which relate to each Project. [this have linked
tables bay to the common data in the DATA file]

The field onthe Projects table ThisProject - is a boolean field which
is set to yes only for the current project that a file represents.

Thus on the form I am talking about, I want to add a check box which
users are able to populate for the current project. (when the enter a
yes or no it will populate the ProjectAttended ByContact table - which
is shared across all projects)

Hope this gives more information into what I am trying to do.
Thanks
Melissa