Ok, I'm feeling like a total idiot, because I cannot come up with a good structure for a fairly simple dataset. I'm using Access 2010, but the issue is normalization, and for some reason I just cannot wrap my head around this particular issue.
The database holds information about various conferences the company hosts. Each conference is identified by a number, we'll call Conf#, plus a Conference Name, Conference Location, Start Date and an End Date. Each conference uses a certain amount of Hotels, and works out a particular Rate with each one for the duration of the conference. The Hotels will Block a certain number of rooms for each day of the conference, and a certain amount of these are used, Pickups. So the data I need to track includes: Conf#, Start Date, End Date, Hotel Name, Rate, Blocks and Pickups. This, I can get. My problem is that I need to track Blocks and Pickups for each Hotel for each day for each conference. So Start and End Date are suddenly not enough, I need to extrapolate each individual day, so I can then use a query to calculate the totals for each day and each hotel.
So how can I properly create tables to allow for data to be tracked for each day? I could have done this for the totals until they told me they needed each day tracked.
I'd have one table with conference info (Conf#, Name, Location, Start & End Date), another with Hotel info (Hotel Name, Rate), then a third table with the Blocks and Pickups...but I cannot figure out how to account for the individual days. Do I make each individual date a value? Doesn't seem very efficient. I could make fields like "Day 1, Day 2" etc., but this is pretty bad too, and will result in nullible values since conference lengths vary. Am I making too much of this and missing a simple answer (I hope)? If anyone can help, point me to an example, anything I would appreciate it...I really want to understand this, I'm pretty new to databases and don't really have anyone here to bounce these things off of.
You need a separate table for conf# (FK1), HotelName (FK2), and a date. The valid dates, of course, would be between begindate and enddate. These fields would be the compound index. Besides these, you would need fields for total bookings and total $. If you can't get away with it, you will also need more details such as attendee name, etc., in which case you will be dealing with bookings/$ of an individual attendee. In any event, this will take care of the normalization issue.
Thanks Sam...I think that's where I was trying to go. So my next question, I've not used a compound index before (I'm pretty new)...I'm ok with making a table with the conference info, and one with the hotel info, but the dates are what is causing me the problem. This feels like an array, but I'm not really sure how to construct it...I have a table with the start and end dates of each conference, linked by conference number...I guess this is where the compound index comes in, but I'm not really getting how to apply it. I haven't found anything online that's really helpful here...could you explain a little more, or point me someplace that explains the idea?
Regarding the compound index, please peruse the help file concerning creating/modifying indexes. It's the best in the business.
Regarding the dates: now that's a different horse. The way I would do it is this. You will already have the begin and end date for each conference entered in one table, and the hotels locked in as well. When an attendee wants to book a hotel for a conference, you would enter the conference #, hotel #, and date. If you're also tracking attendee info, you would enter that as well.
How? Where does it all reside?
Make a form with two combo boxes and a text box, and additional text boxes as well, if you're tracking attendee details. Also, you'll need to have a text box to enter the first date booked by the attendee. (Whether you save that infomation depends on how detailed you want your db to be.) The form should be tied (the recordsource) to your final table. Both combo boxes should have 'table/query' row source types. The first combo box should have a row source query of all open conferences, meaning all conferences where the date(s) in question are between their begin and end dates. The second combo box should have a row source query of all hotels valid for the conference selected. The single text box can either be total $ or total nights, depending on how you want to handle it. The additional text boxes, of course, would be for entering the attendee information, if you want to save it.
Use the combo box wizard as a design tool; it's great. I'm deliberately not spoon-feeding the SQL code and the sequential design; there's no better way to learn something than needing to get it to work.