need to rank in a query

[pat67]

Hi all here is my problem. I need to find the last vendor for a part
that was bought. I have a table with all of the receipts. The problem
is if i group by the part and vendor and look for the max delivery
date, i get the last time the part was bought for each vendor it was
bought from. i want the last vendor period. example


part ABC was bought from vendor 1 on 8/23, vendor 2 on 8/12 and vendor
3 on 7/25, when i run the query i get this

Part Vendor MaxofDate
ABC 1 8/23
ABC 2 8/12
ABC 3 7/25

all i want to see is this

Part Vendor MaxofDate
ABC 1 8/23

can someone explain how to do this for me?

Thanks

 

[John W. Vinson]

Hi all here is my problem. I need to find the last vendor for a part
that was bought. I have a table with all of the receipts. The problem
is if i group by the part and vendor and look for the max delivery
date, i get the last time the part was bought for each vendor it was
bought from. i want the last vendor period. example


part ABC was bought from vendor 1 on 8/23, vendor 2 on 8/12 and vendor
3 on 7/25, when i run the query i get this

Part Vendor MaxofDate
ABC 1 8/23
ABC 2 8/12
ABC 3 7/25

all i want to see is this

Part Vendor MaxofDate
ABC 1 8/23

can someone explain how to do this for me?

Thanks

Use a Subquery, to select the record with the maximum date, rather than a
Totals query grouping by vendor and selecting Max. Guessing at your table and
fieldnames it would be something like

SELECT Part, Vendor, [Date] FROM tablename
WHERE [Date] = (SELECT Max(X.[Date]) FROM tablename AS X WHERE X.[Part] =
tablename.[Part]);

Note that Date is a reserved word for the builtin Date() function; I'd suggest
changing the fieldname to SaleDate or some other non-reserved term.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[pat67]

Hi all here is my problem. I need to find the last vendor for a part
that was bought. I have a table with all of the receipts. The problem
is if i group by the part and vendor and look for the max delivery
date, i get the last time the part was bought for each vendor it was
bought from. i want the last vendor period. example

part ABC was bought from vendor 1 on 8/23, vendor 2 on 8/12 and vendor
3 on 7/25, when i run the query i get this

Part    Vendor  MaxofDate
ABC       1        8/23
ABC       2        8/12
ABC       3        7/25

all i want to see is this

Part    Vendor  MaxofDate
ABC       1        8/23

can someone explain how to do this for me?

Thanks

Use a Subquery, to select the record with the maximum date, rather than a
Totals query grouping by vendor and selecting Max. Guessing at your tableand
fieldnames it would be something like

SELECT Part, Vendor, [Date] FROM tablename
WHERE [Date] = (SELECT Max(X.[Date]) FROM tablename AS X WHERE X.[Part]=
tablename.[Part]);

Note that Date is a reserved word for the builtin Date() function; I'd suggest
changing the fieldname to SaleDate or some other non-reserved term.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

I think that may have worked. I am not sure since there is so much
data. but thanks.

 

[pat67]

Use a Subquery, to select the record with the maximum date, rather thana
Totals query grouping by vendor and selecting Max. Guessing at your table and
fieldnames it would be something like

SELECT Part, Vendor, [Date] FROM tablename
WHERE [Date] = (SELECT Max(X.[Date]) FROM tablename AS X WHERE X.[Part] =
tablename.[Part]);

Note that Date is a reserved word for the builtin Date() function; I'd suggest
changing the fieldname to SaleDate or some other non-reserved term.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com-Hide quoted text -

- Show quoted text -

I think that may have worked. I am not sure since there is so much
data. but thanks.- Hide quoted text -

- Show quoted text -

I see one issue and that is if a part was received on the same day by
a different vendor. Is there a way around that?

 

[John Spencer]

Not unless you record the date and time in the field.

Is there a way that you can tell from the data in the table which delivery was
the latest? If not, then there is no way to reduce the selection to just one
record for that part.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi all here is my problem. I need to find the last vendor for a part
that was bought. I have a table with all of the receipts. The problem
is if i group by the part and vendor and look for the max delivery
date, i get the last time the part was bought for each vendor it was
bought from. i want the last vendor period. example
part ABC was bought from vendor 1 on 8/23, vendor 2 on 8/12 and vendor
3 on 7/25, when i run the query i get this
Part Vendor MaxofDate
ABC 1 8/23
ABC 2 8/12
ABC 3 7/25
all i want to see is this
Part Vendor MaxofDate
ABC 1 8/23
can someone explain how to do this for me?
Thanks
Use a Subquery, to select the record with the maximum date, rather than a
Totals query grouping by vendor and selecting Max. Guessing at your table and
fieldnames it would be something like
SELECT Part, Vendor, [Date] FROM tablename
WHERE [Date] = (SELECT Max(X.[Date]) FROM tablename AS X WHERE X.[Part] =
tablename.[Part]);
Note that Date is a reserved word for the builtin Date() function; I'd suggest
changing the fieldname to SaleDate or some other non-reserved term.
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com-Hide quoted text -
- Show quoted text -

I think that may have worked. I am not sure since there is so much
data. but thanks.- Hide quoted text -

- Show quoted text -

I see one issue and that is if a part was received on the same day by
a different vendor. Is there a way around that?