Multiple query criteria, results to new table

[D Zandveld]

Hi, i'm not quite a beginner, but having 'mental block' with where to start
constructing a query.

I have two tables of data: SAP data & Vendor data. Both have the same
fields, but with different data. The Vendor data contains records that may
have changed, and the idea is to compare the two tables. In actual terms,
there is a record for each combination of Vendor Number, Material Number &
Plant.

They contain data similar to the following:
Vendor Number Material Number Plant Price Terms
123456 987654 555 100 NN99
123456 987654 533 95 NN99
123456 666666 555 87 NN99
111111 987654 555 110 NN30

What I want to do is compare the Vendor Data with the SAP Data.
If the Material Number, Vendor Number and Plant combination are the same,
but the Price or Terms are different, put that record in a new table called
Compared.

Any answer appreciated, but specifics (click on this, go to here, click on
that) would REALLY get me going.

Thanks

 

[John W. Vinson]

Hi, i'm not quite a beginner, but having 'mental block' with where to start
constructing a query.

I have two tables of data: SAP data & Vendor data. Both have the same
fields, but with different data. The Vendor data contains records that may
have changed, and the idea is to compare the two tables. In actual terms,
there is a record for each combination of Vendor Number, Material Number &
Plant.

They contain data similar to the following:
Vendor Number Material Number Plant Price Terms
123456 987654 555 100 NN99
123456 987654 533 95 NN99
123456 666666 555 87 NN99
111111 987654 555 110 NN30

What I want to do is compare the Vendor Data with the SAP Data.
If the Material Number, Vendor Number and Plant combination are the same,
but the Price or Terms are different, put that record in a new table called
Compared.

Any answer appreciated, but specifics (click on this, go to here, click on
that) would REALLY get me going.

Thanks

First question: WHY do you want to a) store the same data in two tables SAP
and Vendor, rather than having a single table? and b) Given that you are
already storing data in two redundnant tables, why do you want to store it in
*Three* redundant tables? As a rule, data should be stored once and once only!

To find the "compared" data, create a Query joining SAP and VENDOR, joining by
Material Number, Vendor Number, and Plant by dragging those fields from SAP to
VENDOR. Put a criterion on the first Critera line under SAP.PRICE of

<> [Vendor].[Price]

and on the next line down under SAP.Terms put

<> [Vendor].[Terms]

This query will display all of the records which match on the three fields but
which differ in either of the other two fields.

If you insist on violating the basic relational principles, you can turn this
query into a MakeTable query.