Relationship question

[Guest]

Identified 4 entities:
Company (primary key: CompanyID)
Factory (primary key: ILS)
Audit (primary key: AuditNumber)
Invoice (primary key: InvoiceID)

One factory can supply many companies and a company can have many factories
supply them (i.e. many to many)
One factory can receive many audits.
Each audit can receive many invoice numbers.
The invoice number is associated (allocated) with one company (One company
can receive many invoices).

Getting the invoice number to include the audit is my problem.
I can't seem to get around this without affecting an index key.

 

[Van T. Dinh]

AFAICS from your description:

Company - Factory: Many-to-Many (Link / Resolver Table needed)
Company - Invoice: One-to-Many
Audit - Invoice: One-to-Many

AuditNumber should be included as the ForeignKey in tblInvoice. CompanyID
should also be another ForeignKey in tblInvoice.

 

[Ron2005]

Not all keys can be automatically loaded when detail records are
created, even if it is a child record. You will probably have to load
some of the foreign keys yourself in the beforeinsert event. The
directly preceding parent key will be loaded if you are in a parent
child form but you will have to do the others yourself.

Ron

 

[Roger Carlson]

Which, when you think about it, makes:

Company - Audit: Many-to-Many

with Invoice being a natural Linking table. So the complete picture would
be:

Company --< Company/Factory >-- Factory
|
^
Invoice
V
|
Audit

However, I don't see the problem with getting the invoice number to include
the audit. Can you elaborate?

--
--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

 

[Guest]

Thanks for the feedback.
This is what I have:
Company (CompanyID as key)
Invoice (InvoiceID as key, CompanyID and AuditNumber)
Audit (AuditNumber as key, ILS)
Factory (ILS as key)
Contract (CompanyID and ILS as keys)

I want to create a form such that the Company is assigned an invoice and
reflects the manufacturer. I also need the invoice linked to the audit.
I can create forms so that a factory can be assigned to a company and a
factory audit (adds new factories and subform for adding audit details). I
don't know how to create a form for my query above.
If it makes a difference only the ILS key is text format, the others are
autonumbers. (Apart from the link table).

 

[Roger Carlson]

Well, if I'm reading this correctly, you want a form with 2 subforms.

The main form will be based on the Company table (or more likely an ordered
query of the Company table.

Subform1 will have a Join of Contract and Factory as it's recordsource
Subform2 will have a Join of Invoice and Audit as it's recordsource

You will link the form and subforms on (Link Parent/Child properties of the
subforms) on CompanyID

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ImplementingM2MRelationship.mdb" which illustrates how to do
this.

--
--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