• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 127
  • Last Modified:

Access 2010 - Data analysis assistance

I am looking for some advice or links to relevant articles/tutorials on the following concept.

Basically I'd have a spreadsheet from Excel that has information on accounts/records. Think Clients.

After importing that to Access, the next day, there would be changes to the aforementioned Excel workbook. Items like, whether an account has been "opened" or "closed" or put "on hold", etc.

So, I'd import that into Access, and my goal here would be to try and compare the previous import to the next day's import, as to be able to generate a report, or give some indication, of what all has taken place, such as which clients were closed or put on hold. Perhaps what costs have increased for clients (i.e. yesterday there would be a $50 balance, the next day there'd be a $20 balance).

So things like that. Is Access suitable? What could be done to at least put me on the right path?

I've checked with an Excel expert, but they advised there's just too much data for vlookups to work with, and it's somewhat tedious.

I'm new to Access
3 Solutions
Jeffrey CoachmanMIS LiasonCommented:
In a real-world scenario, this would be best if it all was done from Access.
In Access you can use a technique like what is outlined here:
...to create an "audit trail" to see what changes were made o the records.

Sending updates daily to Access from Excel everyday, although possible, ...is just too cumbersome.

Dale FyeCommented:
or you could configure your Access data to track particular aspects of the data by date.  For example, In Access, you might have a ClientStatus table which contains fields like: ClientStatusID, ClientID, Status (or possibly StatusID if you want to normalize it), Eff_Date, and Thru_Date.  When I do this, I create a query (qry_Client_Status_Current) which would look something like:

SELECT ClientStatusID, ClientID, StatusID, Eff_Date, Thru_Date
FROM tbl_ClientStatus
WHERE ([Thru_Date] IS NULL)
OR ([Eff_Date] <= Date() AND [Thru_Date] <= Date())

I would then use this query to compare the Status with the new upload and if the status has changed for a particular client, I would update the [Thru_Date] and add a new record to the table with the new status and [Eff_Date].

You would do this for each of the fields (or potentially groups of fields) that you would want to track.  Although I would not normally recommend a table of balances, you might want a ClientPayments table to record payments, from which you could compute balances.
Although I agree with Jeff, that all updating should be done in Access, you can work with what you have assuming that there is a unique identifier for each row in the spreadsheet.  Typically spreadsheets don't bother with such niceties that we get used to in relational databases so you may have to add one.

Once you can uniquely identify each row, you can link to the spreadsheet from Access (the workbook MUST be closed) and Access will then treat it like a table.

The matching is done as three parts.
1.  To find data changes:
Create a query that joins the Access table with the linked sheet on the unique identifier using an Inner Join.  Add a calculated flag column for each column you want to compare.

Select tbl1.ClientName as PrevClientName, sheet1.ClientName as CurrClientName, IIf(tbl1.ClientName = sheet1.ClientName, false, true) as ClientNameDiff, tbl1.Addr1 as PrevAddr1, sheet1.Addr1 as CurrAddr1, IIf(tbl1.Addr1 = sheet1.Addr1, false, true) as Addr1Diff, ......

Then you can make a report that uses a query that selects any row where any one of the "Diff" fields is true.

2. To find the adds, you need to use a right Join.
Select sheet1.*
From sheet1 Right Join tbl1 On sheet1.UniqueID = tbl1.UniqueID
Where tbl1.UniqueID Is Null;

Use the whole query to report on "adds"

3. To find the deletes, you need to use a left join.
Select tbl1.*
From tbl1 Left Join sheet1 On tbl1.UniqueID = sheet1.UniqueID
Where sjeet1.UniqueID Is Null;

Use the whold query to report on "deletes"
garryshapeAuthor Commented:
Awesome thanks for the guidance, should work
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now