QUERY FOR LASTEST OPERATION

[ZigZagZak]

Hi I have a database that tracks where parts are in our shop. I have a table
that we enter where a part was put and it gets taged with the date. I am
trying to make a query to find the latest [date]/[location] for a
[OrderNumber]/[LineItem].

Can anyone help me set up this query?

The plan is to make a report showing department load. I already have a
query finding the average parts through a department. Now I just need how
many are currently in it.

Thanks in advance.

Zach

 

[Jeff Boyce]

Create a new query in design view.

Add the table, add the "partnumber" field, add the date-tagged field.

Click the Totals button ("sigma", looks like a sideways "M").

Use "GroupBy on the "partnumber" field, and "Maximum" on the date-tagged
field.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

Zach:

To return both the location and the latest date per OrderNumber/LineItem
you'll need to use a subquery to find the latest date and use this date to
restrict the outer query, e.g.

SELECT *
FROM [YourTable] AS T1
WHERE [date] =
(SELECT MAX([date])
FROM [YourTable] AS T2
WHERE T2.[ordernumber] = T1.[ordernumber]
AND T2.[lineitem] = T1.[lineitem]);

The aliases T1 and T2 are used to distinguish the two instances of the
table, enabling the subquery to be correlated with the outerquery.

BTW I'd avoid using date as a column name. It’s the name of a built in
function, so its better to use something specific like transactiondate. If
you do use date then be sure to wrap it in square brackets, [date], when
referencing the column in a query or in code.

Ken Sheridan
Stafford, England

 

[ZigZagZak]

Ken,
I gotta say it......YOU ARE THE MAN!!!! Thanks for starting my new year off
right!....(not frustrated!) Have a good day.

Zach

Zach:

To return both the location and the latest date per OrderNumber/LineItem
you'll need to use a subquery to find the latest date and use this date to
restrict the outer query, e.g.

SELECT *
FROM [YourTable] AS T1
WHERE [date] =
(SELECT MAX([date])
FROM [YourTable] AS T2
WHERE T2.[ordernumber] = T1.[ordernumber]
AND T2.[lineitem] = T1.[lineitem]);

The aliases T1 and T2 are used to distinguish the two instances of the
table, enabling the subquery to be correlated with the outerquery.

BTW I'd avoid using date as a column name. It’s the name of a built in
function, so its better to use something specific like transactiondate. If
you do use date then be sure to wrap it in square brackets, [date], when
referencing the column in a query or in code.

Ken Sheridan
Stafford, England

Hi I have a database that tracks where parts are in our shop. I have a table
that we enter where a part was put and it gets taged with the date. I am
trying to make a query to find the latest [date]/[location] for a
[OrderNumber]/[LineItem].

Can anyone help me set up this query?

The plan is to make a report showing department load. I already have a
query finding the average parts through a department. Now I just need how
many are currently in it.

Thanks in advance.

Zach