Can't set up query to compare and sort data and calculate and show differences between two sets of

[Southpaw]

All,

Sorry if you've heard this one before....


Not a newbie to Access, but I might as well be. Have not had to utilize it in about 10 years.


I have been handed the unenviable task of setting up and maintaining a database for tracking personnel changes. Turns out it is several thousand positions spread across several divisions. (Serves me right for opening my mouth). I had originally started the tracking in Excel, but the sheer number of positions, the corresponding data and the formulas needed to track changes keep causing Excel to stall and crash.




I have turned to Access since it seems to be more robust and I can import the data from Excel.


So far, I have set up the tables for data that does not change much week over week (i.e. Position ID #, Position Description, Division, Position Level, Supervisor, Location… etc). Those are not all the categories, but you get the idea.


Where I am coming up short is in the query area. I can run basic queries. Where I am running into a brick wall is queries using calculations.
For example, we get a weekly report that shows the headcount by position number and description. This amount varies week over week. I have configured that past several reports into individual tables in my database. I am trying to create a query that would do the following:
1) 1) Compare the data in the tables for two different weeks.

2) 2) Sort out the position headcounts that show a difference.

3) 3) Show the positions that have headcount differences for each of the two weeks being examined. For example, Headcount as of 3/26, Headcount as of 3/19, Headcount Difference.

4) 4) Calculate the difference between the two week-to-week headcounts.


I have used the Query Wizard for the Find Unmatched Query. This only does part of what I am looking for The Find Unmatched Query shows me the Positions that do not have a headcount match. It doesn’t show me the Headcount as of 3/26, Headcount as of 3/19.
Any of you experts know of a way I can do this?


Thanks – Southpaw.

[June7]

I have configured that past several reports into individual tables in my database.

I do not understand this statement. Should not be multiple tables for the same kind of data.

Post example of raw data and desired output.