asked on

MySQL query in Access

A collegue requested I modify this query (for MS Access) so that he gets the latest date (adate) from the projectactivity table as well as the list of projects:

original query:

SELECT * FROM projects 
WHERE projects.active='yes'

Open in new window

I gave him this query, which works well in MySQL:

SELECT * FROM projects 
LEFT OUTER JOIN (SELECT projectactivity.projectid as id,max(adate) as latestdate FROM projectactivity WHERE projectactivity.projectid='10335-2') as derivedTable ON projects.ProjectID=id
WHERE projects.active='yes'

Open in new window

I don't use Access enough to know why this does not work for him in Access.

NOTE:In his MS Access setup, he is connecting to the same MySQL tables that I tested my query on. He uses Access to format the MySQL data into printable report formats.

SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zipbang

ASKER

Julian,

This helps, but I think I misunderstood his request.

He wants a query that shows all active projects (active='yes') from the projects table and the latest adate (max(adate)) for each one when adate is in the table named projectactivity

SELECT * FROM projects 
--  join on the latest date from the projectactivity table, where projectactivity.id=projects.id
WHERE projects.active='yes'

Open in new window

Sorry for the confusion, am I clear here?

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Julian Hansen

Just checking, why do you have this filter in your sub query

WHERE projectactivity.projectid='10335-2'

Will you only ever by searching on that projectid?