Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fun With Joins (Access 97)

    I'm sending out 4 short articles to a variety of publications but I want to make sure that none of the articles I plan to send out are at any of the publications where I have current submissions.

    Table 1 lists the articles I propose to send out:

    Sub1
    Sub2
    Sub3
    Sub4

    Table 2 lists the article, the publication and the date where it is currently submitted.

    Piece
    Publication
    Date

    I thought I could create a query that would use an Inner Join for Sub1 with Piece, then Sub2 with Piece, then Sub3 with Piece then Sub4 with Piece. But just bringing table 1 and table 2 into the query and then making the joins doesn't work. If Sub1 matches Piece but none of the rest do, nothing shows up. I thought I'd try to write an SQL statement that somehow forces the query to do each of the joins and then return only the ones that match. But, as usual, damned if I can figure out the proper FROM syntax etc.

    All help welcomed. (I have to say that the people on this board has been incredible and generous with their assistance so my continued thanks.)

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Fun With Joins (Access 97)

    I presume that Sub1, Sub2, Sub3 & Sub4 are all fields in the first table.
    You could do this with a Union query.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fun With Joins (Access 97)

    Leaving aside the issue of your table design, I think this *might* do what you're trying ... if I guessed right at what you're trying to do, that is.

    SELECT Table2.Publication, Table1.Sub1, Table1_1.Sub2, Table1_2.Sub3, Table1_3.Sub4
    FROM (((Table2 LEFT JOIN Table1 ON Table2.Piece = Table1.Sub1) LEFT JOIN Table1 AS Table1_1 ON Table2.Piece = Table1_1.Sub2)
    LEFT JOIN Table1 AS Table1_2 ON Table2.Piece = Table1_2.Sub3) LEFT JOIN Table1 AS Table1_3 ON Table2.Piece = Table1_3.Sub4;
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Fun With Joins (Access 97)

    Ian
    Please excuse my last post.

    Do you wish to see the records in Table1 which have not got a Table2 entry for the folowing joins:
    1. Sub1 joined on Field Piece
    2. Sub2 joined on Field Piece
    3. Sub3 joined on Field Piece
    4. Sub4 joined on Field Piece

    Pat

  5. #5
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fun With Joins (Access 97)

    I appreciate the responses so far but none are quite working.

    Let me clarify because Pat's point about table design is well taken, and Charlotte, your suggestion didn't seem to work. I got a long list with the one item that matched, rather than just 1 match. I don't want all the records to come back, I only want the matches to come back to the query. That's why I thought an inner join might be the approach.

    Table 1

    Sub1
    Sub2
    Sub3
    Sub4

    Table 2

    Piece
    Publication
    Date


    Sample Record Table 1

    The Horse Goes to the Moon
    The Rock and the Hard Place
    The Dog Catcher
    The Sneak

    Sampe Record Table 2

    The Sneak
    The Orange Review
    04/04/02


    I want the query to see if any of the fields in Table 1 -- Sub1, Sub2, Sub3, Sub4 -- match the Piece field in Table 2. If there is a match, I'd want the query to come back with the match only, something like:


    Sub 1-------Sub2------Sub3-------------- Sub4-------Piece--------------Publication---------------Date
    ----------------------------The Sneak---------------------The Sneak-------The Orange Review----04/04/02

    (The dashes are there because this forum won't show spacing in my little chart.) If there are no matches, the query would return nothing -- and thus I'd no there was no overlap in what I was proposing to submit and what I have currently submitted. Table 1 may have 100 records in it and Table 2 may have 100 records. It would be a lot to check manually/visually to see if there's a match.

    Does this make it any clearer what I'm trying to do?

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Fun With Joins (Access 97)

    Try the following UNION query:

    SELECT DISTINCTROW T1.Sub1, "..." as Sub2, "..." as Sub3, "..." as Sub4, T2.Piece, T2.Publication, T2.PubDate
    FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Sub1 = T2.Piece

    UNION ALL
    SELECT DISTINCTROW "..." as Sub1, T1.Sub2, "..." as Sub3, "..." as Sub4, T2.Piece, T2.Publication, T2.PubDate
    FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Sub2 = T2.Piece

    UNION ALL
    SELECT DISTINCTROW "..." as Sub1, "..." as Sub2, T1.Sub3, "..." as Sub4, T2.Piece, T2.Publication, T2.PubDate
    FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Sub3 = T2.Piece

    UNION ALL
    SELECT DISTINCTROW "..." as Sub1, "..." as Sub2, "..." as Sub3, T1.Sub4, T2.Piece, T2.Publication, T2.PubDate
    FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Sub4 = T2.Piece

    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fun With Joins (Access 97)

    When you say "..." as Sub3 do you mean literally quotation mark 3 periods with no spaces quotation mark? Forgive me if that's a really stupid question.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Fun With Joins (Access 97)

    It's not a stupid question !!

    Yes I do mean what you ask, the reason is when you use a UNION query all the fields of each SELECT must be aligned and be the same Type, so if I want to show Sub1 because of the join on Sub1 then I need to show something for the other 3 Subs.

    Why don't you just cut and paste the SQL and try it.

    HTH
    Pat

  9. #9
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fun With Joins (Access 97)

    So I can see from trying it that you do mean literally what's in the quotes. When I run the query it asks for T2.Publication and T2.PubDate. So I chose a publication I know where there's an overlap and made up a fictitious date. It returned the match but then it returned a fake match, telling me that another piece also matched the T2.Publication I put in. Both matches used the fictitious date. Close but not there. It shouldn't ask me for any information. It should just check for matches of sub1-4 in table 1 against piece in table 2 and return them or if no matches return nothing.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Fun With Joins (Access 97)

    You have to use your field names for Publication and PubDate.
    What are these fields called in Table2.
    Just substitute your field names and you should be ok.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    Lounger
    Join Date
    Sep 2002
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fun With Joins (Access 97)

    Bravo!!!! It seems to work perfectly. Many 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
  •