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, butright 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.