Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 2010 - Data analysis assistance

Posted on 2015-02-11
4
Medium Priority
?
123 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 668 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 668 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 39

Assisted Solution

by:PatHartman
PatHartman earned 664 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

604 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