Solved

Access 2010 - Data analysis assistance

Posted on 2015-02-11
4
114 Views
Last Modified: 2015-02-16
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
0
Comment
Question by:garryshape
4 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
ID: 40604208
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:
http://allenbrowne.com/appaudit.html
...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.

JeffCoachman
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 167 total points
ID: 40604266
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.
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 166 total points
ID: 40604296
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"
0
 

Author Closing Comment

by:garryshape
ID: 40612265
Awesome thanks for the guidance, should work
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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