Problems with a table relationships and conditional forms

[boeram]

My problem stems from having two tables, one contains information about a given cancer e.g. LesionID (autonumber), the diagnosis, excision complete/incomplete, excision margin (mm) and a field called Type. Type groups the cancer into one of three categories - primary, re-excision or metastasis. My other table contains the Metastasis information e.g. Region(where it was found), what side (Right/Left), etc. The problem I have is I only need to fill out information on the metastasis table sometimes, (as only some cancers metastasise) and so what I need is a kind of conditional relationship. I’ve set the LesionID as the primary key on the main Lesion table but I'mnot sure how to link it to the metastasis table. Do I use LesionID?

I’ve run into problems when trying to create a form for easy data entry. I need a form that when you select metastasis as the type it will open the metastasis form for you to enter data into, but note down at the same time its given LesionID.

Currently I’m completely stuck so if anyone could help me out it’d be much appreciated.

 

[Remou]

Yes, LesionID should suit. When you are creating queries, make sure you include all records from the main table.

[tt]SELECT tblCancer.F1, tblMetastasis.F1
FROM tblCancer
LEFT JOIN tblMetastasis
ON tblCancer.LesionID = tblMetastasis.LesionID[/tt]

 

[Crowley16]

as for the table setup, it should be a simple 1-many link between LesionID table and the Metastasis table, with LesionID as a foreign key in Metastasis. (although you could normalise it further by using a link table which will cut down on nulls...)

as for the form, you should have 2 seperate forms, and on the Lesion form, have a button to automatically goto Metastasis, and with this button, you can create a new Metastasis entry, with the current LesionID, and bound the form to the new Metastasis entry.

--------------------
Procrastinate Now!