DMAX in query - get record with latest date by vendor

[Steven R via AccessMonster.com]

We have revolving contracts with vendors that roll-over into a new contract
with a new cont# - I want a query that only shows one contract record per
vendor, based on which record has the latest contract end date.

I think DMAX just give the latest record based on the criteria

My fields are:

Vendor Name
Contract Number
StartDate
EndDate

 

[John Spencer (MVP)]

One way is to use a correlated sub-query. See example code below. Replace
"YourTable" with the name of your table and fix any field names that are incorrect.

SELECT Y.*
FROM YourTable as Y
WHERE YourTable.EndDate =
(SELECT Max(T.EndDate)
FROM YourTable as T
WHERE T.[Vendor Name] = Y.[Vendor Name])

 

[Guest]

I have a question
Wouldn't it be better to create a group by query that return a vendor name,
and a max EndDate, and then create another query that will link the query we
created to the table by max date and vendor name.
Isn't the other option, using a select within a select will perform a select
for each record in the table, and that will make the query slower, or it just
the same a the option I gave?

Thanks

One way is to use a correlated sub-query. See example code below. Replace
"YourTable" with the name of your table and fix any field names that are incorrect.

SELECT Y.*
FROM YourTable as Y
WHERE YourTable.EndDate =
(SELECT Max(T.EndDate)
FROM YourTable as T
WHERE T.[Vendor Name] = Y.[Vendor Name])

 

[John Spencer (MVP)]

It may be faster to use the two query approach. It probably will be faster,
however, there is the possibility that (1) the difference in speed will be
insignificant; (2) the query must be run as one query. You could post your
alternative solution.

Also, depending on the naming scheme the user has you could include the subquery
in the FROM Clause. In Access/Jet this will work as long as you don't need to
use the square brackets in the sub-query.

I have a question
Wouldn't it be better to create a group by query that return a vendor name,
and a max EndDate, and then create another query that will link the query we
created to the table by max date and vendor name.
Isn't the other option, using a select within a select will perform a select
for each record in the table, and that will make the query slower, or it just
the same a the option I gave?

Thanks

One way is to use a correlated sub-query. See example code below. Replace
"YourTable" with the name of your table and fix any field names that are incorrect.

SELECT Y.*
FROM YourTable as Y
WHERE YourTable.EndDate =
(SELECT Max(T.EndDate)
FROM YourTable as T
WHERE T.[Vendor Name] = Y.[Vendor Name])

We have revolving contracts with vendors that roll-over into a new contract
with a new cont# - I want a query that only shows one contract record per
vendor, based on which record has the latest contract end date.

I think DMAX just give the latest record based on the criteria

My fields are:

Vendor Name
Contract Number
StartDate
EndDate

 

[Guest]

Thank you

It may be faster to use the two query approach. It probably will be faster,
however, there is the possibility that (1) the difference in speed will be
insignificant; (2) the query must be run as one query. You could post your
alternative solution.

Also, depending on the naming scheme the user has you could include the subquery
in the FROM Clause. In Access/Jet this will work as long as you don't need to
use the square brackets in the sub-query.

I have a question
Wouldn't it be better to create a group by query that return a vendor name,
and a max EndDate, and then create another query that will link the query we
created to the table by max date and vendor name.
Isn't the other option, using a select within a select will perform a select
for each record in the table, and that will make the query slower, or it just
the same a the option I gave?

Thanks

One way is to use a correlated sub-query. See example code below. Replace
"YourTable" with the name of your table and fix any field names that are incorrect.

SELECT Y.*
FROM YourTable as Y
WHERE YourTable.EndDate =
(SELECT Max(T.EndDate)
FROM YourTable as T
WHERE T.[Vendor Name] = Y.[Vendor Name])

:

We have revolving contracts with vendors that roll-over into a new contract
with a new cont# - I want a query that only shows one contract record per
vendor, based on which record has the latest contract end date.

I think DMAX just give the latest record based on the criteria

My fields are:

Vendor Name
Contract Number
StartDate
EndDate

 

[Gary Walter]

It may be faster to use the two query approach. It probably will be
faster,
however, there is the possibility that (1) the difference in speed will be
insignificant; (2) the query must be run as one query. You could post
your
alternative solution.

Also, depending on the naming scheme the user has you could include the
subquery
in the FROM Clause. In Access/Jet this will work as long as you don't
need to
use the square brackets in the sub-query.

I have a question
Wouldn't it be better to create a group by query that return a vendor
name,
and a max EndDate, and then create another query that will link the query
we
created to the table by max date and vendor name.
Isn't the other option, using a select within a select will perform a
select
for each record in the table, and that will make the query slower, or it
just
the same a the option I gave?

Thanks

One way is to use a correlated sub-query. See example code below.
Replace
"YourTable" with the name of your table and fix any field names that
are incorrect.

SELECT Y.*
FROM YourTable as Y
WHERE YourTable.EndDate =
(SELECT Max(T.EndDate)
FROM YourTable as T
WHERE T.[Vendor Name] = Y.[Vendor Name])

PMFBIA

Also, the query should be *updateable*
because John has "hidden" the aggregate
in the WHERE clause.

Apologies again,

gary