Multiple user entry in one record

K

Kyle

I would like some suggestion as how to setup a database that would allow all
users input in one record per day then the next day will be next new record.

I am planning to create a database with a form to keep track many users who
are testing their ESD strap daily. I like to have a new record generated
each day to record all users’ entry. The form will have these fields.

- Daily Record Number #: (autonumber)
- User’s Name:
- Type of Strap testing:
- Test result: Pass/fail

This is how it works. First user will fill out the above infor, then click
the submit button. Next user come alone fill in and so on. This Daily
Record Number 0001 will have all users list for today in this record. Next
day, a new record number will be 0002. This is like keeping track of a list
of user in one record per day.

Anyone have an example similar to this idea.
Thanks for any help.
 
T

tina

you can't save multiple users' data in a single table record without a great
deal of work and trouble; recommend you don't bother trying because it
violates data normalization principles and is a bad idea. instead, suggest
five tables, as

tblUsers
UserID (primary key)
FirstName
LastName

tblTestTypes
TestTypeID (primary key)
TestTypeName (the various types of strap tests)

tblResultTypes
ResultID (primary key)
ResultName (pass, fail, aborted, waived, etc)
<this table may not be necessary if the only values that are *ever* used are
Pass or Fail. but things have a way of changing in business processes, so if
you design the database to easily accommodate future changes now, it's make
your life easier later.>

tblDailyRecords
RecordNumber (primary key)
<note: do NOT use an Autonumber data type, because you can't ensure that
the numbers will be in order and will be sequential. instead,
programmatically assign this number via code.>
RecordDate
<note: don't name this field simply "Date" because that's an Access
Reserved word.>

tblRecordDetails
DetailID (primary key, Autonumber is okay here)
RecordNumber (foreign key from tblDailyRecords)
UserID (foreign key from tblUsers)
TypeID (foreign key from tblTestTypes)
ResultID (foreign key from tblResultTypes)

you'll have each "daily" record entered once, in tblDailyRecords. for each
user who records a test result on a given day, there will be a record in
tblRecordDetails, linked back to the daily record for that day. example: 30
users who record a test result on 2/19/2006 equals one record in
tblDailyRecords, and 30 related records in tblRecordDetails.

hth
 
K

Kyle

tina said:
you can't save multiple users' data in a single table record without a great
deal of work and trouble; recommend you don't bother trying because it
violates data normalization principles and is a bad idea. instead, suggest
five tables, as

tblUsers
UserID (primary key)
FirstName
LastName

tblTestTypes
TestTypeID (primary key)
TestTypeName (the various types of strap tests)

tblResultTypes
ResultID (primary key)
ResultName (pass, fail, aborted, waived, etc)
<this table may not be necessary if the only values that are *ever* used are
Pass or Fail. but things have a way of changing in business processes, so if
you design the database to easily accommodate future changes now, it's make
your life easier later.>

tblDailyRecords
RecordNumber (primary key)
<note: do NOT use an Autonumber data type, because you can't ensure that
the numbers will be in order and will be sequential. instead,
programmatically assign this number via code.>
RecordDate
<note: don't name this field simply "Date" because that's an Access
Reserved word.>

tblRecordDetails
DetailID (primary key, Autonumber is okay here)
RecordNumber (foreign key from tblDailyRecords)
UserID (foreign key from tblUsers)
TypeID (foreign key from tblTestTypes)
ResultID (foreign key from tblResultTypes)

you'll have each "daily" record entered once, in tblDailyRecords. for each
user who records a test result on a given day, there will be a record in
tblRecordDetails, linked back to the daily record for that day. example: 30
users who record a test result on 2/19/2006 equals one record in
tblDailyRecords, and 30 related records in tblRecordDetails.

hth
Click to expand...
In the table "tblRecordDetails", do you think the DetailID field is needed.
could I use the RecordNumber as primary key instead.
 
T

tina

In the table "tblRecordDetails", do you think the DetailID field is
needed.
could I use the RecordNumber as primary key instead.
Click to expand...

no. by definition, a primary key must be unique. the RecordNumber field is
a *foreign key* that links the detail records back to a single record in
tblDailyRecords. since you'll have multiple records in tblRecordDetails (one
for each user who enters a test result) for each record in tblDailyRecords,
the RecordNumber value will be repeated multiple times in tblDailyRecords -
so it cannot serve as the primary key in that table.

suggest you read up on table relationships and the role of primary/foreign
keys in tables; see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
for links to numerous articles.

hth
 
Top