Query based on start/stop dates

[EJHansen05]

I am building a database to track number of personnel supporting a
project. The personnel come from different companies and are
supporting the project for different lengths of time. For example:
company A: 10 people between 1 Sep 06 and 5 Sep 06
company A: 14 people between 6 Sep 06 and 15 Sep 06
company B: 24 people between 4 Sep 06 and 19 Sep 06
company B: 21 people between 20 Sep 06 and 25 Sep 06
company C: 11 people between 9 Sep 06 and 16 Sep 06

The database has fields that track start and stop dates with number of
personnel during that period for each company supporting the project.

I need to be able to calculate how many people are on the project each
day so I can generate a chart either in MS Access or MS Excel. Using
the example above I would need to calculate from 1 Sep 06 through 25
Sep 06 for each day (I'm not concerned about weekends or holidays).
With this example I would have 21 people on 10 Sep 06.

I'm stumped as to how I go about calculating how many people are
present each day. Any help would be greatly appreciated.

 

[Jeff Boyce]

You described the information in a general sense, but I didn't catch a
specific list of data elements/tables.

How you query depends on what you query...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mayhem05]

You described the information in a general sense, but I didn't catch a
specific list of data elements/tables.

How you query depends on what you query...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have the following tables: TblCompanies, TblSupport, and
TblSptPersonnel
TblCompanies has some basic information on each company (Name,
Location, etc.).
TblSupport has SptLocation (Text), SptType (Text), foreign key to
TblCompanies, and some miscellaneous data.
TblSptPersonnel has foreign key to TblSupport, StartDate (Date/Time),
EndDate (Date/Time), NumPersonnel (Number). This allows me to change
the number of personnel supporting the project while retaining
historical data. When the number of personnel changes I close out the
old number with an end date and then add in the new number with a start
date.

I need to be able to display totals by days for the number of personnel
supporting the project. For example if the user wanted to chart how
many personnel were there between 1 Sep 06 and 7 Sep 06 I should be
able to generate a query or report that shows how many were physically
there for each day of that week. I can get it to work for a single
day, but not over a period of time.

I hope this helps to clarify my situation.

 

[Jeff Boyce]

A couple of thoughts...

If your tblSptPersonnel held the number of persons on a tblSupport
"incident", but only one day per record, you'd have a simple way to add all
the "numbers" for any given day. By using a date range (two fields), you're
seeing the issue you're trying to solve.

If you exported the data to Excel and used one column per day, and copied a
tblSupport's "incident" number of personnel across the date range, you could
sum the overlapping (actually, all) incidents' staffing.

A possible "brute force" method, as a concept only...

Create a new procedure. In that procedure, get one row from tblSptPersonnel
at a time.

Create a loop that cycles, one day at a time, from the StartDate to the
EndDate, accumulating the number of personnel into an array (or into a table
like I described at the top).

Get the next row from tblSptPersonnel and repeat.

Repeat.

Your array (or table) should hold the total number of personnel by day, from
the minimum startdate to the maximum enddate.

Just one person's opinion...

Jeff Boyce
Microsoft Office/Access MVP

 

[Mayhem05]

I am not very proficient with writing code, but I am willing to learn
and have done coding years ago (like 20). That being said, do you have
any pointers on how I can code a procedure to run through each record
based on user supplied start and end dates or do this by creating an
array? What Jeff described I believe will solve my problem, I'm just
not sure how to go about doing the code.

Thanks for all the help.
Eric

 

[Jeff Boyce]

Eric

My choice would be to write the records to a new table, and do away with the
date-range table. Even if that's the way your data comes to you, you could
still dump it into a "temp" table and use the procedure to append to a more
permanent table.

You could create a procedure (a subroutine) in a code module. You'll want
to spend some time looking into opening a recordset and getting the value(s)
from a row, then opening a second recordset and writing values to it.
You'll also need to look into how to loop through a recordset.

These keywords should help you find HELP -- first open a code module, then
try HELP...

Regards

Jeff Boyce
Microsoft Office/Access MVP