One to Zero or One relationship

[netloss]

Hi -

I'm designing a database to hold results from 5 different contruction
project surveys. A given project can have data for any or all of the 5
surveys.

Since the surveys are different, I placed each one in its own table.
Naturally I also need to associate the survey IDs with the Project IDs,
so I have a Link-Project-Survey table.

The problem comes when I try to enforce referential integrity between
these tables. I need One (the Link table) to Zero or One (survey
tables) relationships, but I don't know how to create one in Access, or
if it's even possible.

Thanks for any advice,
NL

 

[Jeff Boyce]

One table for each survey ... sounds like a ... spreadsheet!

Take a look at Duane Hookom's AtYourSurvey as a model for data structure.

See:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tim Ferguson]

(e-mail address removed) wrote in @t31g2000cwb.googlegroups.com:

The problem comes when I try to enforce referential integrity between
these tables. I need One (the Link table) to Zero or One (survey
tables) relationships, but I don't know how to create one in Access, or
if it's even possible.

Yes it's possible in Access -- in fact, it's the setting of the Required
property of the foreign key field that makes the difference between 1:1 or
1:{0 or 1} and you get a non-required field unless you ask for the
contrary.

I probably agree with Jeff, though: I don't think it's what you need.

All the best


Tim F