query to compare data in 2 tables and choose the correct one.

[Guest]

I have 2 tables that are linked to 2 different Excel worksheets(both tables
have the exact column names). I am needing to create a query that will
compare Table1-Record123 to Table2-Record123. If Table2-Record123 contains a
number(it will be a 6 digit number) in ColumnAAA, I need it to show that data
in the result. Otherwise, it needs to show the data in Table1-Recodr123 that
is in ColumnAAA.

Is there an easy way to do this?

Thanks.
Misty

 

[Guest]

The two tables must have a column of data that matches each other to do the
comparison - does such acolumn exist?

 

[Guest]

Try these --

SELECT [Table1-Record123].Item_ID,
IIf([Table2-Record123].[ColumnAAA]>100000,[Table2-Record123].[ColumnAAA],[Table1-Record123].[ColumnAAA]) AS [Ouput Column]
FROM [Table1-Record123] INNER JOIN [Table2-Record123] ON
[Table1-Record123].Item_ID = [Table2-Record123].Item_ID;

SELECT [Table1-Record123].Item_ID,
IIf(Len([Table2-Record123].[ColumnAAA])=6,[Table2-Record123].[ColumnAAA],[Table1-Record123].[ColumnAAA]) AS [Ouput Column]
FROM [Table1-Record123] INNER JOIN [Table2-Record123] ON
[Table1-Record123].Item_ID = [Table2-Record123].Item_ID;

 

[Guest]

The first one did not work( I think a formatting issue), however the second
one did. I am trying to learn how to write these codes. I assume the "len"
function refers to the length and "=6" referred to how many characters?

Thanks alot. You saved me!!!

Misty

Try these --

SELECT [Table1-Record123].Item_ID,
IIf([Table2-Record123].[ColumnAAA]>100000,[Table2-Record123].[ColumnAAA],[Table1-Record123].[ColumnAAA]) AS [Ouput Column]
FROM [Table1-Record123] INNER JOIN [Table2-Record123] ON
[Table1-Record123].Item_ID = [Table2-Record123].Item_ID;

SELECT [Table1-Record123].Item_ID,
IIf(Len([Table2-Record123].[ColumnAAA])=6,[Table2-Record123].[ColumnAAA],[Table1-Record123].[ColumnAAA]) AS [Ouput Column]
FROM [Table1-Record123] INNER JOIN [Table2-Record123] ON
[Table1-Record123].Item_ID = [Table2-Record123].Item_ID;

Yes, they both have an Item_ID column, which match.