Pull Recent Record for Each Vendor

[Guest]

Hi,
I have 2 related tables. 1 with general vendor info and 1 with contact info
for each vendor. Vendor is field connecting both tables.

Example:
Table 1
vendor
address
city
state
zip
phone

Table 2
vendor
date of contact
contact reason

Want to run a query only showing each vendor with the most recent contact
info from Table 2.

Example: Vendor John has 10 records in Table 2. I want to pull only the
current record based on date, not all of the records. How do I do that?

Many thanks in advance for anyone who can help me.

 

[Arvin Meyer [MVP]]

Try:

SELECT vendor, Max([date of contact]) AS LastContact
FROM [Table 2]
GROUP BY vendor;

 

[Guest]

Arvin,
Many, many thanks for your fast and fabulous response.

Try:

SELECT vendor, Max([date of contact]) AS LastContact
FROM [Table 2]
GROUP BY vendor;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,
I have 2 related tables. 1 with general vendor info and 1 with contact
info
for each vendor. Vendor is field connecting both tables.

Example:
Table 1
vendor
address
city
state
zip
phone

Table 2
vendor
date of contact
contact reason

Want to run a query only showing each vendor with the most recent contact
info from Table 2.

Example: Vendor John has 10 records in Table 2. I want to pull only the
current record based on date, not all of the records. How do I do that?

Many thanks in advance for anyone who can help me.