Relationship/Form Help???

[louisa]

Hi everyone and thanks for taking the time to read my question.
I am very new to access and have been designing my database for sometime now finding problems on the way, i feel i am so near to completion but have hit a dead end, hopefully someone can advise me.
My main database form consists of 5 tabs: General (all contact information, name, address etc), Lines (this will be phone numbers), Equipment (any equipment to be supplied), billing (all invoices issued) and Call log (any call history). I have 5 tables all relating to the relevant data.
Once I open the database it is set to open the General page, from there all the customers address info will be completed, then the second tab will be completed and so on. Every tab will need to be completed for that one customer (record), my problem is in the relationships i have a one to many to link the general to the lines but the equipment and invoicing on data entry saves the information entered on to every customer. I.E i will have a customer called John and enter an invoice number which will save but on Fred's record it has the same data. I am trying to figure out how i link these relationships together so they are unique to that customer I.D, any info will be greatly appreciated. Oh and sorry for the novel......

 

[odin1701]

Before you read too much of what I've written, do some searching on normalized databases.

You should have a unique Customer ID in the customers table. You should also have that as a primary key. But this ID should exist in each of your other data tables as well.

So for example, your customers table has this:

CustomerID	Name
12345		Jones
67890		Smith

For your invoicing table, you need both an InvoiceID AND a CustomerID field. For each entry, you need to identify the CustomerID and the InvoiceID. You can have multiple CustomerID's in the Invoice table, but not multiple InvoiceID's. So the InvoiceID is your primary key, no duplicates allowed. Your CustomerID field is a foreign key, duplicates allowed.

Your Invoice table should look like this:

InvoiceID	CustomerID	Amount		Date
00456		12345		123.68		2/2/2010
00457		67890		34.29		2/1/2010
00458		12345		74.89		2/3/2010

Same thing with your other data tables. They should all have the CustomerID so that you know who that record belongs to. Then it's easy to relate them in a query, and only bring up invoices for a particular customer.

You could then have another table InvoiceDetails, which has more info about a particular Invoice. This could just have the InvoiceID, and information about each item on the invoice. For example:

InvoiceID	ProductID	Price		Quantity
00456		123		100.00		1
00456		456		23.68		1

In this table, both of the fields InvoiceID and ProductID should be highlighted and added as Primary Keys. Because you shouldn't have both numbers repeating. One or the other can repeat, but not both. You wouldn't have an InvoiceID of 00456 and have two entries of ProductID 123 - it would simply have Quantity set at 2.

This is of course just one way to do it.

It just depends on what you want to do, and what information you want in each table.

 

[louisa]

Thanks for the reply, in each table i do have a primary key but the way i want it to work is on the general tab there is a combo box with all the company names listed so when someone selects a company their details are displayed in general, when i switch to lines there details are displayed but on equipment and billing any information i have already entered in there is the same to each customer not unique to the one i had selected. I need to no which relationship needs to be in place for that to work. Many thanks for your help so far.

 

[louisa]

This has now been resolved thank you for your help.