Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table design (Access 2000)

    Could you help me bult the right tables and query for a comparison of the prices between different competitors? I am not sure how to build the tables, and also the query. I have put our own products in the table products.
    For example, our product 2, called OurPoductBBB has an end price = 555 and is equal to the competitor product 1 Fiat aaa with an endprice = 200 and also to the Competitor product 3 VW wwww = 1000
    I want all these products to be expressed in a query and also in a report.,for example:
    OurPoductBBB 555 Fiat aaa with an endprice 200 Competitor product 3 VW wwww 1000
    Which is the best way to do it,shall I build one table for the competitors or each separate table
    I am sending some ideas about the tables, but they do not work
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table design (Access 2000)

    I'd create three tables:
    - A table for the producers
    - A table for the products
    - A table for the correspondences between the products.

    See the attached version of the database.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table design (Access 2000)

    Thank you very much for your proposal. You have suggested something much needed for us and I think it will come in very useful for us. This is for the comparison between competitors as I have asked. I am so grateful to you , you have helped so much to build a truly good database, for my standards at least.
    In my case I have another question, close to it and it concerns counterproducts.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
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table design (Access 2000)

    You'll need to take an approach similar to the one I showed you for products. You'll need a producers table, a products table that lists the producer and price for each product by each producer and a correspondences table with 2 productid columns. If there are 4 competitors that make anti-freeze, there'll be 4 records in the correspondences table

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table design (Access 2000)

    Would you help me with the products table ? You write that i need " a products table that lists the producer and price for each product by each producer ". This sounds to me excellent and i need exactly this. How could i achieve that ? I am obliged to create a special table for each company, so that i will have to have one table for Shell, another for Mobil etc.because this table will contain not only the price but several other positions and will be used for other purposes too.So i will have somehow to connect these tables with the products table

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table design (Access 2000)

    You should *not* create a separate table for each company, that violates the "rules" for a relational database.
    You can adapt the design of the database I attached to <post:=699,859>post 699,859</post:> higher up in this thread.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table design (Access 2000)

    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 699,859 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 ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Table design (Access 2000)

    You enter the products (and their prices) in a form based on the products table. At that point, you don't enter the correspondences between the products yet.
    Later, you can enter the relationships between the products in a form based on the correspondences table.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table design (Access 2000)

    Thank you Hans I really appreciate your reply.I wondered how to begin and now after my trial with these two forms i got the idea how shall i start entering my data
    Thank you !

Posting Permissions

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