Populate Day and allowable times based on date picker

[jzwp11]

tblTourPeopleAnswers
-pkTourPeopleAnswersID
-???

tblQuestionScales
-pkQuestionScales
-fkScalesID???????

Something like this:

tblTourPeopleAnswers
-pkTourPeopleAnswersID
-fkTourPeopleQuestionsID
-fkScalesID (the actual answer)

tblQuestionScales
-pkQuestionScales
-fkQuestionID
-fkScalesID

The tblQuestionScales will allow you to use cascading combo boxes on a form. When someone selects a question, the appropriate allowable responses will be displayed for that question.

[nchesebro]

Ok...

Do I Dare say, that we are done creating new tables? I went through the relationships pane and made the relationships with the tables. I have not yet populated the tables with all the information yet, that is what I will begin working on now.

I also looked at that sample form in that word document referenced earlier and compared it to the tables, and it appears to me that all the tables are present to accomodate what needs to be done.

I could be wrong about this, so would you please take a look at the proposed form versus the tables and relationships to see if what we have now is what we need, and we arent missing anything? Thanks!

[jzwp11]

It looks like you have things basically covered with respect to the data you want to capture via your forms, you might need a few fields here and there which you will find as you start entering test data.

You are missing some relationships in the relationship window. I created the relationships between the degree/program tables in the attached, but I assume that the degree/program also has to relate back to the student as well as the other tables that are not yet joined back.

[nchesebro]

the degree program would make sense to relate back, but to which table i am not sure...

My GUESS is that they should be related in some way to the tblPeople, and also, the tables tblentryterms, tblMeritAward, tblStatus, tblStProv, and tblDates should all relate back to either tblPeople or tblTour... but that is a guess, and i am looking at the relationships in the database you provided, and I am not sure where to begin the process of finalizing all the relationships...

When you say entering test data, i assume that means within the tables, correct? Not a form yet?

[jzwp11]

the degree program would make sense to relate back, but to which table i am not sure...

Since you want to capture the degree program the student is interested in and the degree program is independent of the tour, then it should be related back directly to the student, so tblPeople would seem the most appropriate. I would also guess that these: tblentryterms, tblMeritAward, tblStatus, tblStProv should also related back to the person.

As to tblDates, I think it should be tied to tblTickets assuming that you have athletic schedules included in it and the person is requesting a specific date for the ticket/event.

[nchesebro]

Since you want to capture the degree program the student is interested in and the degree program is independent of the tour, then it should be related back directly to the student, so tblPeople would seem the most appropriate. I would also guess that these: tblentryterms, tblMeritAward, tblStatus, tblStProv should also related back to the person.

So I would create a fk in the tblPeople for each of the tables listed?
As to tblDates, I think it should be tied to tblTickets assuming that you have athletic schedules included in it and the person is requesting a specific date for the ticket/event.

There are currently the following types of dates:
Blackout Dates in which tours are not given, denoted as Blackout, Holiday, or Orientation, and dates in which tickets are available for Hockey, Mens BB, Womens BB, Track, and so on. I want this feature to work in the following manner:

When they reserve a tour, the form should check to see first that the date is not a Blackout, Holiday or Orientation. If it is, I would like to have a message box pop up saying "This date is a (insert date type here) date. Please select another date for this tour." If the date is not one of those, the form should check to see what sport events, if any, are going to be taking place on that date. If dates are available, in the area where the inputter would check a box asking if they would like sporting tickets, there will also be a drop down menu that is enabled if that checkbox is in fact checked. That drop down should be populated with the sporting events where tickets are available for the slected tour date. If no sports are available, when the checkbox is checked, a message box should pop up saying "No sports available for this date, and automatically uncheck that box, that way the office secretary is not sending memos to the athletic department requesting tickets that are just not available.

feedback? am I out of my mind? or will this take place in the form building process of this?

[nchesebro]

ok... i am filling in information into the table right now, and i have more left to do, but this is the most current form of what I have.

[nchesebro]

and here is another question, might sound extremeley stupid, but normalization is new, and i dont want to plug things into where they dont belong, but where do you store info like, first name, last name, address, etc... My guess would be tblPeople, but again, that is just a guess

one thing is for sure however, = , = = . no wonder coffee was invented before the database.

[nchesebro]

Ok...

I now have zip codes. In light of efficiency, I would like to be able to have inputters just go right to the zip code field and type in the zip code, and based on that input, have the city and state populate based on the 5 didgit zip code. i have my "db in the works, and the zip code database" attached, and I was wondering if there was a way to do this.... i will see if i can find one for canada as well and add them to this.

[nchesebro]

crap... here is the attachment... sorry...

[jzwp11]

...feedback? am I out of my mind? or will this take place in the form building process of this?

The various checks you mention would take place at the form level.

and here is another question, might sound extremeley stupid, but normalization is new, and i dont want to plug things into where they dont belong, but where do you store info like, first name, last name, address, etc... My guess would be tblPeople

Yes, tblPeople

In light of efficiency, I would like to be able to have inputters just go right to the zip code field and type in the zip code, and based on that input, have the city and state populate based on the 5 didgit zip code. i have my "db in the works, and the zip code database" attached, and I was wondering if there was a way to do this.... i will see if i can find one for canada as well and add them to this.

I would give the users a combo box with zip codes and they can type to filter down to the one they need. You would actually store the foreign key to the zip code (edit: I see the zip code table does not have a primary key, so you could then just store the zip code itself). You would not need to store the city or the state if they are properly linked to the zip code.

[nchesebro]

I want to put current school, term of entry, curriculum 1 and 2 (although that would not be proper normalization, I need to figure out how that will be done) would also be in tblPeople?

[nchesebro]

and also things like their student ID number, act scores, scholarships, interests, contact codes, how they learned about us?

[jzwp11]

I want to put current school, term of entry, curriculum 1 and 2 (although that would not be proper normalization, I need to figure out how that will be done) would also be in tblPeople?

...and also things like their student ID number, act scores, scholarships, interests, contact codes, how they learned about us?

In general, single items would be in tblPeople, but when you are talking about multiple items such as test scores, interests, contact codes etc. each of those describe one-to-many relationships which require related tables.

[nchesebro]

for example,

tblACTComposite
-pkACTCompositeID
-longACTComposite

tblPeople
-blah
-blah
-blah
-fkACTCompositeID

and repeat for the items listed above?