1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2010 Search

Discussion in 'Business Applications' started by Butterfly807, Jun 15, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    Hello. I am currently trying to create a Vendor Database. I have created 2 tables:
    • Product/Service Directory - lists various products/services by supplier
    • Vendor Contact by Plant - lists all suppliers and our locations that they provide to
    I need to be able to set up a form for users to perform one of the following functions:
    1. Select a Supplier: A list of products/services provided by that supplier is populated along with my companies' locations that the supplier provides to
    2. Select a Product/Service: A list of suppliers that provide that product/service is populated along with my companies' locations that the supplier provides to

    Is this at all possible in Access 2010?
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Butterfly807, welcome to the forum.
    Yes Access does that very well indeed, however I am not sure that your table structure is at it's best.
    Can a vendor supply more than one location and can a Vendor supply more than on product.
    If yes then you may need a Table that links the items together called a many to many table.

    To do what you want a Mainform with a subform would be ideal.
     
  3. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    OBP,

    Yes to both of your questions. Many of our vendor supply multiple locations and provide multiple products or services.

    What functions would I need to use in the mainform and subform? I've only taken and Introductory Access class, so I'm by no means an expert on all the functions of Access. But I'm hoping that with some help I can figure it out. Would I have to use Combo or List boxes? And how would using those generate an answer to a selection (ex. select supplier - get an answer of products)

    Thanks!
     
  4. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    I'm also not sure how to make a primary key for one of my tables so that I can achieve a one-to-many relationship with my other table. For example, the table lists each supplier (column 1) and the location they sell to (column 2). Both columns are going to have duplicate entries because each supplier will be listed multiple times (across multiple different locations) and each location will be entered multple times (across multiple suppliers). How would I achieve designating a primary key?
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, the set up you need is as follows
    A supplier Table with a SupplierID Key field set to Autonumber, plus the other fields you want like name, address etc.
    A Locations Table with a LocationID Key field set to Autonumber, plus the other fields you want like address etc.
    A Product/Service Table ProductID Key field set to Autonumber, plus the other fields you want like Description etc.
    Now you need a table called something like SupplierLocations and it should have a SupplierID field (not key) set to Number type long and a LocationID also (not key) set to Number type long.
    This allows you to select as many suppliers and Locations as you like.
    You can add a ProductID field of the same type to it add products which now allows as many Supplier/Location/Product combinations as you want.
    Or uoi could have a Supplier/Product table of the same design to show Product/Services for suppliers.
     
  6. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    I am really excited to get this all in and test it out! Thanks so much for the help! I'll let you know how it goes!
     
  7. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    I've been able to get my tables set up. I've been playing around with the forms to try to get them to do what I want, but am having difficulty.

    What I want to be able to do is create 2 separate forms. In one form, I want users to be able to type a supplier name, or select from a drop down list. Once that supplier is selected, I'd like the form to produce a list of the products and services provided by that supplier, and our locations that the supplier services.

    For the second form, I would like the opposite of the first. For users to type a product or service they need (or select from a drop down menu) and the form will product a list of suppliers that provide that product or service, as well as our location that the supplier services.

    I am not sure if I can have each of the form provide the service location or not. Perhaps I may have to set that up as a third form? I'm not sure...

    Any guidance is greatly appreciated! I've been playing around with the forms for a while and can't seem to figure out how to get the results I'd like to see.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    You should ideally use 4 forms 2 mainforms and 2 subforms.
    Mainform 1 would be to select the Supplier and based on the suppier table with a Find Combo and the Supplier ID.
    It would have a subtable of product/services based on the Linking table and the Service/Products table.
    The 2 forms would use a Master Child link of ServiceProductID

    The other pair of tables would be reversed, the maintable would have the Product/service combo & ID and the subform would have the Linking table & Customer. The 2 forms would use a Master Child link of CustomerID
    To combine the Linking table and Product Services for the subform you create a Query with Linking table and Products table in it and the other subform you use a query with the linking table and Customer table.
     
  9. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    I'm having an issue where the subform is pulling each product/service multiple times. I have each product/service listed multiple times in my VendorProductService table (combining the Vendor Table and the Product Service Table), but each duplicate it assigned to a different supplier. What have I done wrong, that causes each product/service to appear multiple time in my subform when I've selected a supplier (and it should only appear once)?

    I think I need more Access Training. This is a challanging database to figure out!
     
  10. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    I don't know what I did.... but it's working now. Please disregard my last post.
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Excellent, it would be nice if you could figure out what you did, maybe Master/Child link?
     
  12. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    I'm having an issue getting the Form to act the way I want it to. I want to use a drop down box so that the user may select a supplier, and then a list of products will appear (and vice versa on a second form).

    However, each time I create the form, it creates 300 separate records. When I add the combo box, there are still 300 separate records.

    Is there a way to have one record in a form, where a user can select 'bolt' for example, and then a list will populate all suppliers that sell a 'bolt'?
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Do you know how to create a "Find" combo?
    Or how to set the Form's Filter?
    You can use both techniques to show records.
    You would normally have the Supplier as the mainform and the Products as a subform and the opposite for the other requirement.
     
  14. Butterfly807

    Butterfly807 Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    70
    I do not know how to do a "Find" combo, but am challenging myself to figure it out. I'll see what I can figure out about setting the Form's Filter also.

    I've only taken an Introductory course to Access. Most of what I've learned I either learned from your guidance or figured it out somehow on my own (which takes a looong time).

    I'll let you know how it goes...
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Don't bother I will instruct you, first the Find Combo, use the Combo Wizard to add the Supplier Combo to the Mainform.
    When the wizard starts do not choose the default option which is "I want the Combo to Look up the values in a table or Query".
    Instead select the bottom one of the 3 which is "Find a Record on my Form based on the value I selected in my Combo box"
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1057216