Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Question (Access XP)

    I am a newbie to Access and I am trying to setup a small database to compare prices I get from 4 vendors. I have created 2 tables, one for vendor and one for parts. How can I get a query to give me the lower price for the same part?

    Thanks
    RBelly

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

    Re: Query Question (Access XP)

    If you want to follow good database design, you need three tables:
    1. <LI>A parts table (PartID, Description, ...)
      <LI>A vendor table (VendorID, VendorName, ...)
      <LI>A linking table that contains VendorID, PartID and Price.
    And you need two queries: one to find the lowest price for each product, and a second one to find the vendor that has the lowest price for a given product. I have attached a zipped Access 97 database that demonstrates this. You wil have to unzip it and convert it.

    Look at the three tables in design view and datasheet view, and also at the relations between them (Tools/Relations...)
    Then look at the two queries in design view and datasheet view - the second one is based on the tables and on the first query.
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (Access XP)

    Thank YOU!

  4. #4
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (Access XP)

    One more Quesiton. On tblPrices you have two keys but I do not know how to get both keys to appear.

    Rbelly

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Question (Access XP)

    Excuse me for poking my nose in here, but where do you want both keys to appear? Are the 2 keys VendorID and PartID?
    Pat

  6. #6
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (Access XP)

    Yes I want PartID and Vendor ID in Hans sample he has them setup that way.
    I see it says I have to create a linked table and I am reading my Access book to see how to do that [img]/forums/images/smilies/smile.gif[/img]

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Question (Access XP)

    The following query shows the Vendor as well:

    SELECT tblPrices.VendorID, qryLowPrice.PartID, qryLowPrice.LowPrice
    FROM qryLowPrice INNER JOIN tblPrices ON (qryLowPrice.LowPrice = tblPrices.Price) AND (qryLowPrice.PartID = tblPrices.PartID);

    To create linked tables is very straight forward.
    1. Setup your tables in a database all by itself.
    2. Setup another database (called ProgDB for want of a name) to hold all the queries, forms, reports, macross and modules.
    3. From the ProgDB you can link the tables to the other database by New table, link, choose data DB, then choose tables.

    HTH
    Pat

  8. #8
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (Access XP)

    Ok but if you look at Hans example he does not do that way. Maybe I am not wording my problem corrrectly. He has tblParts and tblVendors and tblPrices has two keys on VendorID and PartID , they may be a primary key and a foreign key but when I check their properties I can not tell. I want to learn how to create this type of table.

    RBelly

  9. #9
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (Access XP)

    I now see why they say that Acces is "easy" but a bad design can bring the whole program down! I am stumped!

    Rbelly

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

    Re: Query Question (Access XP)

    Open tblPrices in design view. You will see a key symbol to the left of VendorID and PartID. This means that the combination of these two fields is the primary key - the table can have duplicate values for VendorID, and also have duplicate values for PartID, but the combined values must be unique.

    How did I create this? By selecting the first two rows in the field grid, and clicking the Primary Key toolbar button (the one looking like a key). I could also have created the primary key in the Indexes window. With the table in design view, select View/Indexes... (or click the toolbar button Indexes - it looks like a lightning flash to the right of some horizontal lines). The attached screen shot shows what a composite index looks like - the captions are in Dutch, but if you compare it to the Indexes window in your version, you'll be able to understand it, I hope. The index name occurs only once (in the first row); if I had entered a name in the first column of the second row, I would have created two separate indexes. You can set the properties of an index in the lower half of the Indexes window.
    Attached Images Attached Images
    • File Type: gif x.gif (3.8 KB, 0 views)

  11. #11
    Star Lounger
    Join Date
    Jul 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (Access XP)

    That is what I wanted to learn. I looked in 3 books and none of them said I could do that. [img]/forums/images/smilies/smile.gif[/img]

    Thank you once again.

    Rbelly

Posting Permissions

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