473,287 Members | 1,492 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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).
>From the sums that I pull I will need to convert this to a graph, but
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.

Sep 14 '06 #1
4 6681
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" <EJ********@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
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

Sep 15 '06 #2
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" <EJ********@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
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

Sep 15 '06 #3
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

Sep 19 '06 #4
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

Sep 20 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: CrystalDBA | last post by:
I am using SQL Server 2000. I need to query my database for all the contracts that came in during a certain time frame (user is prompted for reportingperiodid). Table - Periods Fields -...
53
by: Cardman | last post by:
Greetings, I am trying to solve a problem that has been inflicting my self created Order Forms for a long time, where the problem is that as I cannot reproduce this error myself, then it is...
14
by: Alan | last post by:
Hi everyone! I'm trying to produce a periodic financial report on projects from various departments. My database is set up with the tables tblDepartment, tblProjects, tblPeriods, and tblBudgets...
1
by: Ladislau S. | last post by:
Dear reader, I am an occasional user of MS Access 2000 running on Windows 98. My hobby is ship model building so I made a database for things that I want to buy. After two strokes I bin unable...
1
by: jaswmil | last post by:
I have a query (see SQL below) that essentially grabs a list of all employees terminated during a specific period. What I am needing to do is to be able to take this number and then divide it by...
4
by: NormAmst | last post by:
I have a list of CPU processing times and job durations for an entire department at work. There are 3 classifications I am maintaining. CPU time during peak hours , CPU time during non peak hours...
6
by: Stuart Shay | last post by:
Hello All: I have a array which contains the totals for each month and from this array I want to get a running total for each month decimal month = new decimal; month = 254; (Jan) month =...
4
by: khicon73 | last post by:
Hello All, I would like to calculate weeknumber and period from first day of the week (sunday) and last day of the week (saturday) and week number falls from 1 to 52 only. If week number >= 53 then...
15
by: student4lifer | last post by:
Hello, I have 2 time fields dynamically generated in format "m/d/y H:m". Could someone show me a good function to calculate the time interval difference in minutes? I played with strtotime() but...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.