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

    Unanswered: like a full outer join?

    I am looking for some guidance on how to do a full outer join in MS access.

    SELECT DISTINCT [tblStorePrice].[FuelPrice], [tblStorePrice].[Store_No], [tblCost].[FuelCost], [tblCost].[RecDate] AS CostDate, [tblStoreAddress].[City], [tblFuelType].[FuelTypeDesc], [tblCost].[RecDate]
    FROM ((tblFuelType INNER JOIN tblStorePrice ON [tblFuelType].[FuelTypeNo]=[tblStorePrice].[FuelTypeNo]) INNER JOIN tblCost ON [tblFuelType].[FuelTypeNo]=[tblCost].[FuelTypeNo]) INNER JOIN tblStoreAddress ON [tblStorePrice].[Store_No]=[tblStoreAddress].[Store_No]
    WHERE (((Month([tblCost].[RecDate]))=Month(Date())) AND ((Day([tblCost].[RecDate]))=Day(Date())))

    Here is the query that I have with the where matching on the dates of the 2 tables. I need records to be created when they do match on the dates, AND I need to records when they don't match. So when they do match there would be 2 more fields filled for each record. Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    When you design the query using the QBE grid, look at the Join Properties dialogue by right clicking the line joing the tables. If you choose the correct type of join, you should get the result you want.
    Roger Hampson
    XI - ecs (UK) Ltd

  3. #3
    Join Date
    Sep 2003
    Posts
    2

    FULL OUTER JOIN in ACCESS

    Originally posted by xiecsuk
    When you design the query using the QBE grid, look at the Join Properties dialogue by right clicking the line joing the tables. If you choose the correct type of join, you should get the result you want.
    I think the previous suggestion cannot work - the full join option is not offered. I have played around with this and come up with:

    select * from LeftTable Left join RightTable on LeftTable.key = RightTable.key
    UNION
    select * from LeftTable Right join RightTable on LeftTable.key = RightTable.key;

    This may generate some duplicates so you can add DISTINCT (and/or ORDER BY) by surrounding the whole lot with:

    Select DISTINCT * from (. . . . .) ORDER BY . . .

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    kadabada, Need more information. What version of Access are you using?

    You state: "Here is the query that I have with the where matching on the dates of the 2 tables". But looking at you query:
    Code:
    SELECT DISTINCT
           tblStorePrice  .FuelPrice
         , tblStorePrice  .Store_No
         , tblCost        .FuelCost
         , tblCost        .RecDate AS CostDate
         , tblStoreAddress.City
         , tblFuelType    .FuelTypeDesc
         , tblCost        .RecDate
    FROM tblFuelType 
           INNER JOIN 
         tblStorePrice 
           ON tblFuelType.FuelTypeNo = tblStorePrice.FuelTypeNo
           INNER JOIN 
         tblCost 
           ON tblFuelType.FuelTypeNo = tblCost.FuelTypeNo
           INNER JOIN 
         tblStoreAddress 
           ON tblStorePrice.Store_No = tblStoreAddress.Store_No
    WHERE Month(tblCost.RecDate) = Month(Date()) 
      AND Day  (tblCost.RecDate) = Day  (Date())
    I only seeing where one table is matching the same month of current date in the WHERE clause. And none of the ON clauses used a date column to join.

    You have 4 tables. How do you want to join these tables?

    Inner join Tab1-Tab2, Tab3-4 then Full Outer join the TAB1-TAB2 set with Tab3-4 set.
    Inner join Tab1-2-3 then Full Outer Join that set with Tab4.
    Some other combination?

    What is the column you want to do the Full Outer Join on?

    PS While I haven't tried it, If Access can do Left Outer Joins, it should be able to do Full Outer Joins (but it may depend on the version you are using). You may or may not be able to do it with the QBE grid but you should be able to use the SQL pane to type it in.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Unfortunately Stealth you've been caught out by spam. The thread is nearly 7 years old. I've deleted the spam now.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - Access doesn't support full outer joins. I haven't read the question, but it is very rare (read - I've never yet seen an occasion) when full outer joins are necessary. The problem can usually be accomplished another way, typically using UNION (or INTERSECTION or EXCEPT).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Aargh....

    I didn't even see the date I was responding to. I will try to pay more attention next time.

    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
  •