Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outer Joins (97)

    I have a part table with a unit price(base) and a discount table with a unit price. The discount table has a key of part number and discount code. I have linked the tables on the part number and I am trying to create a price book that will show the base unit price from the part table and a unit price for the discount code. I want to print the base unit price as the discount price if the discount price does not exist.
    example
    Part Table:
    Part price
    123 $12.00
    124 $12.50
    125 $13.00

    Discount table:
    Part Discount code Price

    123 AA $11.00
    125 AA $12.00

    The result I want:

    Part Base Disc
    123 $12.00 $11.00
    124 $12.50 $12.50
    125 $13.00 $12.00

    What I get is #error for the part 124 discount price. I realize that there is no price in the query for the second discount line and I am prepared to code around that, but right now I can not find a way to recognize that the price does not exist in the query. Any ideas?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outer Joins (97)

    Why not just add your discount field to the parts table?

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outer Joins (97)

    I have many different discount codes. I have retailers that get one discount and distributors that get a different discount. The price book I want to publish will limit the pricing to the appropriate customer type.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outer Joins (97)

    This SQL statement:
    <pre>SELECT tblPart.Part, tblPart.Price,_
    IIf(IsNull([tblDiscount].[Price]),[tblPart].[Price],[tblDiscount].[Price]) AS Base
    FROM tblDiscount RIGHT JOIN tblPart ON tblDiscount.Part = tblPart.Part;
    </pre>


    seems to give you the result you are looking for.
    It's using an outer join and replacing any Nulls in Discount Price with the Part Price.
    HTH

  5. #5
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outer Joins (97)

    Thanks!

Posting Permissions

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