Time saving form question?

[culedude]

Big question I know

ok, i have my table set up with the following fields:
Date; Name; Shift; Area; Plant; JobDescr.

I plan to create a form where the user1 can enter the following details just once:
Date; Name; Shift.

Then below, the user1 would need to enter the following fields several times:
Area; Plant; JobDescr

All the above fields need to be recorded in the database on the same table:
Date; Name; Shift; Area; Plant; JobDescr.

User2 would need to come along and generate a report which would look something like this:

22/18/06 Joe Bloggs B Shift

Area 1 (heading)
Compressor (Plant)
Machine overhauled, plant repaired etc, etc.

Area 6
Boiler
Boiler serviced blah blah blah. maybe several lines of JobDescr.

Area 67
Car
Moved due to blocking fire escape.

User2 would need to be able to call up what happened in any given area between any given date or any given plant.

I apologise for the longwindedness of the question.

Can anyone guide me to the easiest way to do this

TIA
culedude

 

[ShaneMan]

All the above fields need to be recorded in the database on the same table:
Date; Name; Shift; Area; Plant; JobDescr.

Hey culedude,

Why do they need to all be in the same table? It appears to me that you would be better served in dividing your fields into at least two tables and maybe more. If you don't then it appears that you would be entering redundate data and that would violate database normalization. Folks here are more apt to help if they can see good table structure. Just trying to help.

Shane

 

[Fear Naught]

Hi CuleDude.

The ShaneMan is right in what he says about database normalisation. You need to have at least 3 tables for what you are trying to do. Also the fields in your table need changing. The fields Date and Name are reserved words and WILL give you problems along the way if you don't change them.

I suggest the following tables.

tblStaff:

StaffID         AutoNumber
StaffName       Text

tblShift:

ShiftID         Autonumber
StaffID         Number
ShiftDate      Date/Time

tblShiftTasks:

ShiftTaskID       Autonumber
ShiftID          Number
Area             Text
JobDesc           Text

You may also want to add another table for areas if they are static.

You can then create a form with a subform.

I hope this helps

 

[culedude]

WOW thanks guys, i'll get to work on your suggestions immediately, it means starting all over again but hey, i guess that's ACCESS lol