INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Selection query - looking for the correct syntax

Selection query - looking for the correct syntax

(OP)
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

RE: Selection query - looking for the correct syntax

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

RE: Selection query - looking for the correct syntax

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


RE: Selection query - looking for the correct syntax

Quote:

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

RE: Selection query - looking for the correct syntax

(OP)

Quote:

'=> 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


RE: Selection query - looking for the correct syntax

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

RE: Selection query - looking for the correct syntax

Quote (Leifoet)

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

RE: Selection query - looking for the correct syntax


The WHERE clause must come after all the JOINS:

CODE

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

RE: Selection query - looking for the correct syntax

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close