ansaurus

Question

Answer 1

+2 A:

Whether or not you use Access, you should know what normalization is and why it's important.

It depends on what "severely complicated" means to you. A dozen tables is hardly mind-bending.

one project, that has multiple phases, to multiple employees where id like to track the hours for each employee for each phase

You need some join tables. It sounds like you have several many-to-many relationships here:

employee is many-to-many with phase
project is many-to-many with phase

The employee-phase many-to-many table might be named time_entry, because the hours you enter really need to be associated with both an employee and a phase.

duffymo 2010-06-09 01:16:46

I have no problem with a dozen tables, but i just cant seem to logically figure out how to relate one project, that has multiple phases, to multiple employees where id like to track the hours for each employee for each phase ...i read the article on normalization, i think i ended with more questions than when i started reading

DataMakesMeCrazy 2010-06-09 01:22:00

Answer 2

+2 A:

Perhaps some more details will help. Here is a very rough outline of the type of thing you may want. As you consider, you will see the need for additional tables.

1. Projects
ProjectID
Details

2. Employees
EmployeeID
Details

3. Phases 
For this table, you may wish to use a unique phase ID 
or a combined key created from project and either a look-up table 
numeric ID or a description of the phase. Both have their advantages
PhaseID 
ProjectID 
PhaseDescription             
Details 

4. EmployeePhases
Depending on your decision above, either
PhaseID          )
EmployeeID       ) Unique key
Start Date Time  )
End Date Time

or

ProjectID         )
PhaseDescription  ) Unique Key
EmployeeID        )
Start Date Time   )
End Date Time

It can get a little complicated getting the right data out, but I think you will find that sticking fairly closely to the rules for normalization will allow you to get the data you need, and the data you do not yet know you need, with the least trouble.

Remou 2010-06-09 08:47:34

ok, that makes more sense, thou the information on normalization still hasnt sunk it.with tables as you have suggested, how would the weekly hour data be handled? I have yet to get down to building the database, but the form entry, as i see it, would allow for the projectID to be entered, whos working on the project be entered, the phases for the project would be entered, the start and end date for each phase, then the part about entering the weekly hours.Does access have the ability to dynamically show the correct weeks x to y for data input?

DataMakesMeCrazy 2010-06-09 19:33:33

EmployeePhases is sketched out as a way to hold time detail for employees, rather than a table for the assigned time allowance for a phase. Each employee would bring up a form with details of a project phase and a subform based on this table for entering time spent per day. A query will allow you to pull information of the hours spent per day, the first start date, the last end date, and the overall time.

Remou 2010-06-09 19:45:57

It is also possible to create a calendar, if necessary.

Remou 2010-06-09 19:47:21

You might like to set up some sketchy tables, add a few relationships and look at the way the tables interact together in forms and subforms. You may also wish to look at some sample databases such as Northwind Traders, which ships with all versions or can be downloaded. Some of the code is not so hot, but it is very useful for an idea of how to get tables working together.

Remou 2010-06-09 19:50:12

Thanks for some great advice, definately got me thinking in the right direction.I like the setup for the tables, I just have to figure out how a table will store hours per week data for employee. I dont want to create a table and have to manually enter the start and end date for each week from now until .... How would you picture this kind of data being stored and entered?

DataMakesMeCrazy 2010-06-09 20:40:28

I am somewhat confused. Why would you want the start and end dates of each week? If you want to fill a table with such data, you can with some code, but why would you? A little SQL or VBA will always give you week start and end dates. All you need to fill in is work data, work per week is SQL.

Remou 2010-06-09 20:52:39

Sorry, each project has multiple phases with at least 2 people working on them (ie project manager and a junior). For each phase, we would plan out the start date and end date for that phase, and then the 2nd phase would start after that, and so on depending on the number of phasesthe ideal input would be to enter the project info, enter whos project it is and who is working on it, select which phases are required, enter the weekly hours for the project manager and the employees by week (best estimating we can do is weekly hours).

DataMakesMeCrazy 2010-06-09 21:00:49

But the user entering the data should just be allowed to enter the start and end dates for each phase and a table (in the form) would only let you populate hours for the weeks (ie. start 2-may-2010 and end 7-may-2010 for phase 1, and start 9-may-2010 and end 23-may-2010 for phase 2) then we would estimate how many hours will be worked on each phase by each employee and enter them in weekly columns, but only be allowed to enter for the date range entered ...i typed that fast, not sure if any thing made sense :S

DataMakesMeCrazy 2010-06-09 21:05:01

The start and end date for each phase goes in the Phases table. You can generate a row for each hour in the EmployeePhase table, but I would not recommend it. Get the tables right, that is, that they are normal and have fields for all the data needed, and getting the data in will follow.

Remou 2010-06-09 21:12:22

Yes, seems wondering about a solution without a starting point is a poor idea, point taken.let me get something going here and i'll check back in,thanks again

DataMakesMeCrazy 2010-06-09 21:15:24

It is confusing because Access is really two separate things: a database and a user interface. These need to be treated separately. Get the database right, then put on your developer hat and set up forms to get data into the tables.

Remou 2010-06-09 21:18:47

Oh, and do not stint on tables. For example, you may well need an EmployeeProject table that holds employees assigned to any project, however, do not add tables just for the sake of it, for example an employee and a client are both people with similar requirements as far as data is concerned (address, name, etc) so a people table, rather than two separate tables is often a good idea.

Remou 2010-06-09 21:23:54

I figure there will be many tables here, and its good advice on getting the database up and running first ...the weekly hour information, as to where it goes, what table, and how the table will hold that information should get me thinking the most

DataMakesMeCrazy 2010-06-09 21:34:05

Allen Browne's article on scheduling conflicts might be useful here, too: http://allenbrowne.com/appevent.html

David-W-Fenton 2010-06-09 22:48:12

the layout here is very close, where the date range is shown, but instead of occurances, id like to see that sheet and enter hours ... hmmm, must fish through that one

DataMakesMeCrazy 2010-06-09 23:16:21

Im wondering if i need a table that acts as a calender for each week, ... errr, i know how i'd do this in C++, good only dynamic data

DataMakesMeCrazy 2010-06-11 01:20:03

Maybe you do, but I don't see why. Do you know that when a textbox contains a date field, you can have a little pop-out calendar beside it in Access >=2007? No coding required. If you need to create a daily calendar, you can either use a numbers table to append records or just update a recordset using VBA (... d=Date ... For i=0 To n, rs!Field=d+i ...) SO is not the easiest place for a forum type discussion :)

Remou 2010-06-11 08:32:18

I have only ever needed a calendar when it was a requirement that every working day had data entered, so it made the data entry easier. You might like to hold a table of fixed holidays and some code for calculating movable holidays.

Remou 2010-06-11 08:36:41

Answer 3

A:

The pop out calendar will be good for entering the start and end dates. But then where does the hours for each empoloyee go?

I've got an Excel file set up for this that is huge, slow and has a horrible user interface (one that the other guys here would struggle to use).

I still think Excel isn't right for this task, and Access is the way to go.

In Excel I have a tab for each employee (so adding/editing info is a pain), and each employee has a list of jobs (jobs are repeated for multi-phase projects) and then a list of dates with hours entered. Using may equal statements, the file works, but poorly and doesn't give the information we want without a lot of manual work.

2010-06-11 18:16:26

ansaurus

tags:

views:

answers:

Entering Complex Data into Access

related questions