By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,323 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Comparing and Calculating differences between two periodic headcount reports.

P: 1
All,

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)Compare the data in the tables for two different weeks.

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

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)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.
Apr 5 '18 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,419
Hi Southpaw.

Welcome to Bytes.com.

Let me start by pointing you in the right direction to start with.

The first point is that this isn't a question. It's a project spec. You'll be lucky to find anyone prepared to jump in on such a thread. If you read those instructions that are posted so clearly everywhere you'll see this isn't allowed. That's not because we don't want to help. It's because that approach doesn't work well. You need to break down your situation into one (or probably more) discrete questions.

My next point is that you need to start by giving some serious thought to your database structure. Get that wrong and the difficulties just keep multiplying. Please check out this short normalisation article (Database Normalisation and Table Structures) before approaching that first stage. One hint would be that you wouldn't want to use separate tables for data of the same type that simply covers different date ranges.

I'm going to lock this thread down now (Feel free to send me a PM if you need to communicate before you're ready to post your first (next) question. The smaller in scope and more direct your questions are the better chance they have of getting good and clear answers quickly.

Best of luck.
Apr 6 '18 #2

This discussion thread is closed

Replies have been disabled for this discussion.