Advice regarding SQL script

[paulbeckett]

Hi

Based on Kevin's (completerookie) advice I am creating some SQL code:

SELECT HTMLVariables.nVariableID, HTMLVariables.sName,
ProductProperties.sProductRef, ProductProperties.sString1,
Product.[Short description]
FROM Product INNER JOIN (ProductProperties
INNER JOIN
HTMLVariables ON ProductProperties.nValue1 = TMLVariables.nVariableID)
ON Product.[Product reference] = ProductProperties.sProductRef
WHERE (((HTMLVariables.nVariableID)=30
Or (HTMLVariables.nVariableID)=31
Or (HTMLVariables.nVariableID)=80))

however when I try to run this query it comes up with an error "Syntax error in JOIN operation". Can anyone see where this error is or what I might of done wrong. I can't seem to pinpoint the issue.

Many Thanks

Paul

KJ Beckett

there is alternative methods that handle the sql using PivotTables ( or CrossTabs ) - not often used, but very very powerful

firstly use a bit of throwaway SQL to find all the CustomVars you want to show on your report using the following SQL code

Code:
SELECT HTMLVariables.nVariableID, HTMLVariables.sName
FROM HTMLVariables
WITH OWNERACCESS OPTION;Make a note of the numbers in the 1st column. - in my particular case, the CustomVars were types 9,12,13,16,17

Now create a second query called "qryFindAllCustomVars" - switch to SqlView on the VIEW menu and enter the following:


Code:
SELECT HTMLVariables.nVariableID, HTMLVariables.sName,
ProductProperties.sProductRef, ProductProperties.sString1,
Product.[Short description]
FROM Product INNER JOIN (ProductProperties
INNER JOIN
HTMLVariables ON ProductProperties.nValue1 = TMLVariables.nVariableID)
ON Product.[Product reference] = ProductProperties.sProductRef
WHERE (((HTMLVariables.nVariableID)=9
Or (HTMLVariables.nVariableID)=12
Or (HTMLVariables.nVariableID)=13
Or (HTMLVariables.nVariableID)=17
Or (HTMLVariables.nVariableID)=16))
I've laid out the code this way so you can see the WHERE clause easily - you can ammend the WHERE clause to suit your circumstances

run the query and it should tell you if you have the correct values coming back - if not, adjust the query to make it right

this will show you
the CustomVar number used in the WHERE clause
the name of the CustomVar
the ProductRef
the value of the CustomVar
the main description of the product

save this as "qryFindAllCustomVars"
now you need create a second query called "qryFindAllCustomVars_Crosstab" and enter the following code:

Code:
TRANSFORM First(qryFindAllCustomVars.sString1) AS FirstOfsString1
SELECT qryFindAllCustomVars.sProductRef
FROM qryFindAllCustomVars
GROUP BY qryFindAllCustomVars.sProductRef
PIVOT qryFindAllCustomVars.sName;this then gives you the
Product Code in the 1st column
each of the required CustomVars in the other column-titles
and each of the values in the actual table
so you end up with a nice little table of you Product-CustomVars


HTML Code:
Ref SupCode Supplier LeadTime MinQty SupplierPrice
----------------------------------------------------------------------
Test1 X123er Maplins 3wk 4 12.60
Test2 ABC123 BandQ 4d 1 3.40

By changing the WHERE clause in the first query, you will get more (or less) columns in the second query

its now a simple exercise to turn this into a report.

[Jan]

TMLVariables.nVariableID

should by HTMLVariables.nVariableID

Regards.

[completerookie]

I've just seen this thread, and I've also just made a comment on the original thread suggesting that you split the queries - the comments apply here just as much.

kev

[paulbeckett]

Hi

Many thanks Jan/Kevin. I have now made a query that calls selected information from qryFindAllCustomVars_Crosstab and created a list exactly how I need it.

I can now create different queries that refer to qryFindAllCustomVars_Crosstab.

Many thanks for you help.

Paul