Show only fields that DONT match in two recordsets

Basically I have two tables. One table called RC (tblReportCard) and another one called RCC (tblReportCardChanges). I have a VBA function that inserts the RC records in RCC whenever the user makes any changes of many fields. For instance if the user makes a change to CarrierCallCount I insert the entire recordset into RCC. That all works fine. Now what I want to do is to be able to get only the fields that do not match. the inner joins are on CarrierCode, ReportMonth, ReportYear. both tables have the exact same field names. So if only one of four fields are different I only want to see the one that is different. I did a query with a bunch of ORs but then it shows ALL of the fields. I want to exclude the fields that match in both tables in my query.
LVL 1
JArndt42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JArndt42Author Commented:
Also I would like to see if this can be done in VBA working with two recordsets. so I would like to compare records in recordsets and return only those that DON'T match.
0
HainKurtSr. System AnalystCommented:
try:

select rc.id,
iif(rc.col1=rcc.col1, null, rc.col1) old_col1,iif(rc.col1=rcc.col1, null, rcc.col1) new_col1,
iif(rc.col2=rcc.col2, null, rc.col2) old_col2,iif(rc.col2=rcc.col2, null, rcc.col2) new_col2,
iif(rc.col3=rcc.col3, null, rc.col3) old_col3,iif(rc.col3=rcc.col3, null, rcc.col3) new_col3,
iif(rc.col4=rcc.col4, null, rc.col4) old_col4,iif(rc.col4=rcc.col4, null, rcc.col4) new_col4
from rc inner join rcc on rc.id=rcc.id
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JArndt42Author Commented:
Hain,
          I will try that and it will probably work but the only issue I have with all of the IIF statements is that if and when the fields change then I need to rewrite it. That is why I would like to do it via VBA using recordsets. That way I can loop through the fields and return the recordset. I will try this though.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

HainKurtSr. System AnalystCommented:
but the issue will be each record will have different number of columns
say result is 3 records...

rec1: id, col2, col4
rec2: id, col1
rec3: id, col3

at the end you need to show all columns :)

id col1 col2 col3 col4

+ you want old & new values

id col1_old col1_new col2_old col2_new col3_old col3_new col4_old col4_new
1                    B        BB                         D        DD
2  A        AA              
3                                      C        CC

Open in new window

0
HainKurtSr. System AnalystCommented:
or are you changing only one column at a time?

based on the sample above, what result set are you looking for?
0
JArndt42Author Commented:
It may be possible to change all of the columns. Right now there are a total of four columns that can be changed. Of course at least one of the four has changed or there wouldn't be an entry in RCC.
0
HainKurtSr. System AnalystCommented:
maybe you can do this

select rc.id, 'Col 1' ChangedColumn, rc.col1 OldValue, rcc.col1 NewValue from rc inner join rcc on rc.id=rcc.id and rc.col1<>rcc.col1
union all
select rc.id, 'Col 2' ChangedColumn, rc.col2 OldValue, rcc.col2 NewValue from rc inner join rcc on rc.id=rcc.id and rc.col2<>rcc.col2
union all
...
union all
select rc.id, 'Col n' ChangedColumn, rc.coln OldValue, rcc.coln NewValue from rc inner join rcc on rc.id=rcc.id and rc.coln<>rcc.coln
order by rc.id, ChangedColumn

id ChangedColumn OldValue NewValue
1  Col 2         B        BB
1  Col 4         D        DD
2  Col 1         A        AA
3  Col 3         C        CC

Open in new window

0
JArndt42Author Commented:
I had to alias the iif statements and it worked like a charm. Thank you very much. I would still be interested in doing this with recordsets if anybody sees this that has an idea on how to loop through the two and do a compare.

Thank you very much
0
HainKurtSr. System AnalystCommented:
it will be difficult or very slow compared to query solution...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.