how to display table columns...

[yehong]

I need to create a report that selects data from tableA and tableB and then displays only those fields that have different values. The tableA and tableB are copies of each other and tableA alows updates and tableB does not. So when we run a report, and say there are 5 rows in each table and column5 of tableA has different value than column5 of tableB, then the report should only list column5. How should I go about it? I have some ideas by doing VBA coding and opening two recordsets and then comparing rs1.field1=rs2.field1 for each column, but not sure if that will be the best way to do it.

 

[dhookom]

You could create two union queries
SELECT ID, ColumnA as TheVal, "A" As TheColumn
FROM tblA
UNION ALL
SELECT ID, ColumnB, "B"
FROM tblA
UNION ALL
SELECT ID, ColumnC, "C"
FROM tblA...;

Then, you can do the same for tblB. Then create a select query from both that joins ID and TheColumn fields and displays where the TheVal columns/fields are not the same.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[yehong]

Thanks Duane. I understood the first part that says to create union all queries for each column for both tables.
Can you explain the second part please?

Then create a select query from both that joins ID and TheColumn fields and displays where the TheVal columns/fields are not the same.

thanks

 

[dhookom]

Assuming two union queries: quniA and quniB. You need to find out where TheVal differs based on the ID and TheColumn. Your sql might look something like this:

SELECT A.*, B.*
FROM quniA A JOIN quniB B on A.ID = B.ID AND A.TheCOlumn = B.TheColumn
WHERE A.TheValue <> B.TheValue

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]