GETTING THE MOST RECENT DATE

There's a table called tbl_contacts and each contact is related to a company
from the table tbl_companies. I want to get the most recent contact from each
company. Which expression should I use in the fld_contact_date cretiria?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

On what fields are the tables related?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"bifteki via AccessMonster.com" wrote:

There's a table called tbl_contacts and each contact is related to a company
from the table tbl_companies. I want to get the most recent contact from each
company. Which expression should I use in the fld_contact_date cretiria?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

Sorry my mistake I didn't give much info.
They are related on the fld_company_id field (primary key for tbl_companies
and foreign key for tbl_contacts).
The tbl_contacts contains contacts that the company has made with numerous
companies (meaning communicated).

This following query gives the dates of all the contacts of all companies.

SELECT TOP 100 PERCENT dbo.tbl_Companies.fld_company_name, dbo.
tbl_contacts.fld_contact_date
FROM dbo.tbl_Companies INNER JOIN
dbo.tbl_contacts ON dbo.tbl_Companies.fld_company_id =
dbo.tbl_contacts.fld_company_id
ORDER BY dbo.tbl_contacts.fld_contact_date DESC

I want each company to appear one time and I want the date of its most recent
contact appear next to it.
I hope this answers any questions.




Jerry Whittle wrote:
On what fields are the tables related?
There's a table called tbl_contacts and each contact is related to a company
from the table tbl_companies. I want to get the most recent contact from each
company. Which expression should I use in the fld_contact_date cretiria?

--
Message posted via http://www.accessmonster.com

SELECT dbo.tbl_Companies.fld_company_name,
MAX(dbo.tbl_contacts.fld_contact_date)
FROM dbo.tbl_Companies INNER JOIN dbo.tbl_contacts
ON dbo.tbl_Companies.fld_company_id =
dbo.tbl_contacts.fld_company_id
GROUP BY dbo.tbl_Companies.fld_company_name
ORDER BY dbo.tbl_contacts.fld_contact_date DESC ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"bifteki via AccessMonster.com" wrote:

Sorry my mistake I didn't give much info.
They are related on the fld_company_id field (primary key for tbl_companies
and foreign key for tbl_contacts).
The tbl_contacts contains contacts that the company has made with numerous
companies (meaning communicated).

This following query gives the dates of all the contacts of all companies.

SELECT TOP 100 PERCENT dbo.tbl_Companies.fld_company_name, dbo.
tbl_contacts.fld_contact_date
FROM dbo.tbl_Companies INNER JOIN
dbo.tbl_contacts ON dbo.tbl_Companies.fld_company_id =
dbo.tbl_contacts.fld_company_id
ORDER BY dbo.tbl_contacts.fld_contact_date DESC

I want each company to appear one time and I want the date of its most recent
contact appear next to it.
I hope this answers any questions.

Jerry Whittle wrote:
On what fields are the tables related?
There's a table called tbl_contacts and each contact is related to a company
from the table tbl_companies. I want to get the most recent contact from each
company. Which expression should I use in the fld_contact_date cretiria?

Thank you very much for your reply. However when I enter this statement the
following error appears when I try to save the view:

ADO error: Column name 'dbo.tbl_contacts.fld_contact_date' is invalid in the
ORDER BY clause because it is not contained in either an aggregate function
or the GROUP BY clause.

What could this mean?


Jerry Whittle wrote:
SELECT dbo.tbl_Companies.fld_company_name,
MAX(dbo.tbl_contacts.fld_contact_date)
FROM dbo.tbl_Companies INNER JOIN dbo.tbl_contacts
ON dbo.tbl_Companies.fld_company_id =
dbo.tbl_contacts.fld_company_id
GROUP BY dbo.tbl_Companies.fld_company_name
ORDER BY dbo.tbl_contacts.fld_contact_date DESC ;
Sorry my mistake I didn't give much info.
They are related on the fld_company_id field (primary key for tbl_companies
[quoted text clipped - 19 lines]
from the table tbl_companies. I want to get the most recent contact from each
company. Which expression should I use in the fld_contact_date cretiria?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

Try removing the last line.

Also do you really need the dbo. on everything? Is this a simple query or
something that you are running from code.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"bifteki via AccessMonster.com" wrote:

Thank you very much for your reply. However when I enter this statement the
following error appears when I try to save the view:

ADO error: Column name 'dbo.tbl_contacts.fld_contact_date' is invalid in the
ORDER BY clause because it is not contained in either an aggregate function
or the GROUP BY clause.

What could this mean?


Jerry Whittle wrote:
SELECT dbo.tbl_Companies.fld_company_name,
MAX(dbo.tbl_contacts.fld_contact_date)
FROM dbo.tbl_Companies INNER JOIN dbo.tbl_contacts
ON dbo.tbl_Companies.fld_company_id =
dbo.tbl_contacts.fld_company_id
GROUP BY dbo.tbl_Companies.fld_company_name
ORDER BY dbo.tbl_contacts.fld_contact_date DESC ;
Sorry my mistake I didn't give much info.
They are related on the fld_company_id field (primary key for tbl_companies
[quoted text clipped - 19 lines]
from the table tbl_companies. I want to get the most recent contact from each
company. Which expression should I use in the fld_contact_date cretiria?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

Thanks a lot! It works !
The thing is I made the original simple query using the diagram and grid
panes and copied the statement from the SQL pane. So I guess Access used this
naming in the query by itself.
Thanks again!!!


Jerry Whittle wrote:
Try removing the last line.

Also do you really need the dbo. on everything? Is this a simple query or
something that you are running from code.
Thank you very much for your reply. However when I enter this statement the
following error appears when I try to save the view:
[quoted text clipped - 17 lines]
from the table tbl_companies. I want to get the most recent contact from each
company. Which expression should I use in the fld_contact_date cretiria?

--
Message posted via http://www.accessmonster.com