Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    building a table (Access 2000)

    Can you help me with building a new table? My target is to prepare a report with the prices of the competitors,having only the table products..Surely somebody must have
    come accross this task. I intend to build a table "competitors" where to write down the product names of the competitors and their prices against our product.For our product i have used the
    LookUp wizard in the table to tie it up with the table products.After that i intended to build a query filling up the remaining fields from the table products, but my
    query called "query1" does not function. How can i mend the query and is the best way to build a table for the competitors?

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

    Re: building a table (Access 2000)

    You don't need the fields Size, Crates and Bottles in the Competitors table, as far as I can see, they would only duplicate the fields of the same name in the Products table. So you don't need an update query either.

    I wouldn't use separate fields for the first and second competitor. Instead, use two tables:
    - The Competitors table contains an AutoNumber field CompetitorID (the primary key), plus one or more text fields describing the competitor (company name, address, etc.)
    - The CompetitorProducts table contains fields ProductID (number, long integer), CompetitorID (number, long integer) and Price (currency).

    The combination of ProductID and CompetitorID is the primary key in the CompetitorProducts table. Each record in this table represents a product offered by a competitor. If a certain competitor has three products, there will be three records for this competitor.

  3. #3
    New Lounger
    Join Date
    Oct 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: building a table (Access 2000)

    Thank you Hans! Your reply is perfect and the structure you have suggested will serve me in the future to build such tables.
    I somehow cannot find the right way to express what i need to build.Therefore please allow me to enumerate with examples what i want.
    For example for our first product called berries the counterporudts of the competitors are
    sweetis,melies and levy.

    And at the end i want to have a report showing as follows


    berries 4,00 sweeties 4,40 melies 3,30 levy 2,200
    or, even shorter
    berries 4,00 4,40 3,30 2,20

    i think i have to rearrange the table CompetitorTables.

    Can obtain this rows with some kind of a query ?

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

    Re: building a table (Access 2000)

    You have forgotten to link the Products table to the CompetitorProducts table, and created two relationshops between Competitors and CompetitorProducts; you need only one - see the screenshot in my previous reply.

    Why not list the prices for the different competitors below each other? It's much easier.
    If you really want to list them next to each other, you can create a report with multiple columns, printed across then down. You set this in the Columns tab of File | Page Setup... for the report.

    PS You should compact the database before zipping it (Tools | Database Uitlities | Compact and Repair Database). This will reduce the size of the database and of the zip file significantly.

Posting Permissions

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