CODEWITHSUNDEEP
excelms-access
user4495697
user4495697

Reputation: 1

Access DB: Daily cumulative count and daily number on assignment

I have a table that tracks the dispatching of personnel. The table has the employee name and the date the person went out and the date they returned.

The table has hundreds of entries from 1988 to current. In Excel I track the cumulative count per day (of the year) of how many people have been sent out, and I also track the number of people out on any given day. The table lists the Month & Day in the first column (every day of the year, including leap days) and the years on the first row. There is data for every date (a zero is entered until the first person is sent out that year, then starts counting up as there are more dispatches, or in the case of the number of people out each day, it will show zero if no one is out that day or if there were, say 5 people out, it would show "5" for that day). I then use the data in Excel to construct a graph that shows the number of dispatches on the y axis and the day of the year on the x axis (along with the current year’s number, the average number and the max over the 27 year history). Currently I just track this manually (I just keep a running count of each and enter it in manually in Excel.) I would like to build a query of my Access data that would return the same information that I could import into my Excel spreadsheet. One query that would show the day & month in the first column and the years along the top row and for each day show a cumulative count for that year of how many people have been sent out. Another query that has the day & month in the first column and the years along the top and a count of how many people were out for that particular day for that particular year. There shouldn't be any gaps (every day has data, even if it is "0"). I would then import those queries into Excel to replace my manual tracking that I am doing now.

I know how to construct the Excel stuff (I have that running already), and how to import info from Access to Excel, what I need to know is how to construct these 2 Access queries.

Any help/ideas on how to construct those 2 queries would be greatly appreciated!

Upvotes: 0

Views: 148

Answers (1)

duffymo
duffymo

Reputation: 308743

I'd recommend that you migrate this app to a web based solution that uses a real database - SQL Server or MySQL, not Access.

"Desk drawer software" is what I call homegrown apps that someone creates for themselves to perform some small task that eventually become integral to running a business and grow out of hand. Your truck factor is 1: if anything happened to you, no one would know how to do this function. The software may not be backed up or checked into a source code management system. There's no QA. There's no way to migrate new features to production: if you alter the app, then that is what you have.

I'd recommend a web app to mitigate all the risks I've described:

  1. You have to deploy a web app to a server, which takes it off your desktop and puts it in a central place where anyone who's authorized can access it.
  2. Separates database from display issues.
  3. Makes you think about how to archive historical data. Partitioning by year makes sense.
  4. Likely you'll put this in a source code management system like Subversion or Git.

Upvotes: 1

Related Questions