Help me with my Relationships!

[CW]

No, I'm not looking for a shrink or an agony aunt...although I may well need
one soon if this carries on...
I have been struggling with how to construct the table relationships in
order to support the creation of invoices, which I do in three stages. I am
aware that nobody can help unless I describe my app clearly enough and I'll
try to summarise it as follows:

tblInquiries holds numerous client details including Name and Ref. These are
maintained via frmMain.
tblInvoices includes InvNo, InvDate, DebtorName, Ref. I have a one to many
relationship from tblInquiries.Ref to tblInvoices.Ref (because several
invoices may be required on an order)
tblDebtors includes DebtorName, DebtorAccountNo, InvNo. I have a one to
many relationship from tblInvoices.InvNo to tblDebtors.InvNo (but have a
sneaky feeling this may be wrong!)
tblInvoiceLines contains InvNo and a number of fields carrying the service
descriptions and the charges etc. I have a one to many relationship from
tblInvoices.InvNo to tblInvoiceLines.InvNo (because of course each invoice
can contain several lines)
From my Main form I have a button that opens frmInvoiceHeader (record source
tblInvoices) and this allocates the invoice number, and has controls for the
date, and the order Ref (pushed from frmMain).
From there I fire another button and open frmInvoiceDebtor (record source
tblDebtors) on which I have a combo to select the required Debtor (with
address etc). The InvNo is pushed from frmInvoiceHeader, creating the link
between the two.
Then I fire another button to open frmInvoiceLines, (record source
tblInvoiceLines) again pushing the InvNo through to complete the link between
all three forms/tables.
This mostly appears to work OK but there are two problems:

1: When I open frmInvoiceDebtor the newly created InvNo has been pushed
through OK. However, if I then use the combo and select a Debtor that has
already had an invoice created against them, the pre-filled InvNo is erased
and has to be re-entered manually.

2. When I run my qryInvoices which captures everything from all 3 linked
tables/forms, sorted by InvNo, it shows certain invoices twice, firstly with
the "original" debtor and then with the newly selected one.

I'm sure this is caused by an error in my relationships and have tried
various other structures but have now got myself in a loop and cannot fathom
out where I'm going wrong.

If I should be using different fields for the relationships and/or they
should be of different join types, please let me know.

I'm sorry this is so long-winded but hope that someone can understand the
situation and point me in the right direction!
Thanks a lot
CW

 

[Roger Carlson]

Try writing all your relationships out in actual word *in both directions*,
like this:

Each Inquiry can spawn One or More Invoices
Each Invoice can be for One and Only One Inquiry

Each Invoice can have One or More InvoiceLines
Each InvoiceLine can be for One and Only One Invoice

So far so good. But now what about Debtors? First of all, you have to
determine what entity the Debtor has a relationship with. Invoices? Maybe.
Or should it be Inquiry? In other words, do the Invoice have a Debtor or is
it really the Inquiry that has a debtor? If all invoices must go to the
same Debtor, then the relationship would actually be Inquiry. If different
Invoices can have different Debtors, then it would be Invoices.

Let's assume it's Invoices like you say. Now write your relationship.

Each Invoice can have One or More Debtors
Each Debtor can have One and Only One Invoice

Now this hardly seems likely. First of all, it's not likely that a single
invoice will have multiple debtors. Granted, I don't know your business,
but usually an invoice goes to a single paying entity. Secondly, it's not
likely that each Debtor will only have one invoice in your entire database.

What it looks like to me is that you've got your relationship backward.
Debtor is on the "one-side" of the relationship. So you would put the
primary key of Debtor (DebtorAccountNo) in the Invoice table as a foreign
key, leaving:

Each Debtor can owe on One or More Invoices
Each Invoice can be assigned to One and Only One Debtor.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[CW]

Roger -
I really appreciate your response - you have unravelled the situation and
laid it out so clearly. It has really helped a great deal - I couldn't get my
head round it but I am sure you have hit the nail on the head and I shall now
try amending the relationships as you suggest.
Many many thanks
CW

 

[Roger Carlson]

I'm glad it helped. Whenever you get stuck in a design, look for
Many-to-Many relationships. Often this is the problem. Why am I saying
this here? Because there is in fact a M:M relationship between Inquiry and
Debtor.

Each Inquiry can have One or More Debtors
Each Debtor can owe on One or More Inquiries

In this case, however, you don't have to create a linking table to resolve
the M:M. In this case, the *Invoice* table IS the linking table.
Recreating the 1:M relationship between Invoice and Debtor resolved the M:M.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L