Only Show records with latest date by vendor

[Steve via AccessMonster.com]

I have query that shows all contracts, and links to the vendor table to
include the vendor name - we only have one "active" contract at a time per
vendor, which would be the one with the latest expiration date - so I want to
filter out old contracts - just show the ones with the latest expiration date
BY VENDOR NAME

Here is my SQL as is, but it shows all contracts - I just want to show one
contract per vendor

================================================================
SELECT CONTRACTS.ContractNum AS ContractNumber, T.Value, T.Type, T.StartDate
FROM CONTRACTS AS T INNER JOIN [Select ContractNum,Max(EndDate) as MxDate
FROM CONTRACTS GROUP BY ContractNum]. AS Mx ON (T.EndDate = Mx.MxDate) AND (T.
ContractNum = Mx.ContractNum);
================================================================

Any help would be greatly appreciated,

Steve

 

[Marshall Barton]

I have query that shows all contracts, and links to the vendor table to
include the vendor name - we only have one "active" contract at a time per
vendor, which would be the one with the latest expiration date - so I want to
filter out old contracts - just show the ones with the latest expiration date
BY VENDOR NAME

Here is my SQL as is, but it shows all contracts - I just want to show one
contract per vendor

================================================================
SELECT CONTRACTS.ContractNum AS ContractNumber, T.Value, T.Type, T.StartDate
FROM CONTRACTS AS T INNER JOIN [Select ContractNum,Max(EndDate) as MxDate
FROM CONTRACTS GROUP BY ContractNum]. AS Mx ON (T.EndDate = Mx.MxDate) AND (T.
ContractNum = Mx.ContractNum);
================================================================

The query looks like it's logically correct, but with all
the aliasing errors I don't see how it can run at all.

If you retyped the real query and those are typos, then I
need to see a Copy/Paste of the real query. Otherwise, go
back and make sure you qualify **every** field with the
appropriate table/alias name.

 

[Steve via AccessMonster.com]

Thanks Marshall,

Here is another version of the SQL without all the aliases - I need to show
all contracts by VENDOR name- if there is more than one contract by Vendor
Name, I need have the query return ONLY the contract with the LATEST date:

SELECT VENDORS.VendorName, Last(CONTRACTS.EndDate) AS LastOfEndDate,
CONTRACTS.ContractNum, CONTRACTS.Value, CONTRACTS.Type, CONTRACTS.StartDate
FROM VENDORS INNER JOIN CONTRACTS ON VENDORS.VendorNum = CONTRACTS.VendorNum
GROUP BY VENDORS.VendorName, CONTRACTS.ContractNum, CONTRACTS.Value,
CONTRACTS.Type, CONTRACTS.StartDate

My fields are:
VendorName (From the VENDORS table)
VendorNum (From the CONTRACTS table and the VENDORS table)
StartDate (From the CONTRACTS table)
EndDate (From the CONTRACTS table)
ContractNum (From the CONTRACTS table)
Type (From the CONTRACTS table)



The alias here came from when I selected "Last" in the criteria of a Sum
Query


Thanks for your input !
Steve

I have query that shows all contracts, and links to the vendor table to
include the vendor name - we only have one "active" contract at a time per

[quoted text clipped - 11 lines]

ContractNum = Mx.ContractNum);
================================================================

The query looks like it's logically correct, but with all
the aliasing errors I don't see how it can run at all.

If you retyped the real query and those are typos, then I
need to see a Copy/Paste of the real query. Otherwise, go
back and make sure you qualify **every** field with the
appropriate table/alias name.

 

[Marshall Barton]

This is a completely different query that has no hope of
doing what you want because, in this situation, Last returns
an essentially random end date value.

Go back to your first query and straighten out the table and
table alias (not field alias) name qualifier for every
field, including the ones in the subquery.

If it still doesn't work, post back with a Copy/Paste of the
query's SQL view and I try to unravel it.

Once you/we get that to work, then you can add the vendor
name part.
--
Marsh
MVP [MS Access]

Here is another version of the SQL without all the aliases - I need to show
all contracts by VENDOR name- if there is more than one contract by Vendor
Name, I need have the query return ONLY the contract with the LATEST date:

SELECT VENDORS.VendorName, Last(CONTRACTS.EndDate) AS LastOfEndDate,
CONTRACTS.ContractNum, CONTRACTS.Value, CONTRACTS.Type, CONTRACTS.StartDate
FROM VENDORS INNER JOIN CONTRACTS ON VENDORS.VendorNum = CONTRACTS.VendorNum
GROUP BY VENDORS.VendorName, CONTRACTS.ContractNum, CONTRACTS.Value,
CONTRACTS.Type, CONTRACTS.StartDate

My fields are:
VendorName (From the VENDORS table)
VendorNum (From the CONTRACTS table and the VENDORS table)
StartDate (From the CONTRACTS table)
EndDate (From the CONTRACTS table)
ContractNum (From the CONTRACTS table)
Type (From the CONTRACTS table)

The alias here came from when I selected "Last" in the criteria of a Sum
Query

I have query that shows all contracts, and links to the vendor table to
include the vendor name - we only have one "active" contract at a time per

[quoted text clipped - 11 lines]

ContractNum = Mx.ContractNum);
================================================================

The query looks like it's logically correct, but with all
the aliasing errors I don't see how it can run at all.

If you retyped the real query and those are typos, then I
need to see a Copy/Paste of the real query. Otherwise, go
back and make sure you qualify **every** field with the
appropriate table/alias name.