By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,813 Members | 2,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,813 IT Pros & Developers. It's quick & easy.

SQL Multi Column Primary Key Problems

P: n/a
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, ProjectsAttencedByContact,
ContactDetails
Defined as below.

Projects ProjectsAttendedByContact 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 ProjectsAttendedByContact
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.ProjectID AS Projects_ProjectID,
Projects.ProjectName, Projects.ThisProject
FROM Projects
WHERE (((5) Not In (select RefrenceNumber from
ProjectsAttendedByContact)) AND ((Projects.ProjectID)<>Exists (select
ProjectID from ProjectsAttendedByContact)) AND
((Projects.ThisProject)=Yes));

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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<me**********@yahoo.co.uk> wrote in message
news:11********************@g49g2000cwa.googlegrou ps.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, ProjectsAttencedByContact,
ContactDetails
Defined as below.

Projects ProjectsAttendedByContact 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 ProjectsAttendedByContact
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.ProjectID AS Projects_ProjectID,
Projects.ProjectName, Projects.ThisProject
FROM Projects
WHERE (((5) Not In (select RefrenceNumber from
ProjectsAttendedByContact)) AND ((Projects.ProjectID)<>Exists (select
ProjectID from ProjectsAttendedByContact)) AND
((Projects.ThisProject)=Yes));

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


The table ProjectsAttendedByContact (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
Nov 13 '05 #2

P: n/a
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, commentsResponses 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 ProjectAttendedByContact table - which
is shared across all projects)

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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.