Solved Cant get past this aggregate function error (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 11:17
Joined
Sep 17, 2001
Messages
939
Hi everyone,

I'm trying to add a field to my continuous form that will show the last record entered for Km or Hours for vehicles.

I added the table to my form query so the sequel looks like this:

Code:
SELECT DISTINCT Machine.MachineID, Machine.ManufacturerRef, Machine.MechanicID, Machine.Type, Machine.Manufacturer, Machine.Model, Machine.RegSerialNo, Machine.VINNumber, Machine.Status, Machine.Location, Machine.OutOfServiceDate, Machine.BackInServiceDate, Machine.PersonResponsible, Machine.CategoryID, Machine.Documents, Machine.Insurance, Machine.Tax, Category.Category, Status.Status, Status.Colour, Type.Type, Manufacturer.ManufacturerName, Location.[Current Location], Location.[Current Location], Machine.CurrentOperator, Machine.Delete, ServiceHistory.KmHours

FROM (Category RIGHT JOIN (Mechanic RIGHT JOIN (Manufacturer RIGHT JOIN (((Machine LEFT JOIN Status ON Machine.Status = Status.StatusID) LEFT JOIN Type ON Machine.Type = Type.TypeID) LEFT JOIN Location ON Machine.Location = Location.LocationID) ON Manufacturer.ManufacturerID = Machine.ManufacturerRef) ON Mechanic.PersonID = Machine.MechanicID) ON Category.CategoryID = Machine.CategoryID) INNER JOIN ServiceHistory ON Machine.MachineID = ServiceHistory.MachineID

WHERE (((Machine.Delete)=No) AND ((ServiceHistory.KmHours)=Last("KmHours")))

ORDER BY Machine.Type, Machine.Model;

But i am getting the error message attached when i try and run it:

I will keep trying to figure it out but if anyone can assist?

Many thanks in advance
 

Attachments

  • Screenshot 2021-06-20 121514.png
    Screenshot 2021-06-20 121514.png
    6.1 KB · Views: 351

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:17
Joined
May 7, 2009
Messages
19,175
create a new query against serviceHistory table (qryLastKMHrs).

select machineID, Dmax([KmHours]) As LastKmHours from serviceHistory group by machineID;

join this to your query (post#1 join with serviceHistory) (on ServiceHistory.KmHours=qryLastKMHrs.LastKmHours)
 

Sam Summers

Registered User.
Local time
Today, 11:17
Joined
Sep 17, 2001
Messages
939
create a new query against serviceHistory table (qryLastKMHrs).

select machineID, Dmax([KmHours]) As LastKmHours from serviceHistory group by machineID;

join this to your query (post#1 join with serviceHistory) (on ServiceHistory.KmHours=qryLastKMHrs.LastKmHours)
Hi Arnel once again.

Just trying to figure out exactly where to put this:

join this to your query (post#1 join with serviceHistory) (on ServiceHistory.KmHours=qryLastKMHrs.LastKmHours)
 

mike60smart

Registered User.
Local time
Today, 11:17
Joined
Aug 6, 2017
Messages
1,899
Hi Sam
Arnel is saying create the following query:-

Select machineID, Dmax([KmHours]) As LastKmHours from serviceHistory group by machineID;

Then open your previous query in design view and add your New query and join this with ServiceHistory) -
(on ServiceHistory.KmHours=qryLastKMHrs.LastKmHours)
 

Sam Summers

Registered User.
Local time
Today, 11:17
Joined
Sep 17, 2001
Messages
939
Hi Sam
Arnel is saying create the following query:-

Select machineID, Dmax([KmHours]) As LastKmHours from serviceHistory group by machineID;

Then open your previous query in design view and add your New query and join this with ServiceHistory) -
(on ServiceHistory.KmHours=qryLastKMHrs.LastKmHours)
I'm trying to find out how to add that new query to the current query?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:17
Joined
Sep 21, 2011
Messages
14,050
Right Click in the table portion of the query window and click Show Table, then the queries tab and select the query?
 

Sam Summers

Registered User.
Local time
Today, 11:17
Joined
Sep 17, 2001
Messages
939
Hi guys, thank you as always for your help and patience.

Now i am getting this error message?
 

Attachments

  • Screenshot 2021-06-20 135124.png
    Screenshot 2021-06-20 135124.png
    5 KB · Views: 337

Gasman

Enthusiastic Amateur
Local time
Today, 11:17
Joined
Sep 21, 2011
Messages
14,050
Hmm, yes, normally with DMax() you would need at least the domain?
 

Sam Summers

Registered User.
Local time
Today, 11:17
Joined
Sep 17, 2001
Messages
939
qryLastKmHours SQL is this:

Code:
SELECT serviceHistory.machineID, Dmax([KmHours]) AS LastKmHours
FROM serviceHistory
GROUP BY serviceHistory.machineID;

?
 

Users who are viewing this thread

Top Bottom