Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Community Discussions (All other subjects)
 Access Query with Nested select		  New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

D3mon
Senior Member

United Kingdom
1685 Posts
Posted - 23 December 2003 :  06:35:38  Show Profile  Visit D3mon's Homepage
Can anyone help with this?

I've three tables tblProjects, tblProjectStatus and tblProjectUpdates

A Project can only have one Status, but many Updates.

I've been using this Access 2000 query to just grab a list of projects and their relevant status:

SELECT
  [ProjectID],  
  [ProjTitle],
  [ProjDescr],
  [tblProjectStatus].[ProjStatusDescr]
FROM tblProjects
INNER JOIN tblProjectStatus
  ON [tblProjects].[ProjStatusID]=[tblProjectStatus].[ProjectStatusID]
ORDER BY [LastUpdated] DESC;


Now,I've added the Updates Table, I'd like to grab just the very latest Update relating to each project. I tried this:

SELECT
  [ProjectID],  
  [ProjTitle],
  [ProjDescr],
  [tblProjectStatus].[ProjStatusDescr]
  (
     SELECT TOP 1
       Author,
       UpdateText
     FROM tblProjectUpdates
     WHERE tblProjects.ProjectID = tblProjectUpdates.ProjectID   
  )
FROM tblProjects
INNER JOIN tblProjectStatus
  ON [tblProjects].[ProjStatusID]=[tblProjectStatus].[ProjectStatusID]
ORDER BY [LastUpdated] DESC;

But no joy. Sadly, Access is rather less specific than SQL in it's error descriptions.

Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won"

pweighill
Junior Member

United Kingdom
453 Posts
Posted - 23 December 2003 :  07:08:19  Show Profile
You can't return two fields in the subselect at the same time, also you proabably want to include an order by in the subselect as well.

SELECT
  [ProjectID],  
  [ProjTitle],
  [ProjDescr],
  [tblProjectStatus].[ProjStatusDescr]
  (
     SELECT TOP 1
       Author
     FROM tblProjectUpdates
     WHERE tblProjects.ProjectID = tblProjectUpdates.ProjectID   
     ORDER BY ????
  ) AS Author
  (
     SELECT TOP 1
       UpdateText
     FROM tblProjectUpdates
     WHERE tblProjects.ProjectID = tblProjectUpdates.ProjectID   
     ORDER BY ????
  ) AS UpdateText
FROM tblProjects
INNER JOIN tblProjectStatus
  ON [tblProjects].[ProjStatusID]=[tblProjectStatus].[ProjectStatusID]
ORDER BY [LastUpdated] DESC;
Go to Top of Page

D3mon
Senior Member

United Kingdom
1685 Posts
Posted - 23 December 2003 :  08:15:30  Show Profile  Visit D3mon's Homepage
Given this is my first venture into Access-based SQL (my experience is in SQL 2000 SP's), I decided to recode, using a seperate query for the additional data. Thank you for your input though.

Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won"
Go to Top of Page

D3mon
Senior Member

United Kingdom
1685 Posts
Posted - 24 December 2003 :  04:33:26  Show Profile  Visit D3mon's Homepage
are there any online guides/resources for hand-writing MS Access Queries?

Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won"
Go to Top of Page

Nathan
Help Moderator

USA
7664 Posts
Posted - 24 December 2003 :  06:48:13  Show Profile  Visit Nathan's Homepage
I havn't really ever found a good Access Resource. I've kinda learned it by trial and error.

I do believe that could be expressed as a join. Something like

SELECT
  [ProjectID],  
  [ProjTitle],
  [ProjDescr],
  [tblProjectStatus].[ProjStatusDescr],
  [Author],
  [UdateText]
FROM (tblProjects
INNER JOIN tblProjectStatus
  ON [tblProjects].[ProjStatusID]=[tblProjectStatus].[ProjectStatusID])
LEFT JOIN tblProjectUpdates ON (tblProjects.ProjectID = tblProjectUpdates.ProjectID AND tblProjectUpdates.id = MAX(tblProjectUpdates.id))
GROUP BY
  [ProjectID],  
  [ProjTitle],
  [ProjDescr],
  [tblProjectStatus].[ProjStatusDescr],
  [Author],
  [UdateText]
ORDER BY [LastUpdated] DESC;
Nathan Bales
CoreBoard | Active Users Download
Edited by - Nathan on 24 December 2003 06:48:36
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000 Version 3.4.07