Multiple record source on a form.

[injanib via AccessMonster.com]

Hi,
I hope someone can give me some advise on this.

I have three tables, Customer, Contracts and Invoice.
1. The customer table has names and contact info of my customers.
2. The Contracts table has the information such as contract number, POP,
Award Date, POC, etc...
3. The Invoice Table contains billing information on the contracts. invoice
number, bill to, paid or unpaid, overdue, billing details, etc.

First what I need to do is to use my Contracts form to create record for a
new contract when awarded. I can make the form to pull the customer info from
the Customers table, but I would like it to relate this particular contract
to tghe customer. My Customers table and Contracts table both have a
CustomerID field as a primary key.
To do this, how should I create the relationship between the table? Should I
use the Form/Subform method or can I create a query using both tables and
make the the query as the record source for my form?

Second, I need to do the same thing involving all three tables when I create
an invoice for a contract.
When creating the invoice, I would like to select the contract number from a
drop down list on my Invoice Form and have it auto populate the related
customer information on the form.

Once again, do I have to use the form/subform method involving all three
record sources or a query method works?

Points to be noted are that one Customer may have more than one contract and
one contract may have more than one invoice.

I hope that I explained it clearly. Thanks in advance.

 

[Sninkle]

First each table should have it's own PK.
Example:
CustomerID for Customers
ContractID for Contracts
Invoice ID for Invoices

Then if you want to connect the Contracts to the Customer you add a field to
the Contract table called CustomerID and set its datatype to number. Then go
into relationships and join the CustomerID from the Customers table to the
CustomerID in the Contracts table. This will join the tables together.

Now for the Contracts form, you mentioned displaying the Customer info.
What info needs to be displayed just the name or ??

For Invoice does each invoice consist of 1 contract or multiple contracts?
Is there ever an invoice without a contract?

Please note that the above information is just on how to connect your tables
together. I need more information in order to assist you with the rest of it.

Carrie

 

[injanib via AccessMonster.com]

Thank you so much for your response.

On the contracts form all the customer information needs to be displayed
including name, company, phone number, email address and physical address.

An invoice will never have multiple contracts, however the other way around
is true, meaning that a contract may have multiple invoice.

No invoice will ever be without a contract number. No contract, no invoice.

Thanks again for your help.

First each table should have it's own PK.
Example:
CustomerID for Customers
ContractID for Contracts
Invoice ID for Invoices

Then if you want to connect the Contracts to the Customer you add a field to
the Contract table called CustomerID and set its datatype to number. Then go
into relationships and join the CustomerID from the Customers table to the
CustomerID in the Contracts table. This will join the tables together.

Now for the Contracts form, you mentioned displaying the Customer info.
What info needs to be displayed just the name or ??

For Invoice does each invoice consist of 1 contract or multiple contracts?
Is there ever an invoice without a contract?

Please note that the above information is just on how to connect your tables
together. I need more information in order to assist you with the rest of it.

Carrie

Hi,
I hope someone can give me some advise on this.

[quoted text clipped - 28 lines]

I hope that I explained it clearly. Thanks in advance.