#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] Daily Data Reconciliation

## CRW

I have data which will be in the form of a spreadsheet and it will need compared with the new data the next day also in a spreadsheet and I will need to see all elements of the data which has changed. This will include:
1. New line items in the spreadsheet
2. Data in each line item that has changed

How would I begin so I have a better idea of where to start investigating how to set this up?

----------


## alansidman

Is this an Access or Excel issue.  You have posted to the Access forum.

----------


## CRW

> Is this an Access or Excel issue.  You have posted to the Access forum.



Yes. this is an Access question. I want to bring the spreadsheets into Access and do the comparison,

----------


## alansidman

What is the structure of the Spreadsheet (field names and data type) and what information are you looking for that may have changed.  Some examples please.

----------


## CRW

> What is the structure of the Spreadsheet (field names and data type) and what information are you looking for that may have changed.  Some examples please.



There are numbers and dates and text. The values in my datasets do not have value to me, meaning I don't care what the values are I only care that I see the differences between the 2 datasets, i.e. what does not match any longer between dataset A and dataset B. 
I am able to do an import to get the datasets into Access with no problems. What I am looking for I think, is to
identify all the records that have changed from Day 1 to Day 2. Any value in any place that has changed. Certainly I will have data 
changes once identified which hold more importance and less importance but I will make the determination after I see the changes 
between the 2 sets. The changes I expect to see each day are rows added to my dataset i.e. in excel I will have more rows of data eachday which I want Access to identify any changes occurring between Dataset A (Day 1) and Dataset B (Day 2). 
On Day 3, Dataset A will no longer be needed and dataset B will be the dataset I am comparing against.

Primarily, and I may have to provide a live example once I have a better idea of what I'm doing. The bottom line to me is idenifying any and all changes between the datasets. I forsee it identifying rows added and values removed from a defined column at minimum 

I am hoping for an easy way to identify changes between the 2 sets, like an unmatched but I'm sure it probably will not be that simple

Thanks for your time.

----------


## alansidman

I don't have a sure fire known solution for you.  What I do have is a suggestion which is untested.  I would attempt to join the two tables on all fields and then run an unmatched query from the left table and then from the right table.  This would give you all the changes.  The issue if there is one may be the number of joins that may make this fail.  How many fields are in your table and which ones are important for the changes.  Try this and then post back with issues.

----------


## CRW

> I don't have a sure fire known solution for you.  What I do have is a suggestion which is untested.  I would attempt to join the two tables on all fields and then run an unmatched query from the left table and then from the right table.  This would give you all the changes.  The issue if there is one may be the number of joins that may make this fail.  How many fields are in your table and which ones are important for the changes.  Try this and then post back with issues.



Unfortunately, after doing some research I attempted that very action last night and failed. I do not have much working knowledge of Access and got stuck at the joins I think...I know how to bring in the tables and I assume I bring in all the fields, first my Day1 then my Day2. When I got to the join part I chose option 2 and joined them at each matching field.  This gave me one directional arrow. A picture might help me. I am not sure how to join them with 2 arrows, is that what you mean. Bottom line was I tried using IsNull as the criteria for Table 1 and running the query and returned a mismatch error, the query would not run. I'm not sure it would have worked but I read it online to use that criteria. It might help me to understand what I am doing when I am join the tables, what is the purpose? Mostly my question at this point is how can I join on both sides if this is what you are saying I should do and do I need a primary key?

Sorry for the remedial questions but I have taken 3 Access courses and still just don't seem to get it.

I plan to play around with the aforementioned today.

----------


## alansidman

You will need to do this twice.  You will have to do a left join and run an unmatch query.  Then you will have to do a right join and do an unmatch query.  Without the joins, Access will not know what to do with the tables and you will get a cartesian result.

How many fields (columns)vdo you have in each table that you are attempting to compare?

----------


## CRW

I have approx 7 columns but only 2 columns have changing data that I care about. The problem I keep having is I do not have a unique identifier and am not sure if I have to have one

----------


## CRW

So today I was able to use an unmatched query which I set up with the query wizard but I think it only created the Relationship on one field but it still worked.  I was able to retrieve new records that were added with no problem. Now, what I need to be able to do is 
identify and retrieve those records that have changed. Primarily, I have the following scenario:

1) 1 column has totals which may completely disappear OR will change to another figure
2) 1 column has invoice numbers which may change

Again, I am needing to compare the data with the day before data and identify the change. I do not seem to have a unique identifier 
which everyone tells me is the hangup.

Any thoughts?

----------


## alansidman

Why don't you upload a sample of the data that you are trying to work with and I will see what I can come up with.

Alan

----------


## CRW

> Why don't you upload a sample of the data that you are trying to work with and I will see what I can come up with.
> 
> Alan



Everyday I compare new (today's) data with yesterday's data which means everyday I upload the new data and compare it with my previous days data.

Sample Day 1.xlsx

Sample Day 2.xlsx

----------


## CRW

My best guess of a combination of a unique identifier would be Amount1, Amount2, Number.  These 3 combined might never be the same. I looked over the data and this seems to be the case.

----------


## alansidman

Is the reference field a unique value?  Will one reference always represent the same record? You refer to a field "number."  Which field is that?  Reference?

----------


## alansidman

Look at the data base I've attached.  I created four queries. Two that run off the other two to show what has changed.

----------


## CRW

Thank you. I will try and take a closer look tomorrow. I am not necessarily seeing the data I need when I run the queries, which is the entire string of all columns and the values which have changed. I am not very technical and have very limited knowledge at this time. What do the long strings of numbers represent?

----------


## alansidman

i did a concatenation to create unique values.  Then I compared the concatenations to determine which unique value was in one table but not in the other.  If you need to see more fields, then just add them to the queries.  I only did what you asked which was to identify which records changed.

----------


## CRW

> i did a concatenation to create unique values.  Then I compared the concatenations to determine which unique value was in one table but not in the other.  If you need to see more fields, then just add them to the queries.  I only did what you asked which was to identify which records changed.



Great. Thank you. I will take a closer look today and play around with it a little and let you know what questions I have. Concat was a good idea. So there are 4 queries, what do they represent?

----------


## alansidman

Two queries do the concatenation and then the remainder do the comparison.  A right join to see what has changed and a left join to see what has been added that was not there previously.

----------


## CRW

> Look at the data base I've attached.  I created four queries. Two that run off the other two to show what has changed.



I am so happy! You are a genius! It worked, concatenating the field gave me my unique value and then, with some help, I was able to write the query.

Thank you so much.

----------

