Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    5

    Unanswered: Outer join query question

    I have 3 tables.

    tblStorePrice(FuelType, RecDate, FuelPrice, Store_No)
    tblCompPrice(FuelType, RecDate, FuelPrice, Comp_No)
    tblStoreComp(Store_No, Comp_No)

    I want to match those records from tblStorePrice and tblCompPrice that have the same FuelType, RecDate, and then match on the tblStoreComp. There are many Comp_No's to Store_No's. I would like to get those that match and then also those that don't watch. So I want to see all the records in tblCompPrice and tblStorePrice, but see those that match as one record.

    Any ideas on how to do in a query in ACCESS, or can also be done then in a data access page. Thanks for any and all help!!!

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    A question on your structure. What is the relationship between tblStorePrice(FuelType, RecDate, FuelPrice, Store_No)
    tblCompPrice(FuelType, RecDate, FuelPrice, Comp_No)

    Looking at the structures they contain the same data so there is a relationship.

  3. #3
    Join Date
    Oct 2002
    Posts
    5
    The relationship is in the third table,

    tblStoreComp(Store_No, Comp_No)

    A Store_No can have many Comp_No's. So a Store_No will have multiple records in this table. The Comp_No's are all unique, so don't duplicate among Store_No's.

    In english, a Store can have multiple Competitors, that are each unique (with Comp_No) and in the tblCompPrice.

    Thanks.

    Originally posted by dynamictiger
    A question on your structure. What is the relationship between tblStorePrice(FuelType, RecDate, FuelPrice, Store_No)
    tblCompPrice(FuelType, RecDate, FuelPrice, Comp_No)

    Looking at the structures they contain the same data so there is a relationship.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select S.Store_No, S.FuelType, S.RecDate, S.FuelPrice
         , C.Comp_No, C.FuelType, C.RecDate, C.FuelPrice
      from tblStorePrice S
    left outer
      join tblStoreComp SC
        on S.Store_No = SC.Store_No
    left outer 
      join tblCompPrice C
        on SC.Comp_No = C.Comp_No
       and S.FuelType = C.FuelType
       and S.RecDate  = C.RecDate
    i think

    rudy

Posting Permissions

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