Selection query - looking for the correct syntax

[leifoet]

TableA = names
TableB = activities + names
Target : list with all the names+total activities and for each name followed by the detailrecords of the activities
(1) the first line = the first name (from tableA)+ total activities for this name (from tableB)
(2) the following line(s) under this nameline(s) : all the (detail) records of the activities for that name (from tableB)
idem for the 2nd name, the 3rd etc. ... EOF names

fp_sQry="SELECT tableB.*, tableA.Ldnnr, tableA.Lname, tableA.Fname,
(Select Sum(AntlAh) FROM tableB) AS [TTAntlAh], '=> total of the whole list = of all the names/activities
(Select Sum(AntlAh) FROM tableB WHERE FnaamAh=[tabelA].Lname) AS [TAntlAh] '=> this total comes in the line(s) (1)
FROM tableA
WHERE tableA.Ldnnr >= 1 '=> selection of the names
LEFT JOIN tableB ON tableA.Lname=tableB.LnameAh
ORDER BY tableA.Lname, tableA.Fname ASC"

Database Results Error
Description: Syntax error (missing operator) in query expression 'tableA.Ldnnr>=1 LEFT JOIN tableB ON tableA.Lname=tableB.LnameAh'.
Number: -2147217900 (0x80040E14)
Source: Microsoft JET Database Engine

What will be the missing operator ? or other possible errors ?
Thanks for tips - Leifoet

 

[ChrisHirst]

If this is supposed to be a comment in the ASP code ....

(Select Sum(AntlAh) FROM tableB) AS [TTAntlAh], '=> total of the whole list = of all the names/activities

.... You can't do that in the middle of a database query.



And to 'debug' a concatenated query .... response.write the whole query to the browser so you can see what is wrong with it.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[leifoet]

Sorry - I thought, this is part of my ASP page. In which forum should it be?
Thanks - Leifoet

 

[ChrisHirst]

Sorry - I thought, this is part of my ASP page.

Sure; but it is the database server that is throwing the error, so without seeing the actual query as it is being sent to the database server, it is impossible to say what is at fault or where it may be.

Your code is jumbled with 'comments' that WILL 'break' the query structure, but with no indication of whether you have added them for annotation here or whether they are part of the original code.

As to what forum, ... ... no idea,

This bit means;
"Source: Microsoft JET Database Engine"

It could be Access or it could be MSSQL Server


Also the parts of the query you have provided are of little use without the structure of the table so we can't even tell if it a spelling error somewhere.


The error message points to this part of the code:

WHERE tableA.Ldnnr >= 1 '=> selection of the names

BUT have you added the comment part

'=> selection of the names

In the code you provided here ONLY???

OR ........ ?????




Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[leifoet]

'=> selection of the names

indeed comments for the purpose of the forum ...

While editing the (difficult) query I adjusted the target table as follows :
I print all the records (activities) from tableB ordered by the name(s) of tabelA
The total (activities) for each name is also displayed in the first record (of each name)

With this query its works fine

fp_sQry="SELECT tableB.*, tableA.Ldnnr, tableA.Lname, tableA.Fname,
(Select Sum(AntlAh) FROM TableB) AS [TTTAntlAh],
(Select Sum(AntlAh) FROM TableB WHERE LnameAh=[tableA].Lname) AS [TAntlAh]
FROM tableA
LEFT JOIN tabelB ON tableA.Lname=tableB.LnameAh
ORDER BY TableA.Lname, TableB.DateAh ASC"

Thanks - Leifoet

 

[ChrisHirst]

Table structure???

Database server type???

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[dhookom]

With this query its works fine

fp_sQry="SELECT tableB.*, tableA.Ldnnr, tableA.Lname, tableA.Fname,
(Select Sum(AntlAh) FROM TableB) AS [TTTAntlAh],
(Select Sum(AntlAh) FROM TableB WHERE LnameAh=[tableA].Lname) AS [TAntlAh]
FROM tableA
LEFT JOIN tabelB ON tableA.Lname=tableB.LnameAh
ORDER BY TableA.Lname, TableB.DateAh ASC"

I'm surprised it works fine since tableB is entered as tabelB.

Can you just provide the code and answer ChrisHirst's questions?

Duane
Hook'D on Access
MS Access MVP

 

[MarkSweetland]

The WHERE clause must come after all the JOINS:

SELECT 
	tableB.*, 
	tableA.Ldnnr, 
	tableA.Lname, 
	tableA.Fname, 
	[TTTAntlAh] =(Select Sum(AntlAh) FROM TableB), 
	[TAntlAh]= (Select Sum(AntlAh) FROM TableB WHERE LnameAh=[tableA].Lname) 
FROM tableA 
	LEFT JOIN tabelB ON tableA.Lname=tableB.LnameAh 
WHERE tableA.Ldnnr >= 1
ORDER BY TableA.Lname, TableB.DateAh ASC

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[leifoet]

Just change the place of JOIN and WHERE and it works - how easy when you know that !
Thanks MarkSweetland, this is the (correct) syntax I was looking for.
Leifoet