Results 1 to 6 of 6
  1. #1
    JuanL is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3

    Using Queries to create entries

    I am fairly new to Access, FYI

    I am making a vendor database that includes the types of services offered by a vendor. I would like to have something like a Multiple Items form that is filled with the different services and a checkbox to indicate if the vendor offers that service. The only thing is that I don't want to create a checkbox for every type of service especially in the case that we add something. What I've done is create a table that includes the service field and a checkbox. A separate table houses vendor info like name and address. One query makes a list of the vendor names. I use a separate query to pull the service and checkbox with the vendor name query. This almost does what I want. It ties a vendor name to the whole list. This way, I can open the form and filter by name. However, I cannot update the checkbox. I get why: there are multiple vendors trying to control the same checkbox - in a sense. Is there an automated way to do this besides creating a checkbox for each service?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    What you are describing is a many to many relationship so you need 3 tables, not 2.

    tblVendors
    VendorPK
    VendorName
    ...
    ...


    tblServices
    ServicePK
    ServiceName
    ...
    ...

    tblVendorServices
    VendorServicePK
    VendorFK
    ServiceFK

    PK=Primary Key to uniquely identify the record
    FK=Foreign Key - to identify with vendor or service

    in a form you would have a main form based on tblVendors and a subform based on tblVendorServices which would use a combo to select a service from tblServices

    or the other way round - a main form based on tblServices and a subform based on tblVendorServices which would use a combo to select a vendor from tblVendors

  3. #3
    JuanL is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3
    Ajax

    Thanks for the reply. I've been busy with other projects, but I finally have time to get back to this one. I'm trying to have the whole list of services available to be selected and deselected for any given vendor. I liken it to the process for selecting skills during a job application. The whole list is available for you to check off and when you select it, the skill is tied to you; or deselecting it removes that skill. I'd imagine that tying the service to the vendor would still require the many to many relationship, but I am not sure how to achieve something like a list of services for each vendor with a checkbox that ties the two together.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    explained in my post - a mainform and a subform.

    if you want tickboxes you will need to use a multivalue field, but be warned, it will create a hidden junction table and mess around with relationships so you lose some control, and can cause issues down the line because they are not that easy to work with.

  5. #5
    JuanL is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2022
    Posts
    3
    Ajax

    I took your advice to avoid the hidden junction table. Now I am creating a search query that contains the vendor table and the join table. The issue is that when I search by the vendor name, it returns different rows for as many services that are tied to that vendor. I can see why it would do it, but I would prefer to eliminate the other rows. I tried using the "unique values" and "unique records" properties for the query, but it did not make a difference. Any advice?

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389

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

Similar Threads

  1. filter on load and allow create new entries
    By Bkndbrown in forum Forms
    Replies: 1
    Last Post: 10-12-2020, 11:29 AM
  2. Replies: 1
    Last Post: 05-20-2013, 01:45 PM
  3. Sum of entries from seperate tables/queries
    By krutoigoga in forum Queries
    Replies: 4
    Last Post: 07-07-2011, 11:54 AM
  4. Count date entries from seperate tables/queries
    By krutoigoga in forum Queries
    Replies: 3
    Last Post: 06-30-2011, 12:48 PM
  5. Queries, using record entries only once?
    By no1beyondfan in forum Queries
    Replies: 11
    Last Post: 04-20-2011, 08:57 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