Calculate totals for period of time

I'm hoping someone can guide me on solving this vexing problem I have
with a database I built to track a projects. I'm using MS Access 2003
and here are the basics: the database is designed to track companies
that are supporting projects and the number of personnel in each
company that supports each project. I'm stuck when I want to sum the
total number of personnel supporting the project over a period of time.
For example:
Company A provides 10 people between 1 Aug 06 and 10 Aug 06
Company A provides 15 people between 11 Aug 06 and 20 Aug 06
Company B provides 12 people between 5 Aug 06 and 12 Aug 06
Company C provides 22 people between 9 Aug 06 and 16 Aug 06
Company C provides 28 people between 17 Aug 06 and 28 Aug 06

Report would show each day between a queried start date and end date
(not just dates where data was input). Using the example above I would
have something like the following:
1 Aug 10
2 Aug 10
3 Aug 10
4 Aug 10
5 Aug 22
6 Aug 22
7 Aug 22
8 Aug 22
9 Aug 44
Etc.
Where query start date would be 1 Aug and end date would be whatever
you pick.

My report should show total number of personnel working on the project
each day. I'm not taking into account holidays or weekends.

Database structure looks like this:
Tables
TblCompanies
CompanyID
CompanyLocation
Etc.

TblProjects
ProjectID
Company ID
ProjectType
ProjectLocation

TblPersonnelDates
PersonnelDateID
ProjectID
PersonnelStartDate
PersonnelEndDate
PersonnelNumber

Data for the number of personnel for each company is updated any time
there is a change to that number (not necessarily on a daily basis).
right now I'm just struggling to get the sums to work right. Any help
is GREATLY appreciated since I've been working on this for a few days
and have made no progress.

re: Calculate totals for period of time

I think you need to create the daily data.

Maybe write a select query to get the company name, the project name, the
start date, the end date and number of people.

Then walk that recordset with something like.

While not rs1.endEOF
For x = startdate to endate
rs2.append
Assign company, project etc to the fields of the new table
x = x +1
Loop
rs1.movenext
Loop

That would leave you with a table with all your data recorded by individual
calendar days.
In the process you could screen out weekends etc as you like.

By the way I assume their is some companyID in theTblPersonnelDates? Else
how do you associate the hours to a company or is each project exclusive to
one company?

"Mayhem05" <EJHansen05@gmail.comwrote in message
news:1158254702.164091.53980@m73g2000cwd.googlegro ups.com...

re: Calculate totals for period of time

Depending on what you want I may have given some less than optimum advice.
If you just want to know the number of staffdays provided you could get that
in a query (Still the problem of weekends,etc).

You would put a calculated field in the query something like:

Staffdays: = (datediff("d",[startdate],[enddate]) +1)* PersonnelNumber

So if there were 12 people for a period of 10 days that would return the
value of 120.

That assumes that the start and end dates are inclusive.


"Mayhem05" <EJHansen05@gmail.comwrote in message
news:1158254702.164091.53980@m73g2000cwd.googlegro ups.com...

re: Calculate totals for period of time

Thank you KC Mass, that does get me started in the right direction.
I'm not concerned about weekends since I am considering them as a work
day for this database. The database is tracking support for emergencies
like natural disasters. I did plan on using a make-table query to
generate a 'temp' table for the data. My dillemma is in coming up with
a means to create this table from my initial query that will populate
records for each day to include the start/stop dates.

My initial query to determine what companies have personnel during said
periods of time is done and works properly. I now need to create the
afore mentioned create table query so I can ultimately create my
reports and charts. I'm just not much of a whiz with VBA.

I hope this further explanation helps clarify my situation.

Thanks,
Eric

re: Calculate totals for period of time

Here is the coding I have so far. I think I'm missing some
declarations in. I have not tried running it since adding in the
coding after calling the query. Up through the query point the coding
worked fine.

What this is suppose to do is populate a table I made based on the
query with records for every day based on user start and end dates.
Each record from the query has a start and end date that will populate
the table with all information except the start and end date. In lieu
of start/end dates it will append a CalcDate based on the current date
in the loop.
QrySupportRpt:
SptState - state
SptType - type of support
StartDate - start date of support
EndDate - end date of support
PAX - number of personnel supporting
UIC - unit id code providing support

TblPaxReport:
SptState
SptType
CalcDate - Date providing support to state
PAX
UIC

Please advise of any modifications to this code I need to make to get
this to work.

Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click

Dim stPAXquery As String

stPAXquery = "QrySupportRpt"

'Check values are entered into Date From and Date To text boxes
'if so run report or cancel request

If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo &
vbNullString) = 0 Then
MsgBox "Please ensure that a report date range is entered into
the form", _
vbInformation, "Required Data..."
Exit Sub
Else
DoCmd.OpenQuery stPAXquery
End If

On Error Resume Next

Dim CalcDate As Date

Dim PaxData As ADODB.Recordset

Dim strsql As String

Dim StartDate As Date

DoCmd.SetWarnings False
' cboTblPaxReport.SetFocus

' Delete records from table
DoCmd.Execute "Delete * from TblPaxReport"

Set CalcDate = Me.txtdatefrom

' Loop for record inside loop for date
' This keeps the dates grouped

If PaxData.RecordCount <0 Then
Do Until CalcDate = Me.txtDateTo
PaxData.MoveFirst
Do Until PaxData.EOF
If CalcDate >= [StartDate] And CalcDate <= [EndDate] Or
CalcDate >= [StartDate] And [EndDate] Is Null Then
DoCmd.Execute "Insert into TblPaxReport (SptState,
SptType, CalcDate, PAX, UIC) values ()"
PaxData.MoveNext
Else
Loop
Set CalcDate = DateAdd("d", 1, CalcDate)
Loop

Else
End If

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub