I need to design a database to house the following type of data (there are other fields involved to be connected to each person at each time eg tick box if attended):

Time---Person1---Person2---Person3---Person4
6:00-----John-----Peter-----Paul-----Sam
7:00 -----Mary-----Lisa -----Julie-----Mike
8:00-----etc
etc

Should I set up the the table structure data as:
time (ie 7:00, 8:00 etc)
Person (ie Person1, Person2 etc)
PersonsID
Attended (Yes/No)

and have four separate records for each time slot
and use crosstab queries (Though I want users to be able to put their own name in, and I think using Access Crosstab that you cant do this :mad:)

OR
Should I set up the table as follows:

Time (ie 7:00, 8:00 etc)
Person1
Person1Attended
Person2
Person2Attended
Person3
Person3Attended
Person4
Person4Attended

and have only one record for each timeslot.
If I did it this way how would I select and report on each individual person (would I need four separate queries?)

Thanks in anticipation
Penny

What i would do (FWIW):
Create a table with the fields Time and Person. If the person doesn't have a record he/she didn't attend. If you want to a recordset/query result with both the persons who attended and who didn't attend, just use an outer join with the persons table.

HTH:D

That is how I wanted to do it, but to display in a form so people can put their own name in (with 4 allocated places), I needed it with row headings "time" and column heading "Person",(As depicted at the start of my post) so the person could put his/her name in timesheet in the body of the form.

However to get this format in an form where the names can be edited, I would need to do a crosstab, and it seems I cant edit the central cells. That is why I was considering the alternative.

Any further thoughts?

Difficult, however not impossible to do when you are using bound forms. In most cases, beginners always use bound forms. You can solve this by creating a make table or an append query to create a temporary table to which you bound your form. Afterwards, you must read the table and store the entered values in their proper tabel and delete/empty the temporary table. This way keeping the database relational.
Using an unbound form you can use the same form design as above but unbound. When saving the record, you can store the entered values in their proper tables.

Enjoy!

Thank you. It does sound like alot of extra work though. Especially to incorporate data checks (eg, same person hasnt enrolled twice).

I was thinking maybe if I displayed the data (uneditable) as a crosstab on the top of the form, and in the body put a subform where names could be selected, then used code to update the selections.

This may save me from having to create extra the table.

In your opinion, is there any "right" way to do this, or do I just do what I'm more comfortable with?

P.S. It really helps to get ideas from others who can think outside the circle that Im thinking in!

I believe you are almost there!
When you use a combobox instead of a subform you are nearly done. You can populate the combobox using a query. In this query you can make a selection of the persons who didn't enroll yet.

There is not *one* way to fix this. Stick to what you are comfortable with. If all your forms are bound, why change now?

I was thinking...
If I was to set up a temp table (along with all the extra work:rolleyes:) it may help me with my other major issue with this database

i.e. how to prevent other users from booking a same timeslot which is currently being edited. When someone goes to book a time, I want to lock all 4 records so they are not available to anyone else to book.

I hadn't yet figured a way of doing this and was thinking that if I created a temp table, I could code - If temp table exists for this timeslot, then these records are locked, and send message to user.

It may also solve another issue (yes there are many:eek:), of creating a log of who had booked who i.e. transfer records from temp table to log for each edit.

mmm... my tired brain is in overdrive, and each issue I try to resolve leads to many others. I guess this is why programmers get paid so much!

It depends on which planet you're on. I don't believe that earth is the place to be as a programmer... or any other occupation.

Yes, i'm reading Douglas Adams at the moment:D

Thanks for your help. It has been invaluable :)