Combining separate data for same company

[Henry C]

I have a table that tracks all companies that buy from
us. It holds sales, totals etc, and each company has its
own customer number. We have one company that has two
customer numbers and they both contain sales and various
info. How do I combine the customer numbers and data for
this one company so it will have one number? Is this a
simple query or is it deeper than that? Thanks in advance.

 

[Kelvin]

Instead of linking the customer number, use the company name, that way even
it a company has 3 different numbers, the report will group them all into 1.

Another option is, if you can change the number, is to use customer numbers
that are similar and just add an extensions, XYZ100-A and XYZ100-B. Then in
your query you can group by just the part that is similar
Left([CustomerNumber],6).

A third option is to add another field like CompanyID which would be the
same for the 2 records.

There are many other options. You just have to pick something that will
identify the 2 records as being related.

Kelvin Lu

 

[John Vinson]

I have a table that tracks all companies that buy from
us. It holds sales, totals etc, and each company has its
own customer number. We have one company that has two
customer numbers and they both contain sales and various
info. How do I combine the customer numbers and data for
this one company so it will have one number? Is this a
simple query or is it deeper than that? Thanks in advance.

Without knowing how the data relates, it's hard to say for sure; but
an Update query should work. Do you want just one record, or two
records? I can see that a "totals" field could be updated by just
adding the two records' values, but what if other fields have
different data - how would you decide which to keep?

 

[Henry]

Thanks for the response.

So, an update query should do the trick? In the query do
I add all the fields of the table? For the fields that do
not necessarily need to be changed do I enter anything in
the criteria or leave it blank? I mainly need to get all
sales/dollars into one of the customer numbers. After
that I plan updating any address changes etc and then
deleting the customer number and info in the table. Am I
on the right track or am I making this more difficult than
it is?

Thanks again.

 

[John Vinson]

Thanks for the response.

So, an update query should do the trick? In the query do
I add all the fields of the table? For the fields that do
not necessarily need to be changed do I enter anything in
the criteria or leave it blank? I mainly need to get all
sales/dollars into one of the customer numbers. After
that I plan updating any address changes etc and then
deleting the customer number and info in the table. Am I
on the right track or am I making this more difficult than
it is?

You're making it MUCH more difficult than it needs to be.

Stop, step back, and do some reading about normalized table design.
You should have one table for each "Entity" - a real-life person,
thing, or event. You'll have a Table of Customers, with name, address
information, etc.; you'll have a table of Sales, with a CustomerID (a
link to the customers table), SaleDate, amount; doubtless other tables
as well. YOu don't put "sales/dollars into one of the customer
numbers" - if you want to display the total sales for a customer, you
would calculate the total on the fly in a Totals Query and base a form
or report on that query. There's no need to store the total, and
there's no need to delete the information when you're done with it!