SQL problem

[John Sh]

Public Function getBoxContent()
    oDB.Execute "DELETE * FROM counted", dbFailOnError
    sQry = "INSERT INTO counted ( Family, Infra, box, collection, Specimens ) " _
           & " SELECT DISTINCT A.Family, A.infra, A.BoxNo, A.Collection, Count(A.BoxNo) AS count " _
           & " FROM boxes as A " _
           & " WHERE NZ(A.Family,'') > '' AND A.boxno > 0 and InStr(A.Collection,'SC-') = 0;"
    oDB.Execute sQry, dbFailOnError
End Function

This sql string returns error 3122 '"Family is not part of an aggregate function"

 

[plog]

1. You make it so much harder to debug SQL when you have it in VBA. It's like a game of telephone you have to play to get to the SQL. What exactly is the SQL in sQry?

2. Why is there an alias in that thing? That makes it even more harder too. If there's only one datasource in the FROM, there's no need to reference a datasource elsewhere in the SQL. And if you ask me, there's little need for aliases at all in the FROM.

3. The problem is that when you use an aggregate function in the SELECT (e.g. COUNT, MAX, MIN, etc.) you need to use a GROUP BY if you have other fields in the SELECT. That means instead of DISTINCT, you need to have a GROUP BY that contains all the other fields in the SELECT that are not in an aggregate function.

 

[John Sh]

1. You make it so much harder to debug SQL when you have it in VBA. It's like a game of telephone you have to play to get to the SQL. What exactly is the SQL in sQry?

2. Why is there an alias in that thing? That makes it even more harder too. If there's only one datasource in the FROM, there's no need to reference a datasource elsewhere in the SQL. And if you ask me, there's little need for aliases at all in the FROM.

3. The problem is that when you use an aggregate function in the SELECT (e.g. COUNT, MAX, MIN, etc.) you need to use a GROUP BY if you have other fields in the SELECT. That means instead of DISTINCT, you need to have a GROUP BY that contains all the other fields in the SELECT that are not in an aggregate function.

3. The problem is that when you use an aggregate function in the SELECT (e.g. COUNT, MAX, MIN, etc.) you need to use a GROUP BY if you have other fields in the SELECT. That means instead of DISTINCT, you need to have a GROUP BY that contains all the other fields in the SELECT that are not in an aggregate function.

Thank you.
I would have thought that anything between the "Insert into " and ";"" was fairly obviously the sql statement.
My apologies if this was too complex for you.
John

 

[plog]

Apology and thanks accepted.

When you want to hardcode SQL inside VBA you should first get your SQL correct by using the query designer. That's the tool to use to debug your SQL. Once it works you can extract the SQL and paste it into your VBA code.

 

[GustavBrock]

Access SQL can't combine DISTINCT and aggregation. Try this:

Public Function getBoxContent()

    oDB.Execute "DELETE * FROM counted", dbFailOnError

    sQry = "INSERT INTO counted ( Family, Infra, box, collection, Specimens ) " _
           & " SELECT A.Family, A.infra, A.BoxNo, A.Collection, Count(A.BoxNo) AS Speciments " _
           & " FROM boxes AS A " _
           & " WHERE NZ(A.Family,'') <> '' AND InStr(A.Collection,'SC-') = 0 " _
           & " GROUP BY A.Family, A.infra, A.BoxNo, A.Collection " _
           & " HAVING A.BoxNo > 0 "
    oDB.Execute sQry, dbFailOnError

End Function

 

[cheekybuddha]

The real question is why you need to store this info in a table (counted) when the SELECT query you use to populate it will always give you the answer in the first place, and is less likely to be out of date.

 

[MarkK]

Agreed. No good reason to dump query results into a table. Just use the query directly.