Problem with SQL statement

[Brad]

What I am trying to accomplish is to grab the first N number of records, group by name, and total the qty value. The below statement grabs all of the records but only displays the first N number of grouped and totaled records. I tried moving the LIMIT statement around but kept getting errors. Was wondering if anyone could see what I am doing wrong.

sSQL = "SELECT name, qty, SUM(qty) as qty FROM items GROUP BY name ORDER BY qty DESC LIMIT " & "'" & iNum & "'" & """

My thought is to use the following statement to grab N number of records, dumping the results into a new table then group and total from that. I hope to avoid the extra step if my above code will work.

sSQL = "Select name, qty, FROM items LIMIT " & "'" & iNum & "'" & """

 

[Djembefola]

A Number is expected, not a String.

Change

sSQL = "Select name, qty, FROM items LIMIT " & "'" & iNum & "'" & """

to:

sSQL = "Select name, qty, FROM items LIMIT " &  iNum

 

[Brad]

That makes sense but what is strange is that it works using my code.

 

[Brad]

Got it working by inserting the raw data into a second table using the LIMIT keyword then ran a query against the second table that grouped and totaled the qty. Also cleaned up my sql statements.

@Dejembefola - Your example didn't work even though qty field is stored as a real. It grabbed all of the records in the table. I went ahead and used

sSQL = "Select name, qty, FROM items LIMIT '" & iNum & "'" & """