Solved Family not included in aggregate function (1 Viewer)

[John Sh]

I am in the process of converting sql strings to incorporate "select distinct" and also to use currentdb.execute where appropriate.
As I understand it, "select distinct" eliminates the need for the "Group" clause.
The following code seem to refute that.
What am I doing wrong?
I understand the setwarnings is unnecessary, I'm yet to remove those.

Public Sub getBaySheets()
    DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE * FROM Bay_Sheet"
        sqry = "INSERT INTO bay_sheet ( Family, Infra, Bay, Shelf, FirstBox, LastBox ) " & _
               "SELECT DISTINCT A.Family, A.Infra, A.Bay, A.Shelf, Min(A.Box) AS MinOfBox, Max(A.Box) AS MaxOfBox " & _
               "FROM Counted as A " & _
               "ORDER BY A.Family, A.Infra;"
        CurrentDb.Execute sqry, dbFailOnError
    DoCmd.SetWarnings True
End Sub

removing the alias has no effect on the error.

 

[DHookom]

I believe with aggregate functions like Min or Max, you will need the Group By.

 

[John Sh]

I believe with aggregate functions like Min or Max, you will need the Group By.

Thank you, that fixed it.
Something to look out for.

 

[MarkK]

In common practice, you would not move this data to a temp table. Rather, you would save this SQL...

SELECT DISTINCT A.Family, A.Infra, A.Bay, A.Shelf, Min(A.Box) AS FirstBox, Max(A.Box) AS LastBox
FROM Counted as A
ORDER BY A.Family, A.Infra;

... to a query called "Bay_Sheet." For most purposes, consumers of the temp table "Bay_Sheet" will not distinguish a difference, and the GetBaySheets() method can be deleted.

In almost all cases, store the raw data, and then provide it to consumers by querying it directly from source.

 

[Mark_]

Select Distinct ONLY returns one entry per DISTINCT value.
Normally you use it for when you need to report on distinct items that are not otherwise separate. Think "I need to make a report on cities. I don't have an existing list of cities, but if I do Select Distinct CityName from CustFile I can generate this list for use in a report".