Access SQL Group By problem 1

[ronaldmacdonald]

Hello

I have the following query, I wish to limit the results to
MAX(AS_RecordID) and group by AS_TenancyRef but I get the error "You tried to execute a query that does not include the specified expression 'AS_Date' as part of an aggregate function"

SELECT max(AS_RecordID), AS_TenancyRef, AS_Date, AS_Period, AS_TransType, AS_TransDesc, AS_Debit, AS_Credit, AS_VAT, AS_Balance, Tenancy_TenantID, Property_PropType, Property_Location, Location, Location_Code, Location_Type FROM tblLocationRef INNER JOIN (tblProperty INNER JOIN (tblTenancy INNER JOIN tblAccount_Summary ON tblTenancy.Tenancy_TenancyRef = tblAccount_Summary.AS_TenancyRef) ON tblProperty.Property_PropertyID = tblTenancy.Tenancy_PropertyID) ON tblLocationRef.Location_Code = tblProperty.Property_Location WHERE AS_Balance > 0 AND Location_Type = 'HULL' AND AS_Date >= #01/01/1900# AND AS_Date <= #31/12/2099#
group by AS_TenancyRef

Cheers

Ronald

 

[Golom]

A GROUP BY clause requires that you include all fields from the select clause that do not participate in a aggregate function (i.e. everything except AS_RecordID in your case.)

You could do that and the query would run ... however ... it would probably not give you the results that you expect.

In essence, a GROUP BY concatenates all the fields in the GROUP BY and produces a separate record for each unique value of those concatenated fields. If you put all 15 fields in the GROUP BY then it is likely that each individual record will be unique and the MAX(AS_RecordID) will return the record number for that individual record.

If you can explain a bit about what you want to see as a result then we may be able to make some more concrete suggestions.

 

[ronaldmacdonald]

Hello golum - yes you are correct, it doesn't give the results I wanted :-(

To illustrate a bit more what I am trying to achieve I shall limit the data to the two main coluns concerned.

Currently the query produces this:

AS_RecordID AS_TenancyRef
387 10783001-0002
430 10783001-0002
642 10783001-0002
723 10783001-0002
388 10783002-0004
505 10783002-0004
643 10783002-0004
389 10783003-0004
431 10783003-0004
644 10783003-0004
724 10783003-0004


I wish to only retrieve the row with the highest AS_RecordID
for each unique AS_TenancyRef

Using the above I should see:

AS_RecordID AS_TenancyRef
723 10783001-0002
643 10783002-0004
724 10783003-0004


Hence why I was attempting to use MAX(AS_RecordID) and GROUP BY on the AS_TenancyRef


Cheers

RonaldM

 

[Golom]

That's sorta what I thought you were after but just a bit more clarification is needed.

Your original SQL includes a where clause

WHERE AS_Balance > 0 AND  Location_Type = 'HULL' AND AS_Date >= #01/01/1900# AND AS_Date <= #31/12/2099#

That means that the MAX(AS_RecordID) for a particular AS_TenancyRef will be the maximum one that is left after the conditions in the WHERE clause are satisfied. Assuming that's what you want to happen.

SELECT AS_RecordID, AS_TenancyRef, AS_Date, AS_Period, AS_TransType, AS_TransDesc, AS_Debit, AS_Credit, AS_VAT, AS_Balance, Tenancy_TenantID, Property_PropType, Property_Location, Location, Location_Code, Location_Type 

FROM (tblLocationRef INNER JOIN (tblProperty INNER JOIN (tblTenancy INNER JOIN tblAccount_Summary ON tblTenancy.Tenancy_TenancyRef = tblAccount_Summary.AS_TenancyRef) ON tblProperty.Property_PropertyID = tblTenancy.Tenancy_PropertyID) ON tblLocationRef.Location_Code = tblProperty.Property_Location )
[blue]
WHERE AS_RecordID IN 

(SELECT MAX(AS_RecordID) As MX
FROM tblLocationRef As L INNER JOIN (tblProperty As P INNER JOIN (tblTenancy As T INNER JOIN tblAccount_Summary As A ON T.Tenancy_TenancyRef = A.AS_TenancyRef) ON P.Property_PropertyID = T.Tenancy_PropertyID) ON L.Location_Code = P.Property_Location 
WHERE AS_Balance > 0 AND Location_Type = 'HULL' AND AS_Date BETWEEN #01/01/1900# AND #31/12/2099# 
GROUP BY AS_TenancyRef) 
[/blue]

where the stuff in [blue]blue[/blue] just produces a list of the maximum AS_RecordID values that qualify. You can also do this with an INNER JOIN or a coordinated sub-query but those are even messier constructions.

 

[ronaldmacdonald]

My goodness Golum, you are genius! That is it! You have managed to do in about 15 minutes what I have spent a day trying to do (and still failed!). You made it look a bit more intelligible as well with the formatting. I was suprised anyone could deconstruct my crazy query - well done and thanks!