Results 1 to 7 of 7

Thread: Table design

  1. #1
    Join Date
    Oct 2005
    Posts
    23

    Unanswered: Table design

    I need an advice how to relate tables containing the lists
    of the competitors against our list of products.The basis for our database is the table products. In our table products, which I am applying, we have listed all our products. Also, we have received the products for the competitors they are Shell,Mobil,etc.Each of these lists contains a lot of products. We have to enter them somehow in our database.I have to show the equivalents, or as we call them counterproducts of the other companies. For example our product is called Antifreeze Silstone B. The name of the shell product is different, I have to find it and connect it with the Lookup wizard. But also I have to find the counterproduct for Mobil in a similar way. And also for the other 6 competitors.I can easily do it for one customer, as I have shwon it.In my case it is Shell. But it is not enough. I also have to connect with the Lookup wizard for Mobil. How can I do it ? And also in the same way for the other competitors. So I think I should have mentioned that it concerns more the counterpoduts and way I could do it in the table products

    i have build the tables in the following way.Table Mobil, the autonumber is mobilid , the next table is Shell, the autonumnber is Shellid,and i conect them with my table products.Which is the best way to obtain a clear cut query and report containig our products and the counterparts from the different competitors?
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2008
    Posts
    19
    You could use two tables: PRODUCTS and OTHERS. Append Mobil, Shell, etc into OTHERS. OTHERS would have the following fields : OTHERS_ID (autonum), OTHER_PRODUCT_NAME, OTHER_COMPANY_NAME, etc. This should allow you to do one report listing each of your products with any matches from others, e.g., a select query containing both tables (not joined) with criteria for other_product_name such as "Like Left(product_name, 4) OR right(product_name,4) ") , and then sorting by product and other_company_name.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    TBH, it looks to me like you need to implement a many-to-many relationship between products and competitors products. For example you have productA. ProductA should be able to relate to product123 and product012. Product123 relates to company "Mobil" and product012 relates to company "Shell". Then you can list all the companies (or products) that are a competitor to any given product.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Oct 2005
    Posts
    23
    Thank you so much for you detailed recommendations.I understand now that my effort to use a separate table for each competitor is inappropriate.I also understand that i must have one table for the products with a field to identify whether it is my product or a competitors. Then i must also have a correspondence table that lists my product ID and each matching competitors.
    I tried to follow the principle in your post but i cannot make my interface where to enter the products,i think this task is too difficult for me.Could you show me with your post how to do it with the following simple example:
    My company name is called Meguin. One of our products is called Meguin 1234.Meguin 1234 corresponds to Shell alvania2 from the company Shell and Mobilgrease 2 from the Company Mobil.How could i enter these corresponding products in a query so that later on to be able to add on their prices ?Is it possible to illustrate this with an attachement?

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You may also want to glance at the Northwinds sample mdb. I believe it has a Products table that might give you some ideas. It also has some good examples on table relationships as well as form design.
    Last edited by pkstormy; 03-17-08 at 22:30.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049

    This might help...

    This may or may not do what you need, but it's the kind of direction that I would be heading in...
    Attached Thumbnails Attached Thumbnails sshot.jpg  
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Mar 2008
    Posts
    19
    Can you use a form instead of a query? If so, create the Other table with fields including other_id, your_product_id, other-name, and other-price. Create the table Products with your_product_id, your_product_name, etc. Create a form that shows the Other products table, including the field other-price. Put a dropdown on the form of your products that fills your product id in each Other record. You can step through the Other records using the form and edit any of th fields now or later including price.

Posting Permissions

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