Fun With Joins (Access 97)

Topic

New Reply

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, I can’t 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.)

Reply | Quote

Replies

I presume that Sub1, Sub2, Sub3 & Sub4 are all fields in the first table.
You could do this with a Union query.
Pat

Reply | Quote

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;

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Bravo!!!! It seems to work perfectly. Many thanks.

Reply | Quote