Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Location
    Telford, Shropshire
    Posts
    4

    Unanswered: Product Suppliers Register assistance

    I am trying to create a database where the user can input a Supplier, then a Product, and create a link to enable a lookup to find which Suppliers supply a particular Product or which Products are supplied by a particular Supplier.

    I have created the following tables:

    Products
    - ProductID (PK)
    - ProductName
    <other info about product>

    Suppliers
    - SupplierID (PK)
    - SupplierName
    <other info about supplier>

    ProductSuppliers
    - ProductID
    - SupplierID
    <other info>

    I have created my forms for Products and Suppliers and they appear to work ok. I think I need a PK for the ProductSuppliers, say, a combination of (Product,Supplier). How do I do that?

    Secondly, I need to provide some way for the user to make a selection (either product or supplier) and the relating information be displayed. This can be as a report or form output. I am just having problems getting this last bit working. I have tried to find other examples where this has been used but cannot find anything. Please could you help me. PLEASE.

    Oh yeah, happy New Year.

  2. #2
    Join Date
    Nov 2004
    Posts
    32
    You are on the right track. Every table should have a Primary Key so that each record can be uniquely identified. There are two ways you can go with the PK of the ProductSuppliers table depending on how you design. You can either create a new field with an autonumber data type or use a composite of the ProductID and the SupplierID fields. The advantage of the former is simplisity. The disadvantage is that the user can duplicate the record easily and put the same product to the same supplier more than once creating data problems. The advantage of the latter is this duplication can't happen the disadvantage is a slightly increased complexity when dealing with relationships. My option would be the latter. To do this you simply select both fields in designview as you would select two rows in Excel and then click on the key symbol in the database window tool bar.

    As far as the forms go you can do several things. Primarily the easiest is to create a form based on the supplier table and then insert a subform based on a query that combines the ProductSuppliers table and the information you want to see about the product such as the name etc. Once the query has been created, this can be done useing the form wizard or manuelly and assigning the suppliersID field as the master link and child link fields and the form type either datasheet or continueous. This is good if you want to see the suppliers information as well as the products that they sell.

    If you want to see the product information and who sells that product you could do a similar thing useing the product table for the main form and then use a query combining the Suppliers and ProductSuppliers for the subform.

    Another example would be to create an main form not base on any table with only a combo box. The combo box should have a query as the record source type with the query selecting the SupplierID and the SupplierName in that order from the Supplier table. Set the bound column to 1 amd the column count to 2. Then set the column width to 0; 5cm or what ever is appropriate to show the entire Supplier Name but the important part for looks is to make the first column 0. Then create a subform like before useing the query that combines the Products and ProductSuppliers tables. Seting the master link field as your combo box in the main form and the child link field to SupplierID.

    cheers
    TM

  3. #3
    Join Date
    Jan 2005
    Location
    Telford, Shropshire
    Posts
    4

    Please help

    TM,

    Thanks for your advice. I am fairly new to access and was wondering do you have a sample database you could forward to me with this working or know where I can download source just so that I can see the basics then modify my project accordingly. It doesn't have to be specific to Product and Supplier, just the working model so I can progress and learn. Alternatively, could you take a look at what I've achieved so far or at least talk me through the changes. Thanks! I hope you can be of help.

    DW (spider-man).

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by tmccollum
    You are on the right track. Every table should have a Primary Key so that each record can be uniquely identified. There are two ways you can go with the PK of the ProductSuppliers table depending on how you design. You can either create a new field with an autonumber data type or use a composite of the ProductID and the SupplierID fields. The advantage of the former is simplisity. The disadvantage is that the user can duplicate the record easily and put the same product to the same supplier more than once creating data problems. The advantage of the latter is this duplication can't happen the disadvantage is a slightly increased complexity when dealing with relationships. My option would be the latter. To do this you simply select both fields in designview as you would select two rows in Excel and then click on the key symbol in the database window tool bar.

    As far as the forms go you can do several things. Primarily the easiest is to create a form based on the supplier table and then insert a subform based on a query that combines the ProductSuppliers table and the information you want to see about the product such as the name etc. Once the query has been created, this can be done useing the form wizard or manuelly and assigning the suppliersID field as the master link and child link fields and the form type either datasheet or continueous. This is good if you want to see the suppliers information as well as the products that they sell.

    If you want to see the product information and who sells that product you could do a similar thing useing the product table for the main form and then use a query combining the Suppliers and ProductSuppliers for the subform.

    Another example would be to create an main form not base on any table with only a combo box. The combo box should have a query as the record source type with the query selecting the SupplierID and the SupplierName in that order from the Supplier table. Set the bound column to 1 amd the column count to 2. Then set the column width to 0; 5cm or what ever is appropriate to show the entire Supplier Name but the important part for looks is to make the first column 0. Then create a subform like before useing the query that combines the Products and ProductSuppliers tables. Seting the master link field as your combo box in the main form and the child link field to SupplierID.

    cheers
    TM
    On the Product - Supplier key issue, I do both. I have an autonumber, the ProductID and the SupplierID as the PK ... Reason? What if you have different price points (or even more likely price history) for the supplier? This allows for that history to exist ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jan 2005
    Location
    Telford, Shropshire
    Posts
    4
    Please can anybody assistance with samples or more help?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by spider-man
    Please can anybody assistance with samples or more help?
    Here's the problem: This is VERY customer specific ... That and I cannot release any code/modules because of confidentiality agreements (plus it's in ADO and VERY complex) ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jan 2005
    Location
    Telford, Shropshire
    Posts
    4
    Okay, I think I'm getting somewhere. I've got the Supplier form with Subform showing related Products from the SupplierProducts table.

    Now, if the user wants to go directly to a supplier using a dropdown or string search how can I do this?

    Thanks!

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by spider-man
    Okay, I think I'm getting somewhere. I've got the Supplier form with Subform showing related Products from the SupplierProducts table.

    Now, if the user wants to go directly to a supplier using a dropdown or string search how can I do this?

    Thanks!
    Numerous ways ... If you have a combobox with the supplier then in the AfterUpdate event you can write queries to load the corresponding data ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •