Results 1 to 6 of 6

Joining tables in Access Web Apps

  1. #1
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    31

    Joining tables in Access Web Apps

    Well, I've been tinkering with this problem for a while now, but I can't figure out what I'm doing wrong, so I hope someone brighter than I am can steer me in the right direction. And by "steer," I mean full on GPS navigation to the end result! LOL

    I'm building my first Access web app. It has three major tables: Households, Resources, and Skillsets. The household table contains names and addresses, etc. The Resources table contains things owned by the household, and the Skillsets table contains things the people in the household are capable of doing. Each table has roughly 400 or so records, neatly joined together and working properly (imported from an access database). If I look at the skillset list view, for example, I see a list of the household names that I can filter, and selecting any existing name shows me the skillsets associated with that name. Ditto with the Resources list view. I can edit any of the households, resources or skillsets, and all the changes stick appropriately.

    However, I cannot figure out how to create a functioning NEW household record. I mean, I can create the new household record just fine! But when I go to the Skillset or Resource table list view, I cannot see the newly created household to assign skills or resources. I'm sure this is pretty lame (my access database works perfectly, but man oh man I'm having a devil of a time with the web app!

    Here's what I've tried:
    • Adding a calculated name field to the household table, then creating combo box on the skillset form that points to that field. (Result: I can look up the newly created household name, but any changes I make to the skillsets are being made to the first Skillset record - NOT to the newly created name. And no new skillset ID is created, so there's still no tie in between the two tables.
    • Added the primary key from the skillset table to the Household list view. (Result: The household list view is no longer editable, and no new records can be added).
    • Added lookup fields to both the Skillset and Resource tables, pointing to the HouseholdID (Result: Not really sure. Nothing useful happened)
    • Added lookup fields to the household table pointing to the ID's of the skillset and resource tables. (Result: Nada).
    • Added a new "join" table, with lookups to all three of the tables, then added subforms to each list view pointing to those tables. (Result: Nothing exciting happened).\
    • And eleventy other wrong ways, besides!


    So... what sort of voodoo sacrifice must I make to be able accomplish the following in the web app?



    1) Create a new household. Enter exciting new data, such as names and address.
    2) Click on the Skillset tab, and be able to find the newly created household among the 400 existing households I can already see.
    3) Click the nifty "Edit" button and begin assigning Skills to the newly created household.
    4) Click on the Resource tab, and be able to find the newly created household and add resources.
    5) Be able to look up the new household later, and find the resources and skillsets still appropriately assigned.

    That sounds SOOOOO easy! And it probably is, but I'm not able to figure it out. As near as I can figure, my issue is that at no time during the creation of the household is any sort of skillset ID created, and without that ID, no link exists between the two records.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,154
    Don't understand this db structure. How are skillsets and resources associated with household? Are Resources and Skillsets lookup tables? Are there multi-value fields in Households table for saving skillset and resource IDs? Why would each table have roughly the same number of records? Why would creating a household create new skillset IDs? If Skillsets and Resources are lookup tables, then these are finite lists available for selection. The Skillset and Resource IDs would be saved into the aforementioned multi-value fields of Households table.

    If there are not multi-value fields in Households, then use a 'join' (junction) table in form/subform arrangement: main from bound to Households and subform bound to junction table with comboboxes to select resource and skill.

    I've never built a web database so not really sure what is involved to accomplish but I think the Navigation form was designed with web database in mind. Same goes for multi-value fields. I don't like either of these features so I'm glad I've never had to build web database.

    Imported from what Access db - a downloaded template?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    256
    Warning: Access Web Apps are going away: http://www.utteraccess.com/forum/ind...post&p=2639600

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    2,304
    Thanks for the update, jwhite.

  5. #5
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    256
    np, I'm just glad I never took the time to learn it!

  6. #6
    ProvPC is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    31
    I'm sorry - I'm new to the forum, and don't really know how to "close" a thread. I worked it all out on my own (basically by forgetting everything I ever knew about access, and pretending it was all just a big spreadsheet). It works fine that way. I'm sorry to have wasted your time.

    Essentially, the Household table provides contact information for 400 or so families. The Skillset table provides a list of 60 or so available skills, and the Resource table provides a list of roughly 40 different resources. The difficulty is that any household can have any number of skillsets and/or any number of resources. While I found this relatively simple to do in Access, I found it a right royal pain to accomplish in Access Web Apps. I was never able to get the kinks worked out of many-to-many relationships in a way that made sense for the application I was trying to build.

    By "imported from Access" I'm referring to the data in the tables. The tables were joined by keys - HouseholdID, SkillSetID, ResourceID etc. In Access, creating a new household in the Household table was done on in the first tab of a form. Clicking on the second tab allowed the selection of any number of skills, and likewise the third table allowed the assignment of any number of resources. The result was that in each case, a new SkillSetID and ResourceID were created and assigned to the Household ID during the data entry. Afterwards, it was a trivial matter to create a search form with simple buttons to look for all the households who had a particular skill, or combination of skills (or resources).

    So... because the SkillSetID and ResourceID already existed in the data imported into the Access Web Apps tables, looking up existing data worked fine. But adding a NEW household did not create the necessary links in Resources and Skillsets to assign them to the household. Looking up the new household would not yield any skillsets, and looking up skillsets would display only the imported households, not the newly created one.

    Part of the confusion, I think, lies in how I perceive the skillsets and resources, not as individual items, but as a set of items assigned to an individual household. I was never able to make that work in a manner that would be simple for end users, so I resolved it by putting everything - Households, Skills, Resources - into one large table. It's not "access" perhaps, but it works flawlessly and is very easy to use.

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

Similar Threads

  1. Joining tables help
    By grewpar in forum Access
    Replies: 7
    Last Post: 10-07-2014, 11:11 AM
  2. Joining two tables
    By rspai in forum Queries
    Replies: 7
    Last Post: 05-21-2012, 11:21 PM
  3. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 03:37 AM
  4. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 02:43 PM
  5. Access 2007 runtime apps
    By Plumber in forum Access
    Replies: 0
    Last Post: 01-27-2009, 07:07 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
  •  
Tech Forums: Microsoft Office Forums