Access Newbie Adding fields

[Grant]

I have three tables. Here's a brief example of what's in them.
Table1 has 100 PartNumber records, plus cost. PartNumber,Cost
Table2 has Customer2 sales by PartNumber, 45 records. PartNumer,Sales
Table3 has Customer3 sales by PartNumber, 75 records. PartNumer,Sales

What I have done is created a relationship from Table1 PartNumber to
Customer2PartNumber & to Customer3PartNumber.

I then created a query to return
Table1_PartNumber,Customer2Sales,Customer3Sales. Lastly I used the
"Expression Builder" and added Customer2Sales+Customer3Sales. I wanted a
column that shows combined sales for Customer2 and Customer3. This works
only if the same PartNumber recored appears in both Customer tables. I
wanted to see a combined sales for both Customers. If Customer2 has sales of
a PartNumber and the Customer3 has no record in the table how can I still
get how can get a combined total.

Appreciate and help.

 

[Jeff Boyce]

Grant

First, step back and reconsider your table structure. With your design,
you'll need to keep adding tables and keep changing queries and keep
modifying forms and reports and keep maintaining your macros and code
modules every time you add a new customer. Do you really want this much
maintenance?!

A relational database (vs. what sounds like a copy of a spreadsheet) would
use ONE table to keep track of sales, and include a field that held the
CustomerID. In Access-speak, this is considered a "foreign key" field,
pointing back to a Customer table that lists all customers and provides an
ID for each.

I have NOT answered your "how do I" question, because it is based on a data
design that will cause both you and Access considerable work and headaches.
Reconsider your table design before proceeding...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP