Page 4 of 7 FirstFirst 1234567 LastLast
Results 46 to 60 of 93
  1. #46
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901

    I would still recommend setting up a table that holds the same information as the four checkboxes on the on-line form that way if they change it, all you have to do is add new records (no change to your table structure)! You would still record the basic request of which group they want to visit and leave the details to the office staff

  2. #47
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    Well, I think I can sum this up in two very broad categories and split it up finer within each category....

    Here is what I am looking for as an end result:

    2 Forms, for database admin and database input (Bold = Form, Underline = Tab on a Form)

    Administration Form
    Day and Time
    On this tab, the user would be able to designate days of the week in which a tour can take place, and what times are available on that particular day. (My vision is 7 checkboxes, one for each day, and if it is checked, enable a text box where the user can type in a time, with formatting conditions so that all entries follow the XX:XX AM/PM format)

    Blackout Dates
    This tab would allow the user to load dates in which a campus tour will not take place. My vision for this is a series of maybe 5 radio buttons (one for 2011, 2012, and so on...) and depending on the selection, load up a single date picker. If that date picker has a date selected, add another blank date picker from the form, if that new date picker has a date selected, add another blank date picker.... and so on) Obviously every 5 years or so there would have to be some work done to this to get rid of old radio buttons and add new ones for new years and such.

    Tour Capacity
    This tab would allow users to view all available days and times a tour can be taken during the week. What I want here is a list of the days and times, with a text box where the user can put in a number, which would be the maximum amount of reservations that particular timeslot could accomodate.

    Athletic Schedule
    This tab would allow the user to update the athletic schedules for home games. The reason i want this is because when taking a tour reservation, it is often cumbersome to try to look up the current athletic schedule to see if there are home games for a certain date while taking the reservation. If possible, I would like this tab to be able to have radio buttons at the top (one for Hockey, another for Volleyball, etc) and depending on which one is selected, enable a date picker on the form. The user could then select a date that a home game is taking place, and once the date is selected, put another date picker on the form. Once the dates are in the database, I would like to use that informaiton to control the athletic ticket type drop down that I had on the old form. If there are no home games to give tickets away for, maybe replace that drop down with a label that says "No home game on the selected date". If there are home games, populate the drop down only with available sports that have home games.

    Reservation Form
    Tour Reservation
    This form would have fields pertaining to scheduling a tour reservation.

    Tour Setup
    This form would have fields pertaining to setting up the tour, handling faculty appointments, sending directions/confirmations, etc...

    Officer Setup
    This form would have fields for admissions officers to put information into the database based on information we curently have such as ACT scores, Student ID numbers, etc...

    Post-Tour Survey
    This form would have fields for the student to complete after taking a tour to evaluate certain aspects of the tour/tour guide.

    Tour Guide Assessment
    This form would have fields for the tour guide to complete after giving the tour, such as weather conditions, attitude of parents/students, campus activity, etc...

    This is what is currently planned for, and I am sure changes will be made along the way at some point, but most changes should be made and agreed upon soon...

    I am guessing i have most of the required tables to do most of this stuff, but i think i might have some that are missing still... especially in regards to the admin form...

  3. #48
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    By describing the forms, you are actually laying out additional requirements, so again, the design of the forms has to wait until you build the table structure that will accept the data from the forms.

    Here are some general suggestion regarding some of your comments:

    My vision is 7 checkboxes, one for each day, and if it is checked, enable a text box where the user can type in a time, with formatting
    It would be easier to just have a combo box to select from (you already have a table setup) and then you can use that to bring in the allowed times (already have a table for that as well) in another combo box for the person to select. You will be better off if the user selects from things you provide rather than relying on them to type things in correctly (opening yourself up to errors which will cause problems!)

    This tab would allow the user to load dates in which a campus tour will not take place. My vision for this is a series of maybe 5 radio buttons (one for 2011, 2012, and so on...) and depending on the selection, load up a single date picker.
    You'll need a table to hold the black out dates. I would not allow users to enter this, but rather import them directly all at once (a database admin function rather than a user function) And again cascading combo boxes might provide a nicer solution than the radio buttons. The first combo box would hold the years. You can use a query to extract the years from the blackout dates already stored in the table and use the query to populate the combo box. If you use radio buttons, how are you going to update the displayed years as time progresses?

    You would use a second combo box filtered by the first to show the blackout dates. Another option is to display all dates on which tours can take place (i.e. use a query that filters the blacked out dates from the list)

    This tab would allow users to view all available days and times a tour can be taken during the week. What I want here is a list of the days and times, with a text box where the user can put in a number, which would be the maximum amount of reservations that particular timeslot could accomodate
    You will need to set rules as to the max # of people on a tour and store that in a table (I'm guessing the tblTour). Is it something that can vary by tour? You'll have to decide. You can use a query to find out how many people are already signed up for a tour and then subtract that from the max allowed to get the # of available spaces.

    This tab would allow the user to update the athletic schedules for home games. The reason i want this is because when taking a tour reservation, it is often cumbersome to try to look up the current athletic schedule to see if there are home games for a certain date while taking the reservation
    You will have a table to hold the blackout dates, why not generalize the table to include all important dates. You can include a field in the table to delineate the date type. If you have the dates available in a spreadsheet, you can import them all at one time and relieve the user of the hassle of entering them (along with the potential of entering them incorrectly which might irritate a perspective student and their family).

    Officer Setup
    This form would have fields for admissions officers to put information into the database based on information we curently have such as ACT scores, Student ID numbers, etc...
    Isn't this information already in another data system (a secure one I hope)? I would not store it again in the Access database, but if the info is housed in a relational database such as SQL Server, Oracle or MySQL, you should be able to use an ODBC connection to have Access go out and retrieve the info and display it. Even in just displaying this type of sensitive data, you will have to build some very good security around your Access database!! I suggest you talk to your IT/security people to get their input.

    Tour Guide Assessment
    This form would have fields for the tour guide to complete after giving the tour, such as weather conditions, attitude of parents/students, campus activity, etc...
    You will need a junction table related to tblTour and to a table that will hold the items for which you want the evaluation.

    Post-Tour Survey
    This form would have fields for the student to complete after taking a tour to evaluate certain aspects of the tour/tour guide.
    Survey/questionnaire applications can take on a life of their own. You will need a table to hold all of the questions. You might want a way to categorize them (another table). You might also want a table to hold possible responses and a table to bring together the tour/person, questions and answers. You will also have to determine what type of answers you want; i.e yes/no type of short response etc.

    Still a lot of work for you to do on the structure...

  4. #49
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    I have attached a word document outlining what should be the final version of the database... i hope. This should give you a better understanding of what the database will include in the end product. now its just getting to the end of the tunnel...

  5. #50
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    so for starts, i think I am going to create two tables:

    tblDate
    -pkDateID
    -fkDateTypeID
    -dteDate

    tblDateType
    -pkDateTypeID
    -txtDateType

    would that be a correct assumption?

  6. #51
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    You will need to set rules as to the max # of people on a tour and store that in a table (I'm guessing the tblTour). Is it something that can vary by tour?
    For example:
    Monday @ 10:30 = 5 Max Cap.
    Monday @ 1:30 = 5 Max Cap.
    Tuesday @ 10:30 = 5 Max Cap.
    Tuesday @ 1:30 = 5 Max Cap.
    Wednesday @ 10:30 = 5 Max Cap.
    Wednesday @ 1:30 = 5 Max Cap.
    Thursday @ 10:30 = 5 Max Cap.
    Thursday @ 1:30 = 5 Max Cap.
    Friday @ 10:30 = 5 Max Cap.
    Friday @ 1:30 = 5 Max Cap.
    Saturday @ 1:30 = 10 Max Cap.

    where each of the timeslots above can have a different maximum capacity.

    In regards to the Officer Setup scenario, security protocol prevents connecting to the database outside of normal situations, and this is not one of them, however, a storage solution already exists to keep this information private. But because of protocol, we will have to look them up in our system, and enter the data in manually.

  7. #52
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    I am going out on a limb here and saying that I think i need a seperate table for each survey answer scale type:

    tblScaleNumeric
    -pkScaleNumericID
    -txtScaleNumeric (entries: N/A, 1,2,3,4,5)

    tblScaleChoice
    -pkScaleChoiceID
    -txtScaleChoice (entries: First Choice, Among Top Choices, Undecided, Not Interested)

    tblScaleMethod
    -pkScaleMethodID
    -txtScaleMethodID (entries: Email, Phone, Web, Did Not Schedule)

    tblScaleActivity
    -pkScaleActivityID
    -txtScaleActivity (entries: High, Average, Low)

    tblScaleWeather
    -pkScaleWeatherID
    -txtScaleWeatherID (entries: Pleasant, Inclement, Unsure)

    tblScaleQuestions
    -pkScaleQuestionsID
    -txtScaleQuestionsID (entries: Asked Many Questions, Asked Questions, Asked Few Questions, Asked No Questions)

    tblScaleExcitement
    -pkScaleExcitement
    -txtScaleExcitement (entries: Very Excited, Enthusiastic, Unhappy, Unsure)

    tblScaleAttendance
    -pkScaleAttendance
    -txtScaleAttendance (entries: Will Likely Attend, May Attend, Won't Attend, Unsure)

    tblScaleSatisfaction
    -pkScaleSatisfactionID
    -txtScaleSatisfaction (entries: Very Satisfied, Satisfied, Unsure, Unsatisfied, Very Unsatisfied)

    My guess is that the above is only a start, given that what I have is even correct...

  8. #53
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    tblDate
    -pkDateID
    -fkDateTypeID
    -dteDate

    tblDateType
    -pkDateTypeID
    -txtDateType
    The above look OK.

    Monday @ 10:30 = 5 Max Cap.
    Monday @ 1:30 = 5 Max Cap.
    Tuesday @ 10:30 = 5 Max Cap.
    Tuesday @ 1:30 = 5 Max Cap.
    Wednesday @ 10:30 = 5 Max Cap.
    Wednesday @ 1:30 = 5 Max Cap.
    Thursday @ 10:30 = 5 Max Cap.
    Thursday @ 1:30 = 5 Max Cap.
    Friday @ 10:30 = 5 Max Cap.
    Friday @ 1:30 = 5 Max Cap.
    Saturday @ 1:30 = 10 Max Cap.
    You already have a table that joins the tour days with the tour times, that is where the MaxCap field should go.

    In regards to the Officer Setup scenario, security protocol prevents connecting to the database outside of normal situations, and this is not one of them, however, a storage solution already exists to keep this information private. But because of protocol, we will have to look them up in our system, and enter the data in manually.
    Does the act of re-entering the data in another system (yours) cause a security concern?


    tblScaleNumeric
    -pkScaleNumericID
    -txtScaleNumeric (entries: N/A, 1,2,3,4,5)

    tblScaleChoice
    -pkScaleChoiceID
    -txtScaleChoice (entries: First Choice, Among Top Choices, Undecided, Not Interested)

    tblScaleMethod
    -pkScaleMethodID
    -txtScaleMethodID (entries: Email, Phone, Web, Did Not Schedule)

    tblScaleActivity
    -pkScaleActivityID
    -txtScaleActivity (entries: High, Average, Low)

    tblScaleWeather
    -pkScaleWeatherID
    -txtScaleWeatherID (entries: Pleasant, Inclement, Unsure)

    tblScaleQuestions
    -pkScaleQuestionsID
    -txtScaleQuestionsID (entries: Asked Many Questions, Asked Questions, Asked Few Questions, Asked No Questions)

    tblScaleExcitement
    -pkScaleExcitement
    -txtScaleExcitement (entries: Very Excited, Enthusiastic, Unhappy, Unsure)

    tblScaleAttendance
    -pkScaleAttendance
    -txtScaleAttendance (entries: Will Likely Attend, May Attend, Won't Attend, Unsure)

    tblScaleSatisfaction
    -pkScaleSatisfactionID
    -txtScaleSatisfaction (entries: Very Satisfied, Satisfied, Unsure, Unsatisfied, Very Unsatisfied)
    I would suggest 2 tables

    tblScales
    -pkScaleID primary key, autonumber
    -txtScale
    -fkScaleCatID foreign key to tblScaleCategory

    txtScale would hold the various choices as records (Very Satisfied, Satisfied, Unsure, Unsatisfied, Very Unsatisfied, Will Likely Attend, May Attend, Won't Attend, Unsure, Very Excited, Enthusiastic, Unhappy, Unsure etc.)

    tblScaleCategory
    -pkScaleCatID primary key, autonumber
    -txtScaleCategoryName

    txtScaleCategoryname would hold the following as records
    Method
    Weather
    Attendance
    Satisfaction
    Choice
    Numeric
    etc.

  9. #54
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    You already have a table that joins the tour days with the tour times, that is where the MaxCap field should go.

    So...

    tblTourDayTimes
    -pkTourDayTimesID
    -fkTourDaysID
    -fkTourTimesID
    -txtTourDayTimeCap

    Does the act of re-entering the data in another system (yours) cause a security concern?

    I believe it is the connection itself... not sure why, but it is a no go.

    tblScales
    -pkScaleID primary key, autonumber
    -txtScale
    -fkScaleCatID foreign key to tblScaleCategory

    txtScale would hold the various choices as records (Very Satisfied, Satisfied, Unsure, Unsatisfied, Very Unsatisfied, Will Likely Attend, May Attend, Won't Attend, Unsure, Very Excited, Enthusiastic, Unhappy, Unsure etc.)

    tblScaleCategory
    -pkScaleCatID primary key, autonumber
    -txtScaleCategoryName
    I will add these. Were you able to understand that word doc I posted? I looked through it, and I think that after setting up these last tables, I think I only have a couple more to go... I will have to go through it really good to make sure though...

  10. #55
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    -txtTourDayTimeCap
    I would probably use a long number field (longTourDayTimeCap) since you will be storing strictly numeric data rather than a text (txt..) field.

    I initially missed your post with the Word document. I appears you have most things covered except a table to hold the survey questions with their respective answers (a junction table would be in order).

  11. #56
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    As I looked through the proposed form, I noticed I might need the following:

    tblStProv for listing states and provinces
    -pkStProvID
    -txtStProv

    tblStatus for listing high school student status (ie freshman, soph, jun, sen, graduate)
    -pkStatusID
    -txtStatus

    tblEntryTerms for listing entry terms like Fall 2012, Spring 2013, etc)
    -pkEntryTermsID
    -txtEntryTerms

    tblDegreeType for listing degree type, Masters, Bachelors, Associates
    -pkDegreeTypeID
    -txtDegreeType

    tblDegreeProgram for listing degree programs, ie nursing, engineering, law
    -pkDegreeProgramID
    -txtDegreeProgram

    tblDegreeProgramType
    going out on a limb with this one, i think this will be necessary to match degree programs with degree types, such as computer science is offered as a Bachelors, Nursing is offered as Bachelors, Associates, Masters, etc...

    tblResTaken for listing methods/people taking reservations
    -tblResTakenID
    -txtResTaken

    tblMeritAward for listing merit scholarship amounts
    -tblMeritAwardID
    -currMeritAward

    Do you see anything out of whack with what I have up there???

  12. #57
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    It all looks OK and yes, you will need a junction table to relate the applicable degrees to a program

  13. #58
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    I appears you have most things covered except a table to hold the survey questions with their respective answers (a junction table would be in order).
    I thought tblScales and tblScaleCategory took care of the answers? I know from this now that I need

    tblSurveyQuestion
    -pkSurveyQuestionID
    -txtSurveyQuestion

    I think I understand what a Junction table is for, but to set one up, I am still kinda fuzzy...

    It all looks OK and yes, you will need a junction table to relate the applicable degrees to a program
    Again, the junction table got me...

  14. #59
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The scales table holds the list of possible answers, you have to associate the person (responding to the survery), the question (from tblSurveyQuestion) and their particular answer to each question.

    I think I understand what a Junction table is for, but to set one up, I am still kinda fuzzy...
    Here is a simple example:

    Let's say you have a table that holds a list of people and another that holds a lists of possible hobbies. A person can have many hobbies (one(person)-to-many(hobbies) relationship), and a particular hobby can be enjoyed by many people (a one(hobby)-to-many(people) relationship). When you have two one-to-many relationships between the same two tables, you need the junction table to represent the possible associations between people and hobbies

    tblPeopleHobbies
    -pkPeoHobID primary key, autonumber
    -fkPeopleID foreign key to tblPeople
    -fkHobbyID foreign key to tblHobbies


    In your case, you will ask a tour participant many survey questions (one person -to- many questions) and you will ask the same question of many tour participants (one question to many people).

    tblTourPeopleQuestions
    -pkTourPeopleQuesID primary key, autonumber
    -fkTourPeopleID foreign key to tblTourPeople
    -fkQuestionID foreign key to tblQuestions

    You will also need a table that associates a question with its appropriate possible answers (from the scales table)

    You will then need a table to hold the actual answers of the participants.

  15. #60
    nchesebro's Avatar
    nchesebro is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2010
    Posts
    108
    So I am looking at...

    tblQuestions
    -pkQuestionsID
    -txtQuestions

    tblTourPeopleQuestions
    -pkTourPeopleQuestionsID
    -fkTourPeopleID
    -fkQuestionID

    tblTourPeopleAnswers
    -pkTourPeopleAnswersID
    -???

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

    As you can see, I have two areas in the 4 proposed tables where I have questions regarding how they should be set up...

Page 4 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date Picker?
    By gazzieh in forum Forms
    Replies: 9
    Last Post: 02-09-2013, 09:35 AM
  2. Date Picker doesn't appear
    By revnice in forum Access
    Replies: 9
    Last Post: 01-09-2012, 08:36 AM
  3. date picker activex control
    By mr2000 in forum Forms
    Replies: 1
    Last Post: 10-13-2010, 09:51 AM
  4. Date picker
    By BI4K12 in forum Access
    Replies: 1
    Last Post: 06-09-2010, 11:11 AM
  5. Microsoft Date and Time Picker 6.0
    By That Crazy Hockey Dood in forum Forms
    Replies: 0
    Last Post: 07-25-2007, 03:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums