Comparing tables

[CB100]

Hello, I have to pull reports from SAP for product returns on a weekly basis.
I then import this table into Access. I have to compare the returns to an
original table of customers, which I call our direct customers, to customer
returns that contain direct and indirect customers. I want to compare the
table of all returns to the original direct customer list and make 2 tables.
One for direct customers and 1 for indirect customers. I would compare the
customer numbers from Original to the table with all returns. If someone
could give me a few pointers for doing this I would appreciate it.

Thank you

 

[Chip]

If I understand what you want you will need two make Table queries. The first
one will be a straightforward query joining the customer number in each
table. to get the matches. The second will be an unmatched query. Access has
a wizard for this. Just use the wizard, then change the query to a make table
and rerun it.

 

[Klatuu]

I would advise against make table queries. They are inefficient and a big
contributor to database bloat

Unless there is some overriding reason to have two tables, that would not be
the correct design. The correct design would be to have one table with a
field that identifies whether the record is for a direct or indirect customer.

I don't know the layout of your tables, so I can't be absolutely precise,
but my suggestion would be to link to the external table and do your import
via an append query. In the append query, you can populate a field to
determine direct/indirect, based on joining to the existion customer table
and marking those records direct where a matching customer id is found.

 

[CB100]

Thank you both for helping me on this project
I am not sure how to Populate a field to determine Direct/Indirect. I have
the original table for sales (Direct Customers) linked. Do I append the
weekly returns report to the original sales? Where and what do I add to the
append query to determine Direct/Indirect.

Thank you again for your help

 

[Klatuu]

I can't give you an exact answer because I don't know enough about your table
or the data you are importing, but the basic idea would be to determine
whether the customer is direct or not and populate the field accordingly. If
you can give some detail about your table and the data your import and how
you know whether a customer is direct or not, I will see if I can help.

 

[CB100]

The information for the tables is all customer information for product that
is sold, such as company name, address, customer number and all the sales of
product (direct customer). These customers are generally distribution
centers, therefore they send the product to other customers who can then
return the product, if need, to the our manufacturer. These are the indirect
customers. I have to separate these out from the original customers that we
sold to. The differences in the tables is only what was sold and what was
returned, otherwise the same type of information is in both tables.
I hope this explains it ok.
Thank you

 

[John W. Vinson]

The information for the tables is all customer information for product that
is sold, such as company name, address, customer number and all the sales of
product (direct customer). These customers are generally distribution
centers, therefore they send the product to other customers who can then
return the product, if need, to the our manufacturer. These are the indirect
customers. I have to separate these out from the original customers that we
sold to. The differences in the tables is only what was sold and what was
returned, otherwise the same type of information is in both tables.
I hope this explains it ok.
Thank you

You may want to condsider just using one table, Customers, with a yes/no field
Direct.