Working With Monthly Data Updates

[Mcosta]

I receive monthly project tracking reports and I'm looking to compare a few things between these monthly updates. I should add I'm new to Access and have been using Excel and dual monitors to manually compare data sets!!! My life might be getting a LOT easier.

Here's what I'm tracking:

• I get monthly excel reports that have unique values from a "Project ID"
• each "Project ID" stays the same every month, but the project status changes from "Installed", "Commitment", "Pre-Commitment", etc
• Sometimes new projects are added
• Sometimes old projects disappear from the report
• A few other columns have numerical data that changes every month as well "Project Cost", "$ Incentive", etc.

Here's what I'm trying to automate:

• Find out what Project IDs are new
• Find out if old Project IDs are missing from the new report
• Somehow "flag" or identify projects that have status changes
• Somehow "flag" or identify which numerical values increased or decreased i.e. "Project Cost", "$ Incentive", etc

Some basic help would be appreciated. Do I create two tables? Do I append the new monthly report to the old one when I import my data, then somehow work with the new information?

Many thanks!

[alansidman]

You will need to have two tables and create queries that are joined on the common unique identifier. You can create Left and Right queries that will identify everything in one table and only what matches in the other. In this manner, you can see what has been added or dropped. Using the Query Wizard for unmatched items will also help. In the query, you can create a new field that compares the status fields and indicates if they have changed or remain the same. Same with the costs.

Now a bit of advise. Get an Access guide and start reading. Access has a steep learning curve and is not as intuitive as Excel. Start here

This reading is very important and if not followed will cause hours of mental discomfort as you move forward.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

Good luck. Post back once you have started and have hit a stumbling point.

Alan