Track availability of people for up to a year out

[BRZ-Ryan]

I have created an event database where we record upcoming marketing events. Everything is nearly complete with that, but now they asked me to record each day that a marketing rep was scheduled to avoid them being rescheduled for another event on that same day. What I need to do is store each persons availability status on a given day, for up to a years worth of time. So if I create an event, and list a few marketers as going to that particular event, record the date\time for each person (which will flag them as unavailable on that day). I already have a table where I store each marketing rep (name, userid, email addr); would it be efficient if I created a delimited list of dates in a field on that same table? ("1/2/14,1/15/14,2/22,14,3/12/14"), the list could get fairly large. I would then read the value in VBA code, using split() then loop through each date and populate our 'available marketing rep' combobox if the selected event date is not in the list. Anyone have any ideas.



An afterthought:
In the Events table, I have a field where each attending representative is stored in a list. So now I'm thinking, is it inefficient to scan through every event (could be up to 1000) scanning through 20+ users to see if they are in an event on that given day? SELECT Events.Attendees from Events WHERE Events.EventDate = NewEvent.Date AND Events.Attendees 'doesnt contain reps name'? Sorry I'm fairly new to access, is there an InStr/StrFind function in SQL code? I could also cycle through attendees on a given date with VBA split() and do it manually, but I want this to be as efficient as possible.

In events table, attendee names are stored in a carriage-return delimited list.
Name1
Name2
Name3
Name4

[June7]

Storing multiple discrete data into a single field is bad. It does not take advantage of the aspects of relational design and will cause you great deal of difficulty and frustration when trying to analyse data.

A query might be able to handle your data but it won't be an efficient query. Is there a table of all Reps?

Names make poor unique identifier. What if you have two Jane Smith? Better to use an ID. Also, name parts really should be in separate fields but if single name field in Reps, try:

SELECT Events.Attendees FROM Events, Reps WHERE Events.EventDate = [enter date value] AND InStr(Events.Attendees,Reps.RepName)=0;

[BRZ-Ryan]

Yes, there's a table that stores all reps, fields are name, employee id, company email.

What would be a better way to store individual names in the event if multiple in one field is bad? Separate fields for reach rep (Rep1, Rep2, Rep3, etc)?

The table is called Security, so Security.Name is the persons name.

[June7]

Not separate fields. Normalized structure would be related dependent (child) table for the attendees at each event. This is a junction table that relates events and reps.

tbleAttendees
EventID
RepID

[BRZ-Ryan]

OK I will do this, what are the steps, if you don't mind. And will this help my issue?

[June7]

Normalized data structure is always preferable.

The steps are to build and populate tables. If you have a lot of data that needs to be retained, transferring to new structure will be a major effort. Too much for me to detail.

Did you try the suggested query?

[BRZ-Ryan]

No, will try tomorrow at work, I'll let you know

[BRZ-Ryan]

Also, the database is not even in production yet, it just finished development. How do I do what you recommend regarding the separate table for attendees

[June7]

Before you do anything, get a better understanding of relational database principles. Start with http://www.rogersaccesslibrary.com/

What do you mean by 'finished'? Just tables? Or is this fleshed out with queries, forms, reports, code?

[BRZ-Ryan]

Finished as in everything is done and working/ready to use, forms/tables/code, everything. We are giving it to the group Friday for testing.

[June7]

Well, I guess anything can be made to work but there are reasons for widespread acceptance of normalization principles.

Good Luck.