List Box updating using another listbox's value as Header

[zero2xiii]

Hay all,

I need to construct a very complex student/coach/time/day Database.

Every student can attend a lesson twice a week, on a particular day and time with a particular coach.

Every coach has his/her own time table (Days available, times available on those days).

So I want this effect on a form:
Day: Monday
Coach: Jack
Time: 10:00

So Coach is dependent on Day, Time is dependent on BOTH Coach and Day.

I got the first one working but I am stuck on the second.

I created a Table for every day of the week (Mon through Friday) and have each coach's name as the "header" of a column, in that column I have the times they are available:
Code:

Jack - Roy
08:00 - 09:00
08:20 - 09:20
08:40 - 09:40

In my form, in the event procedure of the second list box I have the following:
Code:

Select Case [Swimming_Day_1]
       
        Case "Tuesday"
           [Time_1].RowSource = "SELECT [Coach_1].Value " & _
                           "FROM [Tuesday Times] "
                      
    End Select

But now when I select for example "Jack" in the "Coach_1" List box, the "Time_1" list box lists 3 entries of "Jack", not "08:00, 08:20, 08:40" respectively.

How can I accomplish the above without having to create a separate Day/Times Table For each and every coach (There are 8 coaches and 5 days, and I want things as easy as possible to update coaches should there come extra or some leave maybe).

So in summary:
Under the "office use" tab:
Swimming day = Fixed source: monday through friday.
Coach 1/2 = Dependant on day in Swimming day 1/2, from "coaches" table check boxes. (This is also an issue I still have to resolve as it is hacked from "coach days" but that needs to change)
Time 1/2 = Dependant on coach and Swimming day, gathered from *day* Times (eg Tuesday Times) table.



For example if:
Swimming day = Tuesday
Coaches = Christine, Devin, Ezanda. - Select Christine
Times = 9:00, 9:20, 9:40, 10:00. (From Tuesday Times).

All of this is working towards having a localized schedule for the swimmers and their coaches. A ground work for moving all the separate parts of the current system to a localized database with all the information in an easy to access and maintain database.

I hope what I am trying to accomplish is clear. I have been trying to get this to work for over a week now. No google or youtube yielded any usable results.

I will include a clean database so you can see what I have at the moment, please bare with me since this is my first real in depth attempt at access and VBA at all. So most things are hacked together from code I found through google magic.

I realize it might need a lot of cleaning up, but I really need to get it functional first.

Thanks in advance.

EDIT:
I am working on 2010, but the computer where this will be used is still using 2007, if that would make a difference.

[ItsMe]

I created a Table for every day of the week (Mon through Friday) and have each coach's name as the "header" of a column, in that column I have the times they are available:

This seems problematic. Do you have a table that holds the coach's names within records somewhere in your data base? An individual coach should have a unique identifier that corresponds to a record (row) within a table where that table has columns that store fields with names like, FirstName, LastName, Address, CoachID.

[zero2xiii]

Hay,

Yes I do, but I am unsure how this is related to my problem? Would you mind to please clarify? If there is an easier way to do what I am trying to achieve I will gladly follow that route.

Thanks

[ItsMe]

There is more than one way to go about it. To try and keep things simple you can build a table that holds the days of the weeks in records (rows). The table will have two columns. One will hold an AutoNumber field and the other something like WkDay. Fill in your WkDay column with the days of the week, Sunday through Monday. In the end you will have a prime key to identify Tuesday or Thursday, 3 and 5 respectively.

Now you have the beginings of a way to store records of activity in yet another table. You will need columns to represent CoachId and the Prime Key of your Day of the Week, among other columns to document the prime key for your studentID, TimeFrameID, etc.

I didn't really go over your objective here but it sounds like you should have a table of students and a table for predetermined time schedules. All of these prime keys can be placed into your New Activity table. I don't quite understand your main goal. but.....

A table that logged activities such as a student's request or an assignment to a particular coach at a particular time could be stored in this table. Then you can print, forward, distribute the query how ever you like.

[zero2xiii]

There is more than one way to go about it. To try and keep things simple you can build a table that holds the days of the weeks in records (rows). The table will have two columns. One will hold an AutoNumber field and the other something like WkDay. Fill in your WkDay column with the days of the week, Sunday through Monday. In the end you will have a prime key to identify Tuesday or Thursday, 3 and 5 respectively.

Now you have the beginings of a way to store records of activity in yet another table. You will need columns to represent CoachId and the Prime Key of your Day of the Week, among other columns to document the prime key for your studentID, TimeFrameID, etc.

I didn't really go over your objective here but it sounds like you should have a table of students and a table for predetermined time schedules. All of these prime keys can be placed into your New Activity table. I don't quite understand your main goal. but.....

A table that logged activities such as a student's request or an assignment to a particular coach at a particular time could be stored in this table. Then you can print, forward, distribute the query how ever you like.

Hay,

Yes your assumption of towards what I am working is correct.

So I do have a table with a layout like this [Coaches]:

ID	Coach	Monday	Tuesday	Wednesday	Thursday	Friday
1	Philip	Yes/No (Y)	Yes/No (Y)	Yes/No (N)	Yes/No (Y)	Yes/No (Y)
2	Marika	Yes/No (N)	Yes/No (Y)	Yes/No (Y)	Yes/No (Y)	Yes/No (N)

The monday-Friday being check boxes (The value in the brackets is the state of the box - Yes meaning they work on that day).

Then I currently have (and want to use unless an easier way is available) the following tables Mon through Fri:
[Tuesday Times] (As an example):

ID	Philip	Maria
1	08:00	09:00
2	08:20	09:20
3	08:40	09:40

So if in drop down box 1 I select "Tuesday"
List box 2 should have options "Philip" and "Marika" based on table [Coaches], say I select Philip,
List box 3 should have options "08:00" "08:20" "08:40"

I did not see the table options earlier, otherwise I could have re-typed my whole lay out in the first post. If required I can do that?
I hope this clears things up a bit more,

Thanks

[ItsMe]

These two examples are bad table design IMO.

Forget these tables. You have a table that lists out your coaches. Start there. Make sure this table has at least CoachID as an AutoNumber or Long Integer. A FirstName Field and a LastName field. Your current examples have field names that don't allow for growth and accepting dynamic data.

Make three more tables. All of them with a prime key that has an Autonumber Data Type. Name them the following

tblWkDay

tblTimeSchd

tblActivities

All but table activities will have will have two columns two hold fields. For example, tblWkDay will have a column for the Autonumber Field and a column for the weekday. Like I explained previously. The second column for the tblTimeSchd will be named something like TimeShift to hold various shift schedules. Give this a text value for now.

Like I said there is more than one way to approach this. This should be a relatively simple DB. A good opportunity to get all of your ducks in a row so you can see how things work.

[zero2xiii]

Hay,

Oka so create tables like this:

tblWkDay

ID	Weekday
1	Monday
2	Tuesday
...	...

tblTimeSchd

ID	TimeShift
1	08:00
2	08:20

Will this be for ALL available times? Like we are open from 08:00 untill 19:00? Or am I miss understanding this layout completely?

tblActivities, I am completely lost with. I get it should have at least 3 columns of which one needs to be an ID field (auto number and prime key) like the other 2.

ID	???	???
1	...	...
2	...	...

So what happens with tblActivities? And am I understanding correctly thus far?

Thanks for the help I really appreciate it!

[ItsMe]

I like the time shift table. Determining the "Business Rules" for the time shifts allowed you to decide on 20 min intervals. It looks very dynamic to me but not so labor intensive in creating the table. So just consider what all of the possible schedule combinations could be. If it can be any combination then that table will be VERY labor intensive to create. If you listed out only a few options like 9 to 3 and 11 to 6 and 4 to 9, it would probably be too restrictive and not work. It is up to how you determine the business rules.

Your tblActivities will act as a major player in your DB. You will be able to use it to log "things" that happen. It will store the Primary key value from another table inside one of its columns. tblActivities needs Foreign Key fields to store these values from the relative tables.

You need to start by giving some better names for the ID's to your tables. You might do well to name the Primary key for tblWkDay, "PrimeWkDay". Something that is intuitive and unique that follows a convention. So WkDayID or PrimeWkDay or something.

With your new ID names you can now go to your tblActivities and create some fields, some Foreign Key fields. Maybe a CoachID and a WkDayID. These will hold the values from their respective tables, records.

[zero2xiii]

Goo day,

Sorry for the major delay, been caught up a bit.

Ok I changed the ID fields as recommended (The names). So what will be my next step in the journey? I guess compiling the coach, day, times available? Just a gamble.

Thanks for the assistance so far I really appreciate all the help!

[ItsMe]

Let's see what you have.

Backup your DB.
Delete any private data from your tables. Open the attached DB, drag and drop the relevant tables onto the attached DB. Upload the attached DB after you update it with your examples so I can take a look.

[zero2xiii]

Hay,

So I copied over all my tables I think is still relevant? Not sure. Non of my forms wanted to copy over (I am using 2010..)

Attaching it.

[ItsMe]

I am having trouble with the attachment.

Can you try and upload it again? First, do a compact and repair and THEN send it to ZIP.

Attach a ZIP file and upload that.

[zero2xiii]

Oka, here is the zipped version and I did the compact and repair.

[ItsMe]

It is still a corrupt file. Go ahead and create your own file over there. All I am interrested in are the tables. No need to upload any forms, queries, macros, etc. Make sure you delete any private data first.

[zero2xiii]

Wow, oka I re saved it as database, and did a compact and repair again, and zipped it again.
Hope it works this time.

If it fails again I will make a brand new DB and re create all the tables, maybe something did not copy right.