Solved

MySQL query in Access

Posted on 2014-04-11
4
352 Views
Last Modified: 2014-04-14
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.
0
Comment
Question by:Zipbang
  • 2
4 Comments
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 total points
ID: 39993904
Try this

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=derivedTable.id
WHERE projects.active='yes' 

Open in new window

0
 

Author Comment

by:Zipbang
ID: 39994366
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?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 39995067
remove the WHERE clause and I think you missed the GROUP BY clause.
SELECT * FROM projects 
    LEFT OUTER JOIN (
       SELECT projectactivity.projectid as id,max(adate) as latestdate 
       FROM projectactivity GROUP BY projectactivity.projectid
     ) AS derivedTable 
     ON projects.ProjectID=derivedTable.id
WHERE projects.active='yes' 

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39995911
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?
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now