Results 1 to 9 of 9
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305

    Proper form creation for junction table

    So I have a junction table (tblJuncEmpLocApptPref) that is reference three separate tables:



    - tblEmployees
    - tblLocations
    - tblApptType

    I'm asking for thoughts on the best form creation in order to add, edit and view entries, and the corresponding method of creating said form.

    Basically the idea behind the database is that users would select a location, then select an employee who works at the location, then select the appt types that this employee can handle, and lastly in the form will be a preferences text box that shows the employees' preferences for that given appt. (how long they want the appt set up for, which days, etc.) This preference box references back to a field in the junction table.

    My initial thought is a main form for the location, then a sub form for the employees, and another subform for the appt type. The issue is that I have no idea how to create that intricate of a form nor do I know if its the ideal solution.

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So these are 3 lookup tables - tblEmployees lists all employees, tblLocations lists all locations, tblApptType lists all types of appointments?

    Now you need another table for records to associate these data elements?

    Do you want combinations of employee/location/appointment? Probably need another table: Emp_Loc_Appt
    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.

  3. #3
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    I already have a junction table that correlates which employees work at which locations and perform which appts. My need now is to create a form that:
    - can view this info
    - edit this info (add in "preference" info for a particular appt)
    - add future info (i.e. new employee)

    The idea way to design it in my head is to select a location > select the employee > select the appts that this employee can do > which shows the preference for that appt. The issue is I've never designed a form that intricate and don't even know where to begin.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No junction table shown in OP.

    Not clear to me what you want to accomplish. Do you want to prioritize employee choices? Is there a field in this table for Priority? Assign a number that will order the employee's options.

    Do you want to search this junction table to locate employees that meet the parameters of a given location and appointment type?

    If you just want to do simple data entry into junction table, a single form with 3 comboboxes can accomplish that.

    Form/subform option could be main form bound to Employees, subform bound to junction table with comboboxes to select location and appointment. Or different combinations for main form and comboboxes but the junction table would always be subform.
    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
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    305
    In the first line of my OP, I stated that I have a junction table.

    I guess I'll try to explain better and more straight forward. This database is for people who schedule appointments; they do not schedule them within this database. This DB is only to view the preferences for a multitude of different employees, locations and appt types. I have already built the three tables and the junction table that brings them together.

    - tblEmployees
    - tblLocations
    - tblApptType
    - tblJuncEmpLocApptPref

    I now need to make a form that the schedulers will use to view this information (and for me to occasionally update the preference info). In this form, I'd like to be able to select the location > employee > appt type > which populates a text box for the "appt preferences". This preferences text box is from an empty field in the junction table

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    And so you did.

    Still not clear to me what the issue is. If you want a search utility, applying filter criteria for employee/location/appointment parameters is simple enough. Review http://allenbrowne.com/ser-62.html
    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.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe something like this????
    Attached Files Attached Files

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    With all due respect, I didn't download the db as the OP says their desire is not to actually schedule, but to view preferences and I simply want to offer a comment.
    Can't really tell from what was revealed as to whether or not table setup is correct, but this will likely be a case where the form is based on a query and not a table.
    So the advice is to forget about the form for now. If you can't build a query to draw in the data, then having the form first is of no help.
    Since it's not clear (at least to me) if there are any one-to-many relationships involved, it's impossible to say if any subforms are required. If not, then everything can be on one form.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What I read was that the OP wanted to be able to look at
    1) a name to see the locations the person and the appts types were available or
    2) look at a location to see the name and appts or
    3) look at an appt type to see the locations and people available.


    There is a main table (junction?) with 3 FK fields (person, location and appts look up tables) and a text box to record preferences.
    So 1 main form with 3 combo boxes for set up.data entry
    and 3 forms with sub forms to select name,location OR appt types.

    I didn't add the 3 forms required to add/edit the names, locations and appt types.

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

Similar Threads

  1. How to use a Junction table
    By gwboolean in forum Access
    Replies: 17
    Last Post: 09-15-2017, 03:06 PM
  2. Replies: 13
    Last Post: 05-10-2017, 12:43 AM
  3. Proper Primary Key Creation in Access Table
    By Tambe257 in forum Database Design
    Replies: 4
    Last Post: 03-05-2017, 09:01 AM
  4. Replies: 8
    Last Post: 07-18-2013, 01:52 PM
  5. Junction Table
    By mae3n in forum Database Design
    Replies: 2
    Last Post: 01-15-2011, 10:23 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