Missing Expression

[Guest]

I have a standalone query that works. When I cut and paste it into an
existing query that works I get an error. Access says I have
'tried to execute a query that does not include the specified expression
'MachineDesignator' as part of an aggregate function"
I'm a newbie and this is my first try. Any
idea what 's wrong? (The new part is at the end of SELECT DISTINCTROW Max-Min.

SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.Date, Max([MachineUsage])-Min([MachineUsage]) AS
[Usage]
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineDesignator)=[Enter machine designator (ie CL01,
CM02, CLM3)]) AND ((Usage.Date) Between [First date of interest (MM/DD/YY)]
And [Last date of interest (MM/DD/YY)]));

Thanks.

Phil

 

[Douglas J. Steele]

Any time you use functions such as Min or Max in a SQL statement, you must
include a GROUP BY clause listing all of the other parts of the query:

SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.[Date], Max([MachineUsage])-Min([MachineUsage])
AS
[Usage]
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineDesignator)=[Enter machine designator (ie CL01,
CM02, CLM3)]) AND ((Usage.Date) Between [First date of interest (MM/DD/YY)]
And [Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.[Date]

BTW, Date is a reserved word, so you should use it for your own purposes. If
you cannot (or will not) change the field name, at least put it in square
brackets, like I did above. For more details about reserved words, see what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[Guest]

The error is gone, but I'm not getting the results I want. I'll think about
it tonight.

Thanks.

Phil

Any time you use functions such as Min or Max in a SQL statement, you must
include a GROUP BY clause listing all of the other parts of the query:

SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.[Date], Max([MachineUsage])-Min([MachineUsage])
AS
[Usage]
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineDesignator)=[Enter machine designator (ie CL01,
CM02, CLM3)]) AND ((Usage.Date) Between [First date of interest (MM/DD/YY)]
And [Last date of interest (MM/DD/YY)]))
GROUP BY Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.[Date]

BTW, Date is a reserved word, so you should use it for your own purposes. If
you cannot (or will not) change the field name, at least put it in square
brackets, like I did above. For more details about reserved words, see what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a standalone query that works. When I cut and paste it into an
existing query that works I get an error. Access says I have
'tried to execute a query that does not include the specified expression
'MachineDesignator' as part of an aggregate function"
I'm a newbie and this is my first try. Any
idea what 's wrong? (The new part is at the end of SELECT DISTINCTROW
Max-Min.

SELECT DISTINCTROW Machines.MachineDesignator, Machines.MachineName,
Machines.MachineType, Usage.Date, Max([MachineUsage])-Min([MachineUsage])
AS
[Usage]
FROM Machines INNER JOIN [Usage] ON Machines.MachineDesignator =
Usage.MachineDesignator
WHERE (((Machines.MachineDesignator)=[Enter machine designator (ie CL01,
CM02, CLM3)]) AND ((Usage.Date) Between [First date of interest
(MM/DD/YY)]
And [Last date of interest (MM/DD/YY)]));

Thanks.

Phil