Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    232

    Arrow Unanswered: 4 tables with 2 column (1 column same) Simple

    I have 4 tables with 2 column

    channeltnumber (has number)
    Cost ( dollar value)


    How do i join all this 4 tables when some of the channel number is missing in 1 table yet i want all the channelnumber to appear


    I just forgot
    bigfoots

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: 4 tables with 2 column (1 column same) Simple

    SELECT A.ChannelNumber, B.Cost
    FROM channeltnumber As A LEFT JOIN Cost As B
    ON A.Channelnumber = B.Channelnumber
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Oct 2003
    Posts
    232

    not exactly correct

    suppose i dont know which table is deficient in channelid
    bigfoots

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: not exactly correct

    Originally posted by sjumma
    suppose i dont know which table is deficient in channelid
    You better ... What you can do is run the unmatched record wizard and that'll tell you where you have unmatched records ...

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: not exactly correct

    I think access supports OUTER JOIN doesn't it?

    SELECT nz(A.ChannelNumber, B.ChannelNumber), B.Cost
    FROM channeltnumber As A OUTER JOIN Cost As B
    ON A.Channelnumber = B.Channelnumber
    WHERE A.ChannelNumber ISNULL OR B.Channelnumber ISNULL

    Is that what you're getting at?

    I'm trying to get my head around your question here.. Best I can tell, you're essentially looking for a full dataset with a coalesced value..

Posting Permissions

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