+ Reply to Thread
Results 1 to 20 of 20

Daily Data Reconciliation

  1. #1
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Daily Data Reconciliation

    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?

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

    Is this an Access or Excel issue. You have posted to the Access forum.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    Quote Originally Posted by alansidman View Post
    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,

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

    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.

  5. #5
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    Quote Originally Posted by alansidman View Post
    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.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

    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.

  7. #7
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    Quote Originally Posted by alansidman View Post
    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.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

    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?

  9. #9
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    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

  10. #10
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    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?

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

    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

  12. #12
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    Quote Originally Posted by alansidman View Post
    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

  13. #13
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    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.

  14. #14
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

    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?
    Last edited by alansidman; 09-13-2012 at 07:23 PM.

  15. #15
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

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

  16. #16
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    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?

  17. #17
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

    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.
    Last edited by alansidman; 09-13-2012 at 10:53 PM.

  18. #18
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    Quote Originally Posted by alansidman View Post
    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?

  19. #19
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Daily Data Reconciliation

    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.

  20. #20
    Registered User
    Join Date
    07-27-2012
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Daily Data Reconciliation

    Quote Originally Posted by alansidman View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1