Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Nathanlceh is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2019
    Location
    Montana
    Posts
    9

    Am I missing something...query will not populate that includes a junction table

    Hi all,


    I am new to the site, and would be considered more of the Newb status when it comes to Access. Please bare with me if I don't have all the verbiage down.
    I seem to be having an issue with a particular situation within a few work databases I'm creating. To be specific of the issue, when ever I have a M:M relationship that requires a junction table, my queries will not populate and my subforms will not interact.

    To add some context,
    I have a tabbed form containing subforms for data entry, of which, there are 3 1:M relationships with one having an additional M:M relationship. In particular, the M:M relationship consists of
    the tables, "EstablishmentT", "EstablishmentOwnerT", and "OwnerInfoT". The junction table is the EstablishmentOnwer containing 2 FK's linked to each of the adjacent tables PK's. Both the tables EstablishmentT and OwnerInfoT have Autonumber PK's with the junction table having long interger number FK's.

    The query that I setup relating the 3 tables is Estab_OwnerSubQuery and is setup with the default inner joins. When I run the query as is, it doesn't populate. I've tried creating various forms, but ultimately came down to the query not working. For my sanity, I cannot figure out what is wrong, and would really appreciate some help on the issue.
    I have a feeling that I'm missing something within the 3 tables relationships or in the query design.

    As my main goal, I want to be able to enter the data through the main tabbed form.
    Any help would be appreciated. If I'm not clear enough, please bare with me.
    I have attached my database for your eyes.
    Attached Files Attached Files

  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,815
    Generally, a form should edit only 1 table. Conventional options for M:M relationship:

    1. one form bound to junction table with comboboxes to select from the related tables

    2. main form bound to one table with subform bound to junction table and combobox to select from another table

    3. reverse the main form and combobox sources

    Recommend you set each form RecordSource to a table instead of query with joins. There should be no need for LicenseT to be included in subform RecordSource. An INNER JOIN requires related records in BOTH tables for data to display.

    LicenseT table has an autonumber field yet you have designated LNID as primary key. Is this text value what you really want to base key relationship on? If so, why have the autonumber field? And why format it to show with an alpha character? Many would argue against using text fields as PK/FK.

    A record entry/edit is committed to table when: 1) close table/query/form; or 2) move to another record; or 3) run code to save. The Save button might not be needed or might want to use different code, such as moving to new record row.
    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
    Nathanlceh is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2019
    Location
    Montana
    Posts
    9
    June7 I appreciate the input. I have watched some tutorials on using the comboboxes. Could that be the area i'm overseeing ?!?

    If/when you or anyone else glance at the db, please note that a decent percentage of the form structure is setup, with the missing piece(s) being the M:M issue.
    Thanks,

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Nathan,

    You say you are new and also that you have created many forms [a decent percentage of the form structure is setup].
    But what exactly is the business-- often good to start with a clear description of the "business " you are trying to support with automation/database.
    I see Inspector, License, Contact, Establishment and Owner. So is this about Licensed Inspectors Inspecting Establishments, just trying to clarify, how the pieces fit together.
    Good luck.
    Last edited by orange; 01-08-2019 at 06:27 AM. Reason: spelling

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You may have relationship between LicenseT and EstablishmentT backwards. A license can associate with only 1 establishment but I assume an establishment can have multiple licenses. That means saving EstablishmentID into LicenseT.

    Before you commit to using multi-value fields, you should fully understand how they work. https://support.office.com/en-us/art...C-6DE9BEBBEC31 I NEVER use multi-value field. I also never setup tables with alias field names, field format, lookup fields. These mask the true nature of the db structure. As developer, I prefer to see the real field names and real stored value, not the descriptive alias nor formatted data. These settings only assist with creating form and controls, but after form is built, those settings are just an annoyance when you (developer) want to view table. Users should not work directly with tables and queries so these table settings mean nothing to them.

    I may have edited my earlier post after you read so might look at again.
    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.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's a simple Events/Members M:M DB to demonstrate how to link and populate the junction table.
    Includes a query to concatenate First and Last names, and a couple of reports with their recordsource queries to show how to extract the data.
    Perhaps it can serve as a useful guideline for your efforts.
    mtm with subform-davegri-v02.zip

  7. #7
    Nathanlceh is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2019
    Location
    Montana
    Posts
    9
    Orange,
    I work for my local county as a Sanitarian (food/septic/public water inspector) and we are in the process of switching our current databases over to Access. All our old records are in excel and have become to burdensome to sort and "query".
    How weird it may sound, our local IT department knows absolute nothing about Access, so I basically am self taught. I have approached my department with Access and everyone is all for it, being that I have to create everything from scrap.

    This particular db is for our licensed establishments i.e. vacation rentals/hotels/motels/pools/food establishments... I am trying to create a db that is very user friendly in relation to co-workers and office admin. I want to have a general form that each person can maneuver through that would offer a consistent data entry for all establishments.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Nathan,

    I don't mean to discourage your intent, but I have a feeling you are too focused on forms and multi-valued fields.

    I suggest you spend about 45-60 minutes working through this tutorial from RogersAccessLibrary.
    Once you have experienced the process by working through the example, you can do the same for your proposed database, and any other database.

    Here is a list you can reference in future.
    It might be helpful to readers if you could post some samples of the Excel data.

    Good luck with your project.

  9. #9
    Nathanlceh is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2019
    Location
    Montana
    Posts
    9
    I really appreciate each of your responses,

    June7, the crazy thing is that licenses are tied to location and owner. There are situations where an owner (single or many) can have two establishments on the same property under one license....yeah...I toyed with the idea of having the License Number or an Autonumber be the primary key..... Is there a better benefit for an autonumber? I figured for those rare occasions where the license number is the same for 2 establishments, we could use the license number with the abbreviation of the name of the establishment....

    Concerning multi-valued fields, what alternative would be best? For those specific fields, there could be many selections?

    Davegri, Thanks for the guide!

    Orange, our excel db is very rudimentary, there is a lot of information that is not even existent compared to the Access db I want to have. We've noticed that much of the "missing" data in excel is really needed. We are pretty much overhauling our record process, by starting anew with Access.

    I've also spent a considerable amount of time breaking down the data into tables. Any additional comment is appreciative if I've completely missed something.

    I'm wondering if I have bitten more off than I can handle with my limited Access knowledge.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Nathan,

    I appreciate your situation. I just want to emphasize that 1 hour with that tutorial will teach you more about database design than all the reading and messing with Access. Perhaps the best use of 1 hour of your time. Knowledge of Access is not your immediate issue-- you need to be familiar with some database concepts; get your requirements identified clearly; break out your entities, attributes and relationships(paper based model is more than adequate); create some sample data and scenarios; test your model; revise as necessary and retest; once it's working -use that as a blueprint for your database design. Now is the time for Access.

    Anyway, you know where I stand. When you think you may have bitten off more than you can handle-- break it into "do-able chunks" and carry on.

    Good luck with your project.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, sorry for making erroneous assumption. So if license can associate with multiple establishments and establishment can have multiple licenses, sounds like a M:M relationship, which calls for a junction table. Or if you prefer to extend the license number with a suffix and if each establishment can have multiple licenses, this still means saving the EstablishmentID into LicenseT, and duplicating that license info. If establishment will have only one license, then go ahead and save LicenseID into EstablishmentT, no suffix needed.
    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.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    LCEH Establishment-davegri-v01.zip
    See attached. This DB addresses the establishment/owner M:M. See the revised Establishment Information tab in the ** All Entry Form **.
    Also added Owner Form to Owner Information tab.

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Here is an example mostly based on your own.

    Edit: Meanwhile there was post about license linked to more than one estabilishment - I haven't taken this into account.

    Does it mean, that when owner has several estabilishments, then owner needs only one licence? When yes, then you have to link licence with owner instead!
    Attached Files Attached Files

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe link to owner(s). What if owner(s) sells establishment - does the license go with the establishment?

    Don't think it odd that IT staff don't know about Access. It's been my experience that network techs and managers often don't understand databases.
    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.

  15. #15
    Nathanlceh is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2019
    Location
    Montana
    Posts
    9
    Orange, I appreciate your help. I will carve out some time to get solid on database design.

    June7, Davegri, and ArviLaanemets...... I will look into your suggestions...wont have much time to invest over the next week or so.

    You guys have already helped a self taught learner a bunch!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-25-2018, 04:07 PM
  2. Auto Populate new record in Junction Table.
    By Deepak.Doddagoudar in forum Forms
    Replies: 6
    Last Post: 03-31-2018, 12:32 PM
  3. Populate Junction Table related to 3 Tables
    By Daoud1987 in forum Access
    Replies: 5
    Last Post: 12-11-2013, 12:13 PM
  4. Replies: 1
    Last Post: 07-14-2012, 01:37 AM
  5. How to use Junction Table to populate DB
    By Sorbz62 in forum Forms
    Replies: 1
    Last Post: 10-02-2011, 05:45 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