Solved

Access 2010 - Data analysis assistance

Posted on 2015-02-11
4
112 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 34

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Loop within Select Case 3 27
Why wont the Phone List report sort by Last Name? 2 15
Add macros on Open 9 33
SQL Query 3 0
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now