Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004

    Unanswered: Access 97 - Multiple Table Query Question

    Hello all,

    I have a problem with a query in Access 97, and I'll try to give you
    all of the details to see if you have any idea if it's even possible:

    I have 2 tables: Inventory and Pricing

    The inventory table basically has a part number, and all of the part

    Part Number 	Part Description 	Weight 	Category
    000125465 	Test Part		35	Muffler
    I also have a pricing table that contains the part number, and
    multiple pricing for that part such as:

    Part Number	Price Type	Price
    000125465 	Sale		55.00
    000125465 	Retail		104.00
    000125465 	Web		49.00
    What I'm trying to accomplish with a query is that I'd like all of
    this data to come back in one row

    Part Number - Part Description - Weight - Category - Sale Price (If it
    exists) - Retail Price (if it exists) - Web Price (if it exists)

    The problem that I have is that when I build a query I can only seem
    to get one price and price-type from the pricing table, so when there
    are 3 prices in the pricing table, I get 3 results returned in my

    If you have any ideas, or suggestions on where I might be able to find
    a solution to this, I would really appreciate that.

    Thank you,
    Jeff Homan

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    try this (caution: untested):
    select i.[Part Number]
         , i.[Part Description]
         , i.Weight
         , i.Category
         , s.Price as [Sale Price]
         , r.Price as [Retail Price]
         , w.Price as [Web Price]
      from (
           inventory as i
    left outer
      join pricing as s
        on (       
           i.[Part Number]
         = s.[Part Number]
       and s.[Price Type] = 'Sale'
    left outer
      join pricing as r
        on (       
           i.[Part Number]
         = r.[Part Number]
       and r.[Price Type] = 'Retail'
    left outer
      join pricing as w
        on (       
           i.[Part Number]
         = w.[Part Number]
       and w.[Price Type] = 'Web'
           ) | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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