I've managed to avoid Access for most of the last 4 years so I'm not sure
about the syntax for joining. I'll take your word that you need the
parenthesis.
Here's a version without parenthesis...
SELECT *
FROM words_tb
INNER JOIN meanings_tb ON words_tb.word = meanings_tb.wordFK
INNER JOIN categories_tb ON categories_tb.IDC = meanings_tb.IDCFK
INNER JOIN number_tb ON SOME JOIN CONDITION HERE
WHERE words_tb.word = '#form.word#'
I just opened up Access for the first time on this machine! Created 4
tables, joined them in some wizard, and here's the SQL it generated,
formatted for your pleasure...
SELECT
FROM ((Table_1 INNER JOIN Table_2 ON Table_1.ID_2 = Table_2.ID) INNER JOIN
Table_3 ON Table_2.ID_3 = Table_3.ID) INNER JOIN Table_4 ON Table_3.ID_4 =
Table_4.ID;
So I think the solution is to nest the parenthesis. In fact, swap your
tables into the above and it might work straight off.
Or try this...
SELECT *
(
(
FROM words_tb
INNER JOIN meanings_tb ON words_tb.word = meanings_tb.wordFK
)
INNER JOIN categories_tb ON categories_tb.IDC = meanings_tb.IDCFK
)
INNER JOIN number_tb ON SOME_JOIN _CONDITION_HERE
WHERE words_tb.word = '#form.word#'
.... spaced out so you can see the nesting better. Watch the wrap.
Well that was fun :OD
Let us know how you get on.
Teach Yourself SQL in 10 Minutes by Sams Publishing will help a lot with
your SQL. I put it up there with Books Online(BOL) as a resource.
Ade
-----Original Message-----
From: Roberto Perez [mailto:[EMAIL PROTECTED]
Sent: 26 February 2005 00:04
To: CF-Talk
Subject: adding table to Query?
Hi all,
With help from the list (much appreciated :-) ) I set up my first inner
join with a linking table. My query, which works great, looks like this:
SELECT *
FROM (words_tb INNER JOIN meanings_tb
ON words_tb.word = meanings_tb.wordFK)
INNER JOIN categories_tb
ON categories_tb.IDC = meanings_tb.IDCFK
WHERE words_tb.word = '#form.word#'
My problem is, the table "meanings_tb" has another associated table called
"number_tb", a sister to "categories_tb". Both "number_tb" and
"categories_tb" feed data to "meanings_tb" via a one-to-many relationship
through IDCFK (for categories) and IDNFK (for number).
My question is, how would I go about adding the fourth table ("number_tb")
to the query? Do I have to "nest" an INNER JOIN that captures the
relationship between Meanings, Categories, and Number, inside the main
INNER JOIN between Words and Meanings?
(Note: since this is Access, I've found out the parentheses are not
optional)
Thanks in advance for your answers,
Roberto Perez
[EMAIL PROTECTED]
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.5.0 - Release Date: 25/02/2005
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196743
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
