Key matching field problem

[millrat]

Hi, I need some help with relationship design. I have 3 tables tblSheet, tblList and tblPermit.

tblSheet
SheetID (autoNo no dups)
SheetNo (text PK)

tblList
ListID (autoNo PK)
SheetNo (long)

tblPermit
PermitID (autoNo PK)
SheetNo (long)

The join is 1-Many on SheetID and SheetNo, these first 2 tables are used in a parent-child form-subform.
My problems arise when I want to store the tblSheet.SheetID in tblPermit.SheetNo, as not all records require a SheetNo I want to be able to leave some records null, of course Access wont let me do this, it tells me it can't find tblSheet with key matching fields SheetNo.
Does anybody have any suggestions how to get around this problem? All help appreciated. Thanks
millrat

 

[BNPMike]

Your model is wrong. SheetNo cannot be a key if you have nulls in it. The key is maybe SheetId. If you change the other tables to SheetId instead of SheetNo, you'll get rid of the problem.

However you talk of a 1:M relationship. I don't understand the semantics of your model but that would imply you actually want another table. The confusing thing is tblSheet.SheetID - why is it an autonumber?

Perhaps you could explain what Sheets, Lists and Permits are and how they relate to each other.

 

[LittleSmudge]

The confusing thing for me is the use of the SAME field names in different tables to hold different data types.

Your table naming and PrimeKey naming convention is good.
But I can't see where your FKs are.

If the Long data fields are the Foreign keys in the 2nd and 3rd table that link to the PK in 1st table then can I recomment that you rename them SheetRef in each case.

This will make it clear that they are the fields that Refer to the PK in tblSheet


You'll also be wanting to make SheetId in tblSheet the PrimeKey for that table.
Text with some records containing Null cannot be the PK.


'ope-that-'elps.

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[millrat]

BNPMike, thanks for the reply.
I'll give you an overview of my project. tblSheet is an Isolation Sheet that records isolation procedures for a particular piece of process plant( eg compressor), tblList is a list of individual isolations for each piece of equipment on the process plant (ie compressor) and will includes things like valve open/shut, electrical isolation etc. Therefore for each record in tblSheet there will be 1 or more records in tblList. I also have a parent-child/form-subform using these 2 tables.
tblPermit is a "Permit to Work". Some jobs require an Isolation sheet and some do not. So in tblPermit I would like to store the SheetID for those Permits that have an Isolation sheet and null for those that do not. I realise that with my current design this is not possible, I was hoping that someone would be able to make some suggestions as to a better design. As to why tblSheetID is autoNo I thought it would make more sense to store a number in tblPermit than a text string (as this could be quite long,even though it is unique)
I hope this makes it clearer.
cheers,
millrat

 

[lespaul]

Check out the "Fundamentals of Relational Database Design" in the developers section of TT member JeremyNYC at

http://www.alphabetcitydataworks.com

leslie