Select only the rows I need.

[Guest]

I have a table that I need to pull only the max date for each item. I will
give an example below.

Item Unit of Measure Effective Date
3 EA 8/8/2006
3 EA 2/5/2006
3 EA 3/21/2005

5 EA 10/21/2006
5 EA 8/26/2006

With this example, the result set I would be looking for would be:

Item Unit of Measure Effective Date
3 EA 8/8/2006
5 EA 10/21/2006

I have tried several methods with the DMAX function and used SQL, but I
can't seem to get a handle on it.

Any help would be greatly appreciated.

Thank you
fvlmasl2

 

[storrboy]

I have a table that I need to pull only the max date for each item. I will
give an example below.

Item Unit of Measure Effective Date
3 EA 8/8/2006
3 EA 2/5/2006
3 EA 3/21/2005

5 EA 10/21/2006
5 EA 8/26/2006

With this example, the result set I would be looking for would be:

Item Unit of Measure Effective Date
3 EA 8/8/2006
5 EA 10/21/2006

I have tried several methods with the DMAX function and used SQL, but I
can't seem to get a handle on it.

Any help would be greatly appreciated.

Thank you
fvlmasl2

SELECT tn.[Item], tn.[Unit Of Measure], Max(tn.[Effective Date]) AS
MaxOfEDate
FROM tableName AS tn
GROUP BY tn.[Item], tn.[Unit Of Measure];

 

[Guest]

Yes you are correct, this does work, but please forgive me. I forgot to add
a field in the examples. The field is UPC CODE. I have added in the
original example.

Thanks again,

I have a table that I need to pull only the max date for each item. I will
give an example below.

Item Unit of Measure UPC Effective Date
3 EA 111 8/8/2006
3 EA 222 2/5/2006
3 EA 333 3/21/2005

5 EA 888 10/21/2006
5 EA 999 8/26/2006

With this example, the result set I would be looking for would be:

Item Unit of Measure UPC ffective Date
3 EA 111 8/8/2006
5 EA 888 10/21/2006

I have tried several methods with the DMAX function and used SQL, but I
can't seem to get a handle on it.

Any help would be greatly appreciated.

Thank you
fvlmasl2

SELECT tn.[Item], tn.[Unit Of Measure], Max(tn.[Effective Date]) AS
MaxOfEDate
FROM tableName AS tn
GROUP BY tn.[Item], tn.[Unit Of Measure];

 

[John W. Vinson]

I have a table that I need to pull only the max date for each item. I will
give an example below.

Item Unit of Measure Effective Date
3 EA 8/8/2006
3 EA 2/5/2006
3 EA 3/21/2005

5 EA 10/21/2006
5 EA 8/26/2006

With this example, the result set I would be looking for would be:

Item Unit of Measure Effective Date
3 EA 8/8/2006
5 EA 10/21/2006

I have tried several methods with the DMAX function and used SQL, but I
can't seem to get a handle on it.

Just a Max([Effective Date]) won't work, as it will get the max date
irrespective of the other fields. You'll need a Subquery instead: use a
criterion on [Effective Date] of

=(SELECT Max([Effective Date]) FROM yourtable AS X WHERE X.Item =
yourtable.Item)


John W. Vinson [MVP]

 

[storrboy]

Just a Max([Effective Date]) won't work, as it will get the max date
irrespective of the other fields. You'll need a Subquery instead: use a
criterion on [Effective Date] of

It worked for me.

 

[storrboy]

Just a Max([Effective Date]) won't work, as it will get the max date
irrespective of the other fields. You'll need a Subquery instead: use a
criterion on [Effective Date] of

Worked for me. 3 Columns: Item, UOM, EDate
Test Values:

1,m,2/12/07
1,m,2/15/07
1,m,2/9/07
2,m,2/9/07
2,m,2/10/07
3,m,2/11/07
3,m,2/12/07

Results:

1,m,2/15/07
2,m,2/10/07
3,m,2/12/07

 

[Guest]

What does the formula look like???

Just a Max([Effective Date]) won't work, as it will get the max date
irrespective of the other fields. You'll need a Subquery instead: use a
criterion on [Effective Date] of

Worked for me. 3 Columns: Item, UOM, EDate
Test Values:

1,m,2/12/07
1,m,2/15/07
1,m,2/9/07
2,m,2/9/07
2,m,2/10/07
3,m,2/11/07
3,m,2/12/07

Results:

1,m,2/15/07
2,m,2/10/07
3,m,2/12/07

 

[storrboy]

What does the formula look like???

The one I already posted. Dummied up a table and fields to test the
query since I have no idea of the structure of yours.

SELECT tn.Item, tn.[Unit Of Measure], Max(tn.[Effective Date]) AS
MaxOfEDate
FROM Table1 AS tn
GROUP BY tn.Item, tn.[Unit Of Measure];

 

[John W. Vinson]

What does the formula look like???

The one I already posted. Dummied up a table and fields to test the
query since I have no idea of the structure of yours.

SELECT tn.Item, tn.[Unit Of Measure], Max(tn.[Effective Date]) AS
MaxOfEDate
FROM Table1 AS tn
GROUP BY tn.Item, tn.[Unit Of Measure];

In a later post fvlmasl2 added a wrinkle: to quote -

Yes you are correct, this does work, but please forgive me. I forgot to add
a field in the examples. The field is UPC CODE. I have added in the
original example.

Thanks again,


You would NOT be able to Group By UPC (or you'ld be back to all five rows);
and if you want to see the UPC value associated with the latest effective
date, you do in fact need the subquery.

You're quite right for the question as originally posted, though!

John W. Vinson [MVP]

 

[storrboy]

I missed the UPC part. That post came through after your reply.

 

[Guest]

I do apologize, I added the UPC after the fact. I assume that this cannot be
done with a single query? A second should be used?

 

[storrboy]

As Mr. Vinson said...You would NOT be able to Group By UPC (or you'ld be back to all five
rows);
and if you want to see the UPC value associated with the latest
effective
date, you do in fact need the subquery
When you group on fields, each record that differs is shown on it's
own, same records are grouped. UPC is likely to be different for a
good many rows if not all, so you'd get a row for every different UPC
regardless of the rest being the same.