Lookup Tables design help

[Carouser]

In my current database, I have 3 fields that have drop down combo boxes linked to lookup tables respectively. The look up tables are getting excessive with options not relevant for a particular day, or session.


I am after some advice about setting up lookup tables for 3 fields. Where this is getting beyond me is the interdependence of exhibiting only certain data pertinent to the former fields.

SessionsTbl
ID-PK
Presenter ID
Date
Session
Room
GoTo-hyperlink


The date field is easy enough, But there may be different time session on each day. Sessions may be Day1: 0900, 1000, 1100. Day2: 0830, 0930, 1030. etc


Now for rooms, on Day1 @ 0900 there is Rm1 only as an option, Day1@1000, there is Rm1, Rm2, Rm3 as options. I am envisaging some kind of matrix with radio buttons, but not sure how this will go together.

At the moment, the concatenation of these 3 fields gives me a hyperlink to specific folders. I create these folders manually and if I can get the above to work, I would like to create a macro to make directories from this information. I have read many normalizaion articles, and scoured the internet, but I am still coming up short of direction.

Regards

[June7]

What is this db for - some kind of event scheduling? Have you looked at the template Events database?

[Carouser]

Hi June7

I have looked at the Events template and it is not what I am after. I created this database a few years ago using books and the net, so my skill level is slighty above novice. My database keeps track of presenters for conferences. There are usually several sessions running concurrently with several presenters in each session. All presentation activity is from a centralized Speakers preparation room where PPT presentations are checked and placed in the appropriate folders for up loading when we have gathered all presentations for the session. The system I have in place now works efficiently to process high volumes of presenters after all their data has been entered prior to the conference and their status check boxed during processing which generates reports to find who is missing for sessions.



I am wanting to restructure the date, session and room tbl so that the date field will open a particular session tbl available for a particular day, and only list rooms available for that session. This is where I can't seem get my head around a table structure that if you select the date field, will lead to sessions for that day, but I would have to create or delete session tables for every conference as days vary and mess up my front end with session input forms. The other thought is that I will have to enter the dates, sessions and rooms for my folder structure anyway, so could this be used as a basis for a lookup? Please let me know if I'm over engineering this whole process.

[June7]

This type db is beyond my experience, however, it seems to me a date value would be essential to any search/filter functionality and the table does not show a date field.

[Carouser]

In my db, I am using day as the date which is a txt field. I will be migrating to date fields so that they will show up on reports, rather than just the day, otherwise date has no relevance other than separating the days of a 2+ day conference.

Streamlining the process of the initial setup in the admin stage is where my focus is. Each date has a relation to a separate block of data which could be separate session tables. These separate tables have links to one room table. This data will also be used for Making batch directories (another project once I get this right)



Above is relations ship table design, but not sure how to implement the connections or if it is the right process. The first Tbl_Day_Lookup entry would have a relation to Tbl_Session1_Lookup. The second Tbl_Day_Lookup entry would have a connection to Tble_Session2_lookup. I would then entere data into the Tbl_Conf table from which a filter for available lookups is created. I hope this make some kind of sense as Access speak and logic is not one of my strong points.

[jameslarsen]

I would suggest creating a series of lookup tables for entities like day, session, and room, and using a correlative table to create unique combinations. You will likely need to create an interface for managing these combinations (creating new ones, modifying existing ones, and deleting unwanted ones), unless you just want to do it manually on the backend. You could then use another correlative table to join a specific combination of day/session/rooms to specific speakers, presentations, etc. You could also store information such as Received, Seen, etc. in that table. I have mocked you up a quick ER diagram to help illustrate the idea. Hopefully this solution works for you, or at least gives you a general direction of thinking. Let me know if you need any more help with this, or need clarification on how to create this structure.



James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[Carouser]

Hi James. I am concentrating on the elusive multiple sessions lookup. Your diagram Session table has start and end times, whereas I only need a start time. This is a text field anyway, so do not need to make any calculations based on time, just a meaningful name place holder. 2 x session lookups was where I was heading.

I am also exploring combo boxes and how the row source from the first combo box can trigger different lookup in the second.

Link http://office.microsoft.com/en-us/ac...001173058.aspx

It's getting late here and all this reading and trying to understand concepts at my level is very tiring .

Cheers

[June7]

Here is another tutorial on dependent comboboxes http://datapigtechnologies.com/flash...combobox2.html

[jameslarsen]

Sorry, I might have over complicated it. I created the multiple times to give you more flexibility in creating your custom sessions. If you can explain to me the difference between Session 1 and Session 2, and why you think you need two different tables to hold them, I can try to explain to you what design will let you accomplish it. If possible, you should use a single session table, and just restrict the rows you return using a different query for each combo box.

As for having one selection from a combo box drive the available options in another, I have done that many times. You essentially have to create an "AfterUpdate" event on the first combo box to update the rowsource of the second box, then refresh the selection. I've never been a big fan of using bound columns in Access, and prefer to set the rowsource manually to have full control over what it's doing. I created a very simple database to demonstrate this concept. Take a look at it and let me know if it makes sense.

Multiple Combo Boxes.accdb

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[Carouser]

Hi James, you are right, I don't need 2 tables to hold what is effectively the same information. I was thinking about the admin setup which would have made it convenient to add or delete for specific sessions, but it will be more efficient to have one session table , and use queries with combo boxes in the admin stage all going to a combo table (just like you suggested) and that be used as a filter for the main forms for validated combinations. Because what am after is like a directory tree result which is why my mind has been clouded.

Below is my revised version of tables and relationships. I have Added another tble which is tbl_Combo_sessions



I will have a form to enter all Lookup table data for Day, Session and Rooms.

From queries I can assign the days and sessions only. A drop down box for the day, select day and enter all the sessions data for that day. Click the Day combo for the next day and enter sessions data for that day . etc. This will limit the list if I need to add or remove sessions (unless I create another form for that).

Rooms are added in combo_Main table that has link a to the Tbl_Combo_Session. This is where I would like to have a form with day combo box, and sessions combo box and enter all the room data.

From the different tables I can create my file directory for making parent directories and sub folders.

Now.........is this a viable option?

[jameslarsen]

I'm still not 100% clear on what you are trying to accomplish, so I don't know if this is a perfect solution, but give it a try, and see if you can make it work. If you run into trouble and you need the application to do something that this structure doesn't allow for, let me know, and I will see if I can offer some advice on how to change the model to accommodate the necessary functionality. Also, was the demo database I posted for changing the source of one combo box depending on the selection in another clear enough? Good luck!

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[Carouser]

Thanks James, the sample combo box will be very useful as was the video from Jun7. I have almost completed the final design for all tables that i need and how I want the db function. I Have grand plans for what is what is required for efficient setup, user navigation of the UI, and finally an ambitious amount of automation using macros. I am ok with SQL, but vba is very limited. You haven't heard the end of me.........

.Cheers