Select from one of two fields to utilise in a one to many relationship

[DannyBoy]

Hi folks,



So, I am slowly getting my DB in order (I think!), but now I’m migrating data I realised I have another issue.

As things stand, I have my relationships defined as per the attached diagram, which if all of my issued documents matched I would have no issue with. We do however have to issue client documents which do not (and will not) have our references against them.
I thought of adding a separate field which would populate with an IF THEN type statement, but I’m not sure a) how to do it(!) and b) if this is the best way to deal with it.

See attached image for an overview of the current table values and relationships



Any advice please?

Thanks!

[June7]

No, that is not a good idea.

Why do you have ID fields set as primary key if you are not saving as foreign key? Linking should be on Primary/Foreign key fields. Primary and Foreign key fields must be same type (autonumber/number, text/text).

Use the ID field from tblDocuments as the foreign key saved in tblJoinXmtDoc.

[DannyBoy]

Hi June,

Thanks for your reply (again!).

I’ll have to take a step back from the DB to explain the process. My group are in a position where we either generate our own documents (some are transmitted and some are not), or transmit documents on behalf of our client. Some of our documents are issued to the client, at which point they have both numbers.
Preference would always be to issue documents on a transmittal using our own number and only in instances where that isn’t available would we utilise the client document ID.
Prior to set up of this DB, we tracked the numbers in a single column in Excel, which resulted in having to filter and manually manipulate the data when reporting was required – something which we would rather avoid having to do again, hence having distinct fields.

To be able to issue a document, the user has to populate the document details (if not already available in the system), then create a transmittal by using a form. I have the form created with the relevant fields from the transmittal table, what I am struggling with is how we can manage the numbering issue and then how to add the document population details to the transmittal form.

Does this all make sense? Or am I as clear as mud?

[June7]

Doesn't change my previous comments.