Hello All,

I've been trying to build an Access database to manage my company's forecasting for our employees. Basically, we would like to see how busy are employees are in advance, so that we can balance workload and take employees off certian projects and move those projects to employees with lighter workloads. The additional factor is that each project has multiple phases (which we would track seperately under the same project ID), so for example:

Project #: 12345
Descrp: fake project
Owner: Dave (who is running the project)
Client: ABC Corp

then this project will have 3 phases, say (evaluation, design, and construction)

so each phase will have:

Phase desciption: Evaluation
Status: Active
Assigned to: Bill (who is working on this phase, could be several employees)
Start Date: 12-may
End Date: 30-may
% Complete: 0%

then from here, the owner of the project will forecast how many hours each employee will work on each phase (between the start and finish date).

Hopefully this makes sense, Ive tried to get the tables set up, but the hours is what really confuses me, how can that be entered into a table without making hundreds of week rows.

Any advice would be great.

Welcome to AWF!

The table structure of any relational database is critical, so I will focus on that first. From your explanation, this is how I would recommend structuring your tables

tblProject
-pkProjectID primary key, autonumber
-ProjectNumber
-fkClientID foreign key to tblClient
-fkEmployeeID foreign key to tblEmployee

tblClient
-pkClientID primary key, autonumber
-txtClientName

tblEmployee
-pkEmployeeID primary key, autonumber
-txtEmpFName
-txtEmpLName

tblPhases (you will have 3 records in this table:evaluation, design, and construction)
-pkPhaseID primary key, autonumber
-txtPhaseDescription

A project can have many phases; and a phase exists in multiple projects

tblProjectPhases
-pkProjPhaseID primary key, autonumber
-fkProjectID foreign key to tblProjects
-fkPhaseID foreign key to tblPhases
-StartDate
-EndDate
-PercentComplete (if this value can be calculated from other data, then you would not need a field)

It is best not to have spaces or special characters (%,!, # etc.) in your table and field names.

Since many employees may work on a phase, then you have a one to many relationship

tblProjectPhaseEmployees
-pkProjPhaseEmpID primary key, autonumber
-fkProjPhaseID foreign key to tblProjectPhases
-fkEmployeeID foreign key to tblEmployees


In addition to forecase hours are you also capturing actual hours worked?


...then from here, the owner of the project will forecast how many hours each employee will work on each phase (between the start and finish date)...but the hours is what really confuses me, how can that be entered into a table without making hundreds of week rows.

I don't see a way of getting around entering the data, but you could set up ways to automate the entry using append queries, but that will have to wait until the table structure is finalized.

I think for now I would just like to use it for forecasting (although it would be great to see how well we are forecasting)

But back to the hour data... so with that table set up, if i have Bob and Jim working on project 1234, and in the evaluation phase, which runs for 2 weeks ... how can i collect this data. Entering it manually is okay, but where would the data be stored? Ideally the input would go:

Project 1234
Phase Evaluation, Start DATE1, End DATE2

Employee ------- week1 ------ week2 ----- week3 ---- etc
Bob ------------ 8 ----------- 5 --------- - ---------
Jim ------------- 2 -------------2 -------- - ----------

So that for each week we can total not only the project hours, but each employee's hours and summarize that data.

I hate when things seem simple on paper ! haha

Oh, and thanks for the very detailed and quick reply!!

You will need another table to capture the forecast hours

tblProjectPhaseEmployeeHours
-pkProjPhaseEmpHoursID primary key, autonumber
-fkProjPhaseEmpID foreign key to tblProjectPhaseEmployees
-periodstart (or end depending on your business rules)
-WorkHours

Once the data is entered, you would use queries to do your calculations for employee hours for a project and total project hours, and then use forms or reports to display the results of the calculations.



Now if you want to capture ACTUAL hours worked, you will have to deal with the time worked that was not tied to a particular project (non-project time, vacation time etc.) as well as time worked on a project. I don't know if that is beyond your scope at this time, but if it is something you want to do, setting it up all at one time might be more efficient.

You will need another table to capture the forecast hours

tblProjectPhaseEmployeeHours
-pkProjPhaseEmpHoursID primary key, autonumber
-fkProjPhaseEmpID foreign key to tblProjectPhaseEmployees
-periodstart (or end depending on your business rules)
-WorkHours

Once the data is entered, you would use queries to do your calculations for employee hours for a project and total project hours, and then use forms or reports to display the results of the calculations.

--------------

I thought this is what i needed, but how does this relate to entering hours in for each week. If a project is 20 weeks long, i will need 20 locations (for a lack of a better word) to enter weekly hours.

I sort of felt this is where access wouldnt be able to handle the kind of data, almost needs dynamic data where we know the start and end dates, and can enter weekly data in based on that information into an array.

Using the table you have suggested how can this be done, especially considering the hours will not be the same each week

You will need a record (your "location") in the tblProjectPhaseEmployeeHours for each project-phase-employee--time period combination. You can automate the creation of records using Visual Basic for Application code within Access, and then have someone go in and put in the actual hours if that varies in that particular field.

If you have many projects and many employees you will have a lot of data; Access will be able to handle it much more efficiently than a spreadsheet, but it has to be captured in the correct table structure as I have suggested.

Would it be possible to cheat the system so to speak, and manually create a table of weeks? ie have excel populate column heads based on

A1 --------------- B1 ------------ C1
1-jan-2010 --- = A1+7 -------=B1+7

Then access could allow for data to be entered in a range based on this?

Yes, you could create a table that holds the week starting dates, but the structure would look like this

tblWeekStartDates
-pkWeekStartID primary key, autonumber
-dteWeekStart

And you would reference it in this table:

tblProjectPhaseEmployeeHours
-pkProjPhaseEmpHoursID primary key, autonumber
-fkProjPhaseEmpID foreign key to tblProjectPhaseEmployees
-fkWeekStartID foreign key to tblWeekStartDates
-WorkHours


Or you can have the VBA code I mentioned work out the starting date for each week at the time the records are created. You can bring the project start and end dates into the code and have it create all the records that will be needed in tblProjectPhaseEmployeeHours (you will have to define each phase and the associated employees prior to running the code)

You cannot have a table formatted like this:


Employee ------- week1 ------ week2 ----- week3 ---- etc
Bob ------------ 8 ----------- 5 --------- - ---------
Jim ------------- 2 -------------2 -------- - ----------

In this case the week1, week2... constitutes repeating groups which violate normalization rules. For more on normalization, check out this site (http://en.wikipedia.org/wiki/Database_normalization).

The normalization is something I have been reading a lot about, and trying to keep within the NF rules.

The VBA coding appears to be beyond me, although I have a lot of C++ coding, I really dont have any experience with VBA.

I have an excel database which is *doing* what i need, however, its user interface would be summarized as brutal, and repeat information is everwhere. Plus creating new records is tedious to say the least.

I may revert to trying C++, using class based code, something i think i can see a possible way to get it to work.

Looks good so far! The basic logic behind VBA should be the same as C++; the command syntax will be different of course. I am guessing you will need some nested recordset loops to create the records in the hours table. From there, someone will probably still have to enter the hours value for each record since they are variable. How do you have Excel doing it now?

Once you automate things, you would enter the hours just as you currently do with Excel. Now if you were to come up with some rules, you might be able to add some hours via automation, but that depends on your business more so than Access.

jzwp,

Thanks for the great help, I think the whole thing just sort of clicked in my brain and i can see how it will all come together ... including the weekly hours, which had puzzled me.

Check back in once I've got something more substantial with forms and queries

thanks again

You're welcome! Good luck with the project.

One thing,

Entering this data in ... for examle when im entering a new project, do i have to tell access to look up the clientid when entering information, because they are already linked, or will i need several subforms to get this working properly?

On your main project form, create a combo box control that is based on the client/customer table. Bind the combo box to the foreign key field for the client in your project table.


Using the example I provided earlier (see below), you would bind the combo box to the fkClientID field of the project table.

tblProject
-pkProjectID primary key, autonumber
-ProjectNumber
-fkClientID foreign key to tblClient
-fkEmployeeID foreign key to tblEmployee


Now, if you need to create a new project for a new customer, you will have to create the customer record first and then go to the project form.

There are ways in Access to use the Not In List event of the combo box to execute code that would open a form to enter a new customer, but that gets a little more involved.

Thanks!

I keep it simple, and ensure all the data is in for Clients and Employees prior to entering any data in otherwise ...

will check back in shortly

Thanks

I'm not sure I quite understand what you are asking, but you should be able to use a combo box based on your week start table (or an appropriate query) to populate the fkWeekStartID control & display the appropriate week start date (you can calculate the weekend date using the dateadd() function). You should also be able to limit the dates based on the phase start and end dates (you'll need a query that references those other controls in the appropriate subform). The user would have to select the start date from the combo box. If you want to add the dates automatically so your user doesn't have to, you will need some VBA code to loop through all projects, phases, employees.

Attached is the weekstartdates table

I dont want the user to have to scroll though these values, rather knowing the phase.startdate and phase.end date, just allow hours to be entered here. but it should display these records all at once

i doubt im explaining it very well ... but it would be confusing for someone to enter hours if they have to find the exact weeks they are looking for

I did not see an attachment.

If you want to populate a record for each week start date that occurs within the phase start and end dates, that will have to be done ahead of time manually or using VBA code.

Actually the VBA might not be too involved. You could do it when a new person is added to a phase (as long as the phase information is already populated).

Even without VBA, you can limit the start dates shown in the combo box by using a query for the combo box. (I'm not sure if I have the form and control names correct, some of the names were cut off in your attachments). The query might go something like this:

SELECT pkWeekStartID, dteWeekStart
FROM tblWeekStartDates
WHERE dteWeekStart between forms![tblProjectPhases subform1]![start date] and forms![tblProjectPhases subform1]![end date]

that sounds promising ... i think my abilities with access havent yet grasped queries so well, let alone filtering the data ...

Anytime you want to filter data, you are basically using a query, so no time like the present to start learning.

In Access, you can design your queries in a couple of different ways. I usually start off with the design grid, add the tables you want as part of your query. In this particular case, you only need the week start table. Then select the fields you want in the query. You will need the primary key field pkWeekStartID (not sure if I have the full field name) and the week start date field dteWeekStart

Now, you can switch from the design grid view to the SQL view and you should see something like this:

SELECT tblWeekStartDates.pkWeekStartID, tblWeekStartDates.dteWeekStart
FROM tblWeekStartDates

The SELECT clause indicates the fields that will be displayed by the query (it gives the full reference tablename.fieldname

The FROM clause tells you from which table or query the fields are selected . When more than one table is indicated in the FROM clause, you might see something like this FROM table1 inner join table2 on table1.pkField=table2.fkField

To make life easier when constructing queries, make sure to complete your relationships in the relationships window. Access will use those relationships in the construction of the query. You add the tables you want to the query and Access will automatically carry over the relationships and construct the FROM clause for you.

The WHERE clause or criteria section of the query is what really does the filtering. You can switch back to design view. For the column with the dteWeekStart field, find the corresponding row for criteria and enter the following: between forms![tblProjectPhases subform1]![start date] and forms![tblProjectPhases subform1]![end date]

This tells Access to go to the particular controls on the form and see if the tblstart date falls between the two dates. For this to work the form has to be open and the currently displayed record must have phase start and end dates specified. If you try to run the query without the form open, the query will basically ask you to input the 2 dates manually. The thing I am not sure about is whether you have to use the full form reference since the phase start and end dates are in the subform, so you might need something like this:

BETWEEN forms!tblProjects![tblProjectPhases subform1].form.[start date] and forms!tblProjects![tblProjectPhases subform1].form.[end date]

so i followed your steps and have
Between [Forms]![formProjectPhases]![StartDate] And [Forms]![formProjectPhases]![EndDate]

but as you mentioned, this probably wont work for a sub-form structure?

It seems like the most intuitive way to enter this data ...

The WHERE clause was just a way to filter the combo box data, so your user didn't have to look through all of the dates, but only the dates within the range of the phase.

Did you try this variation of the WHERE clause?
BETWEEN forms!tblProjects![tblProjectPhases subform1].form.[start date] and forms!tblProjects![tblProjectPhases subform1].form.[end date]

If you want to post your DB (with any sensitive data removed), I can try to get it to work.

I'll have to take a look at it tonight from home; we don't have Access 2007 here at work.

I did have to use the full form reference, so the query for the combo box ended up looking like this:

SELECT tblWeekStartDates.pkWeekStartID, tblWeekStartDates.dteWeekStart
FROM tblWeekStartDates
WHERE (((tblWeekStartDates.[dteWeekStart]) Between [forms]![formProject]![tblProjectPhases subform1].[form].[startdate] And [forms]![formProject]![tblProjectPhases subform1].[form].[enddate]));


I've attached your database with the modification

It didnt appear to work, also the layout, while looking better, doesnt seem to allow multiple entries for employee per phase ...

I could only seem to get that to work using a database view.

But i tried entering the dates, and granted some got filtered, not all of them ...

It didnt appear to work, also the layout, while looking better, doesnt seem to allow multiple entries for employee per phase ...

The only way to view all of the subforms is to use the single form view (with the exception of the final subform) rather than the datasheet view. You still should be able to enter multiple employees per phase unless you turned off the record navigation buttons at the bottom of each form.

But i tried entering the dates, and granted some got filtered, not all of them ... Can you tell me when they were not filtered (what had you done)? Perhaps we need to add some code to the on current event.

I forgot to save the DB in an earlier version of Access, so again, I won't be able to look at it until tonight.

Dont go out of your way, you have helped greatly, and i appriciate it ...

Im just realizing how, as I'm trying to build a simple report that I really don't know Access at all. haha.

I'm just spending time reading up on things. I can send you the excel file of what im trying to convert to access and you would see right away why access would work so much better. Im just trying to get the basics down, then i can try to make it a more user friendly data entry program.

Ideally the database should:

1. Summarize each employee's total hours per week (then ideally a summary table with all the employees so that at a quick glance we can see if someone has too much work)
2. Summarize each project listing the weeks and showing the hours worked on each phase by each employee
3. Sortable master by project owner

Once i've grasped getting this together, i think i can return to the detailed aspect. The database as you have helped me build to date holds all the correct information, I just have to peice together using and reporting that information. With the vast number of tables, I really need to read up on that part.

I thought it would be easy to produce those reports, nope, not so much, haha

It might be beneficial to see the spreadsheet you mention that way I might get a better idea of what you are modelling. It might also help point out any table issues that would need to be fixed before getting too far with your forms and reports. The table structure is the MOST critical aspect of a relational database, so if there are errors there everything else becomes difficult!


1. Summarize each employee's total hours per week (then ideally a summary table with all the employees so that at a quick glance we can see if someone has too much work)
2. Summarize each project listing the weeks and showing the hours worked on each phase by each employee
3. Sortable master by project owner

Access should have no problem with any of the above objectives, but you would not create a summary "table" as you suggest in #1 above; you would use a query to create the summary and display it in a form or report for your users. Your users (employees) should NEVER see your tables, they should only interact with the database through forms and reports.

You will need queries to generate the reports you need. You will use the queries to bring the data together from multiple tables.

I had to add a requery command to requery the combo box (in the hours subform) when migrating between current records (on current event) of the phase subform. Additionally, I had to add the same command to the after insert event of the phases form so that the combo box is requeried when a new phase record is added.

I also reformatted the forms so that you can see the record navigation buttons along the bottom of each form and subform. The modified DB is attached.

I think the datasheet view will work better, because the fields dont clear when you select a new phase, or when you add an employee to a phase. It would be very confusing.

The hours entered works for the first phase perfectly, then when you switch to add a phase, enter new dates, the old dates show up in the combobox

Did you see the excel sheet? Fun wasnt it?

It worked fine for me last night. Did you try this after you went back to datasheet view? If so, that will probably not work right; I believe it has to be in single form view (or I messed up and should have used a different form event?)

In terms of organization, yes, having that many subforms may be confusing to your users. It might be better to use some simple forms and set up ways for your users to migrate between them. Using some navigation forms, I generally have the user choose whether they are creating something new (new project, phase etc.) or just adding data to an existing project/phase etc. You can prompt the user for specific information by providing combo boxes for selecting various things and then take them to that specific record. Of course, that takes a little more time to set up, but your users won't get frustrated!

jzwp22,

Thanks for the help, Im going to keep hacking away at in and will check in

Can you delete the post with the attachment of the database, just has a couple of the guys names on it, would be appriciated, thanks

I modified the data in the attached database in my earlier posts and reattached that modified version. I wanted to keep it there in case anyone refers to the thread in the future.