Results 1 to 11 of 11
  1. #1
    Miche311 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    6

    Probably a Newb Question but...

    I'm creating a form for the assistant principal at my high school to look up students by periods and day (we have Day 1 and Day 2). She provided me with the database that contains:

    Student first name
    Student last name
    Day 1 teacher
    Day 2 teacher


    Period the student has lunch
    Comment section

    I am tasked with creating a form where she can either look up the day and the information above will return with records of these students so she can locate them and make comments if they're in attendance or not. One issue I'm having is that the fields labeled Day 1 and Day 2 only list teachers names not a number of the Day 1 or Day 2.

    Is there a DLookup statement I can create where when she types in the number 1 for Day 1 it'll look for the query labeled Day 1 with the teachers names and student info or do I have to add a field called Day in the database and have it lookup the information with that? Maybe this would be better as an If statement, like if she types 1 for the day the fields for a query will be called up. Can one write a statement that pulls up field data from a query based upon something that is typed in a text field and not actually data from the database?


    Thank you!

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    At first blush, at least with the info you have provided, it appears that the data is not normalized.
    At the very least there should be a table of students, a table of teachers, and a schedule table.

  3. #3
    Miche311 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    6
    Thanks for the quick response! Everything is on one big Excel spreadsheet that I'm bringing into access. I'll split them up and create a primary key. Thank you!

    Quote Originally Posted by moke123 View Post
    At first blush, at least with the info you have provided, it appears that the data is not normalized.
    At the very least there should be a table of students, a table of teachers, and a schedule table.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    And what if the search should consider both Day fields at same time?

    I do agree that you should have at least 3 tables if each student can have multiple schedules. Each day can be viewed as a separate schedule and then each student would have 2 records in Schedules. StudentID and TeacherID will be saved into Schedules, not full names. However, if you keep the 2 Day fields then only 2 tables is needed.

    If you use 3 tables, which table should the LunchPeriod and Comments fields go in? If the lunch period will never be different for each day, put it in Students table. A Comments field can be in every table regardless of structure.

    The two Day fields is not a normalized structure, however, it can be made to work. I expect a dynamic parameterized query could be structured to do what you describe. However, I recommend building a 'search' form and using VBA to construct search criteria and apply to form or report. Review http://allenbrowne.com/ser-62.html

    Do you care about history? Or does this db hold only current period info?

    It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts, denormalize until it works.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Miche311 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    6
    Quote Originally Posted by June7 View Post
    And what if the search should consider both Day fields at same time?

    I do agree that you should have at least 3 tables if each student can have multiple schedules. Each day can be viewed as a separate schedule and then each student would have 2 records in Schedules. StudentID and TeacherID will be saved into Schedules, not full names. However, if you keep the 2 Day fields then only 2 tables is needed.

    If you use 3 tables, which table should the LunchPeriod and Comments fields go in? If the lunch period will never be different for each day, put it in Students table. A Comments field can be in every table regardless of structure.

    The two Day fields is not a normalized structure, however, it can be made to work. I expect a dynamic parameterized query could be structured to do what you describe. However, I recommend building a 'search' form and using VBA to construct search criteria and apply to form or report. Review http://allenbrowne.com/ser-62.html

    Do you care about history? Or does this db hold only current period info?

    It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts, denormalize until it works.
    I'll try this now, thank you for the suggestion! Let me play with this first and then I'll come back and answer your questions.

  6. #6
    Miche311 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    6
    Ok, here is a question. I'm stuck and cannot figure it out. If I created a choice list that displays the options of either Day 1 and Day 2, how to I link those options with the two separate tables? Is there an IIF than statement I can write or is this done with a DLookup and if so how/where do I type this in? One table is called Day 1 and the other is Day 1. Thank you in advance.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, there should not be two tables Day1 and Day2. You either have two fields Day1 and Day2 or you have a record for each day entry in one table. In this case fields needed would be: TeacherID, StudentID, DayNumber.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Miche311 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    6
    Ok, fixing it now, thanks for the quick reply!

  9. #9
    Miche311 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2019
    Posts
    6
    Alright, there are two fields, Day 1 and Day 2 respectively. How do I connect the control box so my administrator can choose either Day 1 or Day 2 to pull up the specific information?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    All the combobox does is list the teachers from Teachers table.

    Have another control such as 2 radio buttons within an option group control. A radio button for each day. Selected radio button determines which day to search.

    Conditional VBA code constructs filter criteria based on selections. Review Allen Browne link in depth and modify as needed for your situation.

    Just hit me you said 'periods and day' but original table structure does not have a field for PeriodNum. If day is broken into class periods and you want to list all periods/teachers for each student, then you definitely need 3 tables. And lunch period would not be a separate field in Schedule, lunch is just another period in the day so use "lunch" for the 'teacher'. So do you need another field for PeriodNum?
    Last edited by June7; 01-06-2019 at 12:48 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Its been a long time since I was in High school and your description was a little confusing.
    In my day we had 8 periods to a school day and one set of classes on monday, wednesday and fridays (Day 1?) and another set of classes on tuesdays and thursdays (Day 2?).
    is this similiar to what your schedules are?

    It may be helpful if you posted a sample of what you have so far and describe the school days in a little more detail.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Newb Question
    By Calluna in forum Access
    Replies: 6
    Last Post: 04-04-2014, 12:29 PM
  2. Newb Question
    By Jesse_Munos in forum Import/Export Data
    Replies: 11
    Last Post: 05-12-2012, 03:33 PM
  3. Newb question
    By blkdragon201 in forum Access
    Replies: 5
    Last Post: 02-03-2011, 02:17 PM
  4. This is probally a newb question
    By tdanko128 in forum Queries
    Replies: 4
    Last Post: 01-30-2011, 12:24 PM
  5. Newb Question
    By smokeyvol in forum Access
    Replies: 0
    Last Post: 01-14-2009, 08:28 AM

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