SQL problem (2 Viewers)

[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.

 

[John Sh]

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

Thanks. Already sorted this one out

 

[John Sh]

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

This is just one of a string of queries that generate a table that lists all the errors from an inherited database.
Many of the errors refer to duplicate locations of images. incorrect genus and family combinations and a multitude of other errors.
The inherited database had only text controls and no checks on data entry. That plus entry was directly into the tables.
I have since fixed the entry onto forms with appropriate checks on any data entered so there are no new errors being created.
The reason for this new form is to get all of the still existing errors onto one form is so they are repairable but also they are evident.

What it boils down to is what you see is not always what you get.
John

 

[MarkK]

This is just one of a string of queries that generate a table

If you have a working query, there is no advantage to dump its results into a table. To any consuming process, the query itself is indistinguishable from a table and, since it queries the live data, it's guaranteed to be current.
When you render it out as an actual table, you create these liabilities...
• you must immediately suspect it of being out of date.
• it clutters your workspace offering no additional value.
• to use it again, you must delete its contents.
And here are the advantages...
•