date filtering errors

[nerd19]

Im trying to create a report that will give the most current setup for
a machine. The report should display each machine's number and each
stage (the type) of tool with a serial number of the the date it was
installed. The query i am working off of is a query of another table
so this is a query of a query. Here is the code for the query:

SELECT [Machine Query].Machine, [Machine Query].Type, First([Machine
Query].Date) AS FirstOfDate, [Machine Query].[Serial In]
FROM [Machine Query]
GROUP BY [Machine Query].Machine, [Machine Query].Type, [Machine
Query].[Serial In]
ORDER BY [Machine Query].Machine, [Machine Query].Type, First([Machine
Query].Date) DESC;


As you can see, the way i have filtered this is by the number of each
machine in ascending order, then by each stage in ascending order then
by each date that the machine was serviced in descending order then it
gives the serial. The problem with this is even though i am using the
FIRST command for Types and Dates, which should when combined give the
most recent record for each type, however i am not getting the most
recent record for that type, it will give all records. Anyone know
what im doing wrong?

I am using ms access 2002

 

[Jaazaniah]

Im trying to create a report that will give the most current setup for
a machine. The report should display each machine's number and each
stage (the type) of tool with a serial number of the the date it was
installed. The query i am working off of is a query of another table
so this is a query of a query. Here is the code for the query:

SELECT [Machine Query].Machine, [Machine Query].Type, First([Machine
Query].Date) AS FirstOfDate, [Machine Query].[Serial In]
FROM [Machine Query]
GROUP BY [Machine Query].Machine, [Machine Query].Type, [Machine
Query].[Serial In]
ORDER BY [Machine Query].Machine, [Machine Query].Type, First([Machine
Query].Date) DESC;

As you can see, the way i have filtered this is by the number of each
machine in ascending order, then by each stage in ascending order then
by each date that the machine was serviced in descending order then it
gives the serial. The problem with this is even though i am using the
FIRST command for Types and Dates, which should when combined give the
most recent record for each type, however i am not getting the most
recent record for that type, it will give all records. Anyone know
what im doing wrong?

I am using ms access 2002

My piece of advice is change First to Max. This will return the
highest date for the other fields (the most recent), where as First
will only return the first record found for the other fields (Quite
possibly the oldest).

Hope you can find help on returning all records, as I'm out of time.

 

[nerd19]

Im trying to create a report that will give the most current setup for
a machine. The report should display each machine's number and each
stage (the type) of tool with a serial number of the the date it was
installed. The query i am working off of is a query of another table
so this is a query of a query. Here is the code for the query:

SELECT [Machine Query].Machine, [Machine Query].Type, First([Machine
Query].Date) AS FirstOfDate, [Machine Query].[Serial In]
FROM [Machine Query]
GROUP BY [Machine Query].Machine, [Machine Query].Type, [Machine
Query].[Serial In]
ORDER BY [Machine Query].Machine, [Machine Query].Type, First([Machine
Query].Date) DESC;

As you can see, the way i have filtered this is by the number of each
machine in ascending order, then by each stage in ascending order then
by each date that the machine was serviced in descending order then it
gives the serial. The problem with this is even though i am using the
FIRST command for Types and Dates, which should when combined give the
most recent record for each type, however i am not getting the most
recent record for that type, it will give all records. Anyone know
what im doing wrong?

I am using ms access 2002

My piece of advice is change First to Max. This will return the
highest date for the other fields (the most recent), where as First
will only return the first record found for the other fields (Quite
possibly the oldest).

Hope you can find help on returning all records, as I'm out of time.- Hide quoted text -

- Show quoted text -

I still seem to get the same results