Solved

Show only fields that DONT match in two recordsets

Posted on 2012-03-16
9
499 Views
Last Modified: 2012-03-16
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.
0
Comment
Question by:JArndt42
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 1

Author Comment

by:JArndt42
ID: 37729872
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
 
LVL 55

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 37730097
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
 
LVL 1

Author Comment

by:JArndt42
ID: 37730116
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 37730140
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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 37730151
or are you changing only one column at a time?

based on the sample above, what result set are you looking for?
0
 
LVL 1

Author Comment

by:JArndt42
ID: 37730185
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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 37730293
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
 
LVL 1

Author Closing Comment

by:JArndt42
ID: 37730307
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
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 37730352
it will be difficult or very slow compared to query solution...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question