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.

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

"Henry C" <(E-Mail Removed)> wrote in message
news:086001c39c96$5bd3cae0$(E-Mail Removed)...
> 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.

On Mon, 27 Oct 2003 06:26:49 -0800, "Henry C"
<(E-Mail Removed)> wrote:

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

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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.
>-----Original Message-----
>On Mon, 27 Oct 2003 06:26:49 -0800, "Henry C"
><(E-Mail Removed)> wrote:
>
>>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?
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.
>

On Mon, 3 Nov 2003 09:16:31 -0800, "Henry"
<(E-Mail Removed)> wrote:

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

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public