SQL Query question

[abarnett]

Have two tables UseCases and UseCaseNotes, where UseCases is the parent table. Trying to get the name, owner, start date, and status along with the latest Note Date, and the associated resource and description.

I started with the query below, but am having some dificulty in completing it. Anyone who can help?

SELECT UseCases.Name, UseCases.Description, UseCases.Owner, UseCases.Status, UseCases.StartDate, MAX(UseCaseNotes.NoteDate), UseCaseNotes.Resource, UseCaseNotes.Description
FROM UseCases, UseCaseNotes
WHERE UseCases.UseCaseID=UseCaseNotes.UseCaseID
ORDER BY UseCases.Owner;

Thanks for the assist!

A

 

[WayneRyan]

A,

Try using two queries:

qryGetMax:

Select UseCaseID, Max(NoteDate)
From   UseCaseNotes

qryGetAll:

SELECT UseCases.Name, 
       UseCases.Description, 
       UseCases.Owner, 
       UseCases.Status, 
       UseCases.StartDate, 
       qryGetMax.NoteDate, 
       UseCaseNotes.Resource, 
       UseCaseNotes.Description
FROM UseCases, UseCaseNotes, qryGetMax
WHERE UseCases.UseCaseID=UseCaseNotes.UseCaseID And
      UseCases.UseCaseID=qryGetMax.UseCaseID
ORDER BY UseCases.Owner;

I would generally use an Inner Join, but that should get you started.

hth,
Wayne

 

[abarnett]

This does not work inside the query builder...Is there a way to make it work inside a query, rather than building code?

A

 

[tehNellie]

If you change the view inthe query builder to SQL and just paste the above query SQL in it's a lot simpler.