SQL SYNTAX ERROR ON JOIN OPERATION

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Will G - 28 Mar 2007 01:18 GMT

can anyone see what i am not see...the sql bellow give me a syntax error on
join operation, but i dont see where. is it because there are too many joins?

SELECT MSM.ModelNumber, MSM.Desc1, MSM.Desc2, Spring.Spring, Label.Label,
Size.Size, Needle.Needle, Pattern.Pattern, Config.Config, Border.Border,
MSM.Produce, Concatenate("SELECT Qfill FROM qrQfills WHERE ModelNumber = """
& [MSM].[ModelNumber] & """") AS Qfill, Concatenate("SELECT Bfill FROM
qrBfill WHERE ModelNumber = """ & [MSM].[ModelNumber] & """") AS Bfill,
Concatenate("SELECT UphFill FROM qrUphfills WHERE ModelNumber = """ &
[MSM].[ModelNumber] & """") AS Uphfill, Concatenate("SELECT PtFill FROM
qrPtfills WHERE ModelNumber =""" & [MSM].[ModelNumber] & """") AS Ptfill,
FoamCore.FoamCore, FoamEnc.FoamEnc
FROM [Size] INNER JOIN (Pattern INNER JOIN (Needle INNER JOIN (Label INNER
JOIN (Config INNER JOIN (Border INNER JOIN (FoamCore INNER JOIN(FoamEnc INNER
JOIN(Spring INNER JOIN MSM ON Spring.SpringID = MSM.SpringID) ON
Border.BorderID = MSM.BorderID) ON Config.ConfigID = MSM.ConfigID) ON
Label.LabelID = MSM.LabelID) ON Needle.NeedleID = MSM.NeedleID) ON
Pattern.PatternID = MSM.PatternID) ON Size.SizeID = MSM.SizeID)ON
FoamCore.FoamCoreID = FoamCoreID)ON FoamEnc.FoamEncID = MSM.FoamEncID;

Signature

need help

Reply to this Message

John Spencer - 28 Mar 2007 02:02 GMT

When I break down your query I note that you are missing spaces in
several places in the FROM clause. Usually right before a parentheses.

... INNER JOIN(FoamEnc ...
...INNER JOIN(Spring ...

Missing MSM as table name in the following
...ON FoamCore.FoamCoreID = FoamCoreID...
Also, your JOINs should parallel your ON clauses

So you should end up with something like the following if I got all my
parens matched up correctly.

SELECT ...

FROM [Size]
INNER JOIN (Pattern
INNER JOIN (Needle
INNER JOIN (Label
INNER JOIN (Config
INNER JOIN (Border
INNER JOIN (FoamCore
INNER JOIN (FoamEnc
INNER JOIN (Spring
INNER JOIN MSM

ON Spring.SpringID = MSM.SpringID)
ON FoamEnc.FoamEncID = MSM.FoamEncID)
ON FoamCore.FoamCoreID = MSM.FoamCoreID)
ON Border.BorderID = MSM.BorderID)
ON Config.ConfigID = MSM.ConfigID)
ON Label.LabelID = MSM.LabelID)
ON Needle.NeedleID = MSM.NeedleID)
ON Pattern.PatternID = MSM.PatternID)
ON Size.SizeID = MSM.SizeID

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> can anyone see what i am not see...the sql bellow give me a syntax error on
> join operation, but i dont see where. is it because there are too many joins?
[quoted text clipped - 15 lines]
> Pattern.PatternID = MSM.PatternID) ON Size.SizeID = MSM.SizeID)ON
> FoamCore.FoamCoreID = FoamCoreID)ON FoamEnc.FoamEncID = MSM.FoamEncID;

Reply to this Message

Will G - 28 Mar 2007 18:46 GMT

Hey John,

this is what i got now and i still have the same errors.....it seems to work
on a query now, but when i get to create a report, it still doesnt accept
those new fields, foamCore, foamenc, boxtype, and boxprofile. somehow, it's
not displaying it.

SELECT LinkedSalesOrder.OrderNo, LinkedSalesOrder.ID,
LinkedSalesOrder.RequiredDate, LinkedSalesOrder.ID_1,
LinkedSalesOrder.Description, LinkedSalesOrder.Comment1,
LinkedSalesOrder.OrderQty, MSM.Produce, qrMSM.Bfill, qrMSM.Qfill,
qrMSM.Uphfill, qrMSM.Ptfill, LinkedSalesOrder.Comment2, MSM.SpringID,
MSM.LabelID, MSM.SizeID, MSM.NeedleID, MSM.PatternID, MSM.ConfigID,
MSM.BorderID, MSM.FoamCoreID, MSM.FoamEncID
FROM qrMSM INNER JOIN (LinkedSalesOrder INNER JOIN MSM ON
LinkedSalesOrder.ID_1 = MSM.ModelNumber) ON qrMSM.ModelNumber =
LinkedSalesOrder.ID_1
WHERE (((MSM.BoxChecked)=No))
WITH OWNERACCESS OPTION;

Signature

need help

> When I break down your query I note that you are missing spaces in
> several places in the FROM clause. Usually right before a parentheses.
[quoted text clipped - 57 lines]
> > Pattern.PatternID = MSM.PatternID) ON Size.SizeID = MSM.SizeID)ON
> > FoamCore.FoamCoreID = FoamCoreID)ON FoamEnc.FoamEncID = MSM.FoamEncID;

Reply to this Message

John Spencer - 29 Mar 2007 00:38 GMT

I don't see the fields in the Select clause of the query. You need to
have them there if you are going to use them in the report.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> Hey John,
>
[quoted text clipped - 15 lines]
> WHERE (((MSM.BoxChecked)=No))
> WITH OWNERACCESS OPTION;

Reply to this Message

Will G - 29 Mar 2007 21:00 GMT

Hey John,
thanks for the help, it worked. i was wondering if you have an answer for
me for the following question. i am new in access development and i am trying
to create a query that uses the function count to count the amount of items
for a vendor, i am trying to create a Purchase Order per vendor. now, i have
a vendor table, a spring table, a foam table and so on and a production query
that you helped me with. i like to create query that shows, spring, foam, and
other items per vendor. i created a query for each item and thier vendor, but
when i try to create just one for vendor and all items, i get stuck and get
bad results. it's there a directory where i could learn about that or can you
help? thanks again.

Reply to this Message