Compare fields against each other in a table

[Dominick D.]

Good Day, Access Gurus,

I have a task at my job that requires creating a query that compares one
field to another field in a table and determine if, after the comparison, the
value in both fields match. If they do not match, then the output of the
query says, "No Match". Basically, in a table, I have 117 fields that
correspond to SAP data and 117 fields that correspond to DB2 data. So, I have
a table that has 117 fields that correspond to SAP data and the other 117
fields that correspond to DB2 data and I have to match an SAP field to the
DB2 field to see if there is a matching value in both fields, within this
table. Now, I don't know VB or VBScript, so does this query have to be coded
with VB, VBA, VBScript or can I do a select query, then design it to do this
task? I appreciate your help.

 

[John Spencer]

Do you have one (or more fields) in each table that can be used to join
specific records together?

That is can you match a record in the DB2 table to a record in the SAP table?
And you cannot do this by position (first record to first record, since
that is not a valid concept in a relational database.

If you can do that, then you could write a query to do so.

Do you want "No Match" for each set of fields or do you want "No match" for
the record?



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[ANDY-N via AccessMonster.com]

I'm assuming that both tables have something in common, a unique ID of some
sort that you can join them together. If they do, in your query, call up
these two tables and create a join relationship, then in the join properties
select option 2. This will give you all matching and non matching records.
Non matching records will be not have anything in it. Then make another query
using the first query, create a new field call whatever you want and use this
code:

New field: iif([GROUP] Is Null,'NO MATCH',[GROUP])

Hope this helps.

 

[Dominick D.]

Hi, John and thanks for your assistance:

Here is a look at the table, with partial fields:

SAP-DB2 Table

ZLKUNNR 'SAP field'
CON_TYPE_REC 'SAP field'
CON_LEGACY_NAME2 'SAP field'

and here are the DB2 fields:

CUST_ACN 'DB2 field'
CUST_REC_TYPE_CD 'DB2 field'
CUST_NAM 'DB2 field'

So, each SAP field corresponds with each DB2 field in this table. So, the
data in the field ZLKUNNR must match the data in the field CUST_ACN. For
example, ZLKUNNR = "10101" CUST_ACN = "10101" and so on. So, if there is no
match of the SAP field to the DB2 field, the query should say, "No Match".
So, in summary, I need a query that compares each SAP field to the
corresponding DB2 field in this table. If the sap data does not match the db2
data in the same record in this table, the query should say, "No Match". Can
I do this using a simple query or do I have to write code? I don't know VB,
VBA, VBScript. If you can, can you provide me an example? Thanks.

 

[John Spencer]

I'm sorry, but that does not tell me how you know that a specific record in
SAP matches up to a specific record in DB2. Without that ability, I cannot
envision any way to do what you want. You would be matching the first record
in SAP to every record in DB2 - so it is highly likely that you would have no
match for almost every record in DB2 table. Then you go to the second record
in SAP and match it against every record in the DB2 table.

So, can you say which one, two, or three (up to ten) fields will let you match
up the records between the two tables. If not, I can see no way to reliably
do what you are asking to do.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County