Advice on best way to set up a new database

[red6000]

Hi, I have just successfully set up my first access database after many
hours investment and I'm now about to start on my 2nd.

I would like a bit of advice on the best way to set it up. I am capturing
the following:

name
time start work
time go to lunch
time return from lunch
time spent on breaks
time left work
plus time spent on task (about 200 tasks)

What I then want is to be able to run reports on how much time has been
worked and how this is split between tasks (both at individual name level
and at total level) and (both at daily, weekly, monthly level).

The main element that I am unsure of is the best way to record the 200
tasks.

Any advice greatly appreciated, should I just go for 1 big table with a
field for each task and thus one row per person/day?

Thanks.

 

[Guest]

Hi

I'm good enough at this stuff (access) to give advice on setting up an
entire d base - I have made many myself and they are all different as the
clients want them to record different items and do different functions (and
results).

But in general - if you have already set up a d base yourself it would be a
good idea to start the next as a fully relational database. You will need at
least 2 (prob more) tables and other items such as forms queries, reports,
etc what I am getting at is that this would be an ideal time (your second d
base) to start to learn the basics and then see how far you can progress
towards your aims -that is, as all programmers will agree - is to get a
database that will do what its meant to and, hopefully allow you as the
programmer to learn a little bit more as you progress.

Start with a couple of tables and see where you can go from there.

The one bit of advice I will give is to try and work out as much as possible
what you want the end result to be and then design the d base along those
lines.

Good luck

 

[Guest]

ooops = very important "not" missing from the 1st line of last post.

I'm "not" good enough at this stuff

 

[John Vinson]

Hi, I have just successfully set up my first access database after many
hours investment and I'm now about to start on my 2nd.

I would like a bit of advice on the best way to set it up. I am capturing
the following:

name
time start work
time go to lunch
time return from lunch
time spent on breaks
time left work
plus time spent on task (about 200 tasks)

What I then want is to be able to run reports on how much time has been
worked and how this is split between tasks (both at individual name level
and at total level) and (both at daily, weekly, monthly level).

The main element that I am unsure of is the best way to record the 200
tasks.

Any advice greatly appreciated, should I just go for 1 big table with a
field for each task and thus one row per person/day?

NO... absolutely NOT.

That's called "committing spreadsheet on a database" and it's a venial
sin, punishable by being required to read Codd and Date's textbook in
its entirity. <g>

If you have a Many (employees) to Many (tasks) relationship, you need
*three* tables, one of them a table of Tasks, one row per task. I'd
actually suggest creating two additional "tasks", Lunch and Break; the
accounting will be simpler. Your tables would be something like

Employees
EmployeeID <Primary Key>
LastName
FirstName
<any other needed bio information>

Tasks
TaskNo <Primary Key>
TaskName
<other info about the task itself>

Timesheets
EmployeeID <Primary Key>
WorkDate Date/Time <Primary Key>
StartTime
EndTime

Activities
EmployeeID <Primary Key>
WorkDate <Primary Key>
TaskNo <Primary Key>
StartTime
EndTime

Each activity will be stored as a record in the Activities table; the
EmployeeID identifies who was doing the work, the TaskNo what they
were doing, and the start and end times identify when.

This can get much more elaborate, but the principal will hold that you
store data *in fields*, not in fieldnames!

John W. Vinson[MVP]

 

[red6000]

Hi, I'm only just getting around to setting up my database using the advice
below.

The advice suggests that in the Timesheets table I should have 2 primary
keys, but how do I do this as it only lets me have one?

Thanks.

 

[red6000]

okay, got it!

Hi, I'm only just getting around to setting up my database using the
advice below.

The advice suggests that in the Timesheets table I should have 2 primary
keys, but how do I do this as it only lets me have one?

Thanks.

 

[red6000]

Okay,

I've set up my tables, but I'm not really sure what to do now?

Do I set up a form purely based on the activites table? If so then what is
the purpose of the other tables?

Thanks.

 

[John Vinson]

Okay,

I've set up my tables, but I'm not really sure what to do now?

Do I set up a form purely based on the activites table? If so then what is
the purpose of the other tables?

You'll use tables like the Employees table as the rowsources for combo
boxes (so you can select which employee is doing an activity); or the
Activities table to select which task they're doing...

John W. Vinson[MVP]