Solved

Access 2010 - Data analysis assistance

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

861 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