Best Way to Setup Table/Form Fields for Attendance Tracking (Attendant1, Attendant2 etc...)

[Machinery]

Hello,

I was wondering if anyone would be willing to share any insight into how to set up a form where a user enters in the names of attendees during a meeting.



Right now, I have it set up as separate fields for each attendant. Therefore, there are twenty attendant fields named [Attendant1], [Attendant2] .... [Attendant20]. I believe this structure would be fine if the only purpose were to store the data. However, I can foresee issues once I want to recall specific meetings by searching for individual attendants since any attendant could have been entered into the attendant1-20 fields.

Long story short, I am asking if there is a better way to setup the fields or if there would be an easy way to create a search form for when I want to search by attendant. I am thinking that if I keep it in the structure I have, then I would end up using union queries and then perhaps losing the integrity of my data.

Thanks for any feedback!

[orange]

Yes there is a better way.
research Normalization and Primary Key

A better structure would be

tblAttendees

AttendeeID autonumber Primary key
AttendeeFirstName text
AttendeeLastName text

[Machinery]

Thank you for your reply.

I understand Normalization and Primary Keys and essentially, that is how I have it structured except for that in tblAttendees I have 20 attendee fields. I am still not sure how to get around having 20 separate fields for the attendants. Forgive me if I am missing something obvious but I don't understand how the structure you suggested would allow for anymore than just one attendee to be entered. Any additional attendee entered would create a new record from the AttendeeID.

Do you have any additional insight you can share?

[orange]

Consider tblAttendees

AttendeeID  AttendeeFirstName   AttendeeLastName
         1            Tom                   Foolery
         2            Yura                   Payne
         3            Suzy                   Queue
.....
        9999        Zack                   Zutramannin
      10000        Rory                    McIlroy

[Machinery]

Rory chuckin' that 3 iron because he's at the bottom of the list!

Thanks for the visual. Are you suggesting that I pre-load all possible attendees into tblAtendees so they could be selected on the form? If so, I'd like to stay away from that. Even though things would get messy from manually typing names, that doesn't off-set the need for someone to have to go in and update tblAttendees.

I apologize, but I am still confused. I am still unsure on how this would allow the user of the form to relate more than one attendee to the new record.

[orange]

Let's step back and you tell readers WHAT you are trying to do in plain English.
Let's make sure we are all on same wavelength as to issue description and desired output.

I have a suspicion that you may have experience with excel(spreadsheet).

[Machinery]

Plain English = I have an attendance form in which up to twenty names can be input via manual typing into text boxes. These twenty names are twenty different fields in tblAttendees. Sometimes all fields are used when creating a new attendance form and sometimes not. Sometimes Tom Foolery is listed in the attendee #1 field and sometimes he is listed in any other position. The end goal is to use a search form to find attendees and then be able to navigate to their corresponding records. Does that make sense?

What I want to know is if there is a different way to structure tblAttendees where I don't have twenty separate fields. It just seems like the way I am doing this is not proper and with my limited experience, I am unable to think of any other way to do it.

[Machinery]

Thank you for the link btw. That will definitely come in handy.

[orange]

Post a jpg (screen capture) of your table Attendees design so we can see what you have.

[Machinery]

I am out of the office today and tomorrow so I will post on Sunday. However, my table is identical to what you posted except for your first name and last name fields would be replaced by attendee1, attendee2 and then an additional 18 fields up to attendee20.

My inquiry is simply if there is a different/better method to enter 20 attendees rather than using 20 separate fields. I ask because I would like to do it the right way. Perhaps I already am?

[orange]

Did you follow the sample in post #4?
That's how you set up a table for a number of People or things. You don't put the same thing in different fields.

I think we're all waiting to see your table design to see what it is you are working with.

[Machinery]

As promised, here are some screen shots of my table as well as my form with the table's fields represented as text boxes. I hate to keep beating a dead horse, but is there a better method to input attendants on a form rather than having 20 some odd separate fields? The end goal is to be able to search records by attendant name, which I can accomplish, but would like to do it in a proper and clean manner. Hope the images provide some insight and thanks for taking a look!

[orange]

I think you have completely missed the point of relational database.
Find a tutorial or video on Normalization.
I'll recommend this one it leads you through the process in steps, and you will learn by doing.
Here's another from the same source RogersAccessLibrary.

In very plain English, if you have People who could attend and Event, you would have a general set up as follows.

There are several people/persons
A Person may attend 0,1 or more Events
There are several Events
An Attendee is a Person who attends/attended an Event
An Event occurs at a specific plave and time.


The tables involved:
tblPerson
tblEvent

and to identify which People attended which Event requires a junction table

tblPersonAttendedEvent


tblPerson
PersonID PK
FirstName
LastName
PhoneNumber
Addressline1
Addressline2
City
State
Zip
..etc

tblEvent
EventId PK
EventName
EventLocation
EventContact
EventStartDate
EventEndDate
...etc

tblPersonAttendedEvent <<<--people in this table are Attendees
PersonAtEventID PK
PersonId FK to tblPerson +++++
EventId FK to tblEvent +++++
... any info specific to this Person and this Event

The 2 fields with +++++ would also be used to create a unique compound index to prevent duplicates.

Use google to research.

As for your data entry, you should research MSAccess Form/Subform. There are several tutorials on youtube.
Good luck.

[Machinery]

Thank you for the detailed response, it is greatly appreciated and useful.

Perhaps I have missed the point but perhaps not. This may be totally wrong, but the way I interpret the structure you described working is that I would input all employees information into tblPerson. Then whenever a new person is hired, their information would need to be added <----- This is where I would be missing the point the most if that's not true.


Google is great!

[orange]

Let's go back to the 1 line overview of what your database is about.
In 1 -2 lines - describe a typical day in the life of the business this database will support(simple English - no techie stuff).

Also you talked about Attendees (without much context). Think of it from the readers perspective.

Attendee to what? Can you describe Attendee for readers? I gave a scenario involving Person Attends an Event, but that's just an example on my part.

From a logic view, how would you do the new employee if you had paper files?

PS: Did you work through the tutorials I suggested?