INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Help with nested group by query in Ms Access

Help with nested group by query in Ms Access

(OP)
Hi I have two tables
tblProjectLog
fldProjectID
fldProjectDesignPhaseID
fldLogDate
fldProjectIDLog - primary key

tblProjectDesignPhaseList
fldProjectDesignPhaseID - PK joins to same field in tblProjectLog
fldProjectDesignPhase - text (example of data 'Planning, 'Detailed Design' and 'Construction'

I need to return the earliest value of fldLogDate where fldProjectDesignPhase is 'Detailed Design' for each fldProjectID which is fairly easily using a group by query.
SELECT tblProjectLog.fldProjectID, Min(tblProjectLog.fldLogDate) AS fldFirstLogDate
FROM tblProjectLog INNER JOIN tblProjectDesignPhaseList ON tblProjectLog.fldProjectDesignPhaseID = tblProjectDesignPhaseList.fldProjectDesignPhaseID
WHERE (((tblProjectDesignPhaseList.fldProjectDesignPhase)="Detail Design"))
GROUP BY tblProjectLog.fldProjectID, tblProjectLog.fldProjectID
ORDER BY tblProjectLog.fldProjectID;

Where I'm stuck is I need to add a nested query to return the earliest log date where the design phase is 'Construction' for the project. I'm only the log date if the record is after
after the 'Detail Design' phase record. So I have tried to use fldProjectIDLog> original log value in query (to only use records that are after the original one).

I was hoping to have a nested query like the following but keep getting error messages I think the issue I'm having is passing in the grouped by values from the main queries into the nested part:
SELECT tblProjectLog.fldProjectID, Min(tblProjectLog.fldLogDate) AS fldFirstLogDate, (SELECT Min(tblProjectLog1.fldLogDate) AS fldMinLogDate, tblProjectDesignPhaseList1.fldProjectDesignPhase
FROM tblProjectLog AS tblProjectLog1 INNER JOIN tblProjectDesignPhaseList AS tblProjectDesignPhaseList1 ON tblProjectLog1.fldProjectDesignPhaseID = tblProjectDesignPhaseList1.fldProjectDesignPhaseID
WHERE tblProjectLog1.fldProjectID=[tblProjectLog].[fldProjectID] AND tblProjectDesignPhaseList1.fldProjectDesignPhase='Construction' AND tblProjectLog1.fldProjectIDLog>min([tblProjectLog].[fldLogDate])) AS fldFirstConstruction

FROM tblProjectLog INNER JOIN tblProjectDesignPhaseList ON tblProjectLog.fldProjectDesignPhaseID = tblProjectDesignPhaseList.fldProjectDesignPhaseID
WHERE (((tblProjectDesignPhaseList.fldProjectDesignPhase)="Detail Design"))
GROUP BY tblProjectLog.fldProjectID, (SELECT Min(tblProjectLog1.fldLogDate) AS fldMinLogDate, tblProjectDesignPhaseList1.fldProjectDesignPhase
FROM tblProjectLog AS tblProjectLog1 INNER JOIN tblProjectDesignPhaseList AS tblProjectDesignPhaseList1 ON tblProjectLog1.fldProjectDesignPhaseID = tblProjectDesignPhaseList1.fldProjectDesignPhaseID
WHERE tblProjectLog1.fldProjectID=[tblProjectLog].[fldProjectID] AND tblProjectDesignPhaseList1.fldProjectDesignPhase='Construction' AND tblProjectLog1.fldProjectIDLog>min([tblProjectLog].[fldLogDate]))
ORDER BY tblProjectLog.fldProjectID;

I know I could easily do this using multiple queries but I would like to become more familiar with nested queries. I have been searching nested queries and I'm fine when there is no grouping but I seem to be having issues as soon as I have to incorporate it.

Any advice appreciated.
Thanks
Justin

RE: Help with nested group by query in Ms Access

First think that pops is your bold portion of your statement:

(SELECT Min(tblProjectLog1.fldLogDate) AS fldMinLogDate, tblProjectDesignPhaseList1.fldProjectDesignPhase FROM ...) AS fldFirstConstruction


You are selecting 2 fields AS one field.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Help with nested group by query in Ms Access

(OP)
Thanks for the reply Andrzejek. I didn't even notice that I have changed it so my sql is now as below but I'm still getting a syntax error. I just cant seem to get my head around the group bys in the query which is causing me the issue, I have tried using aliases in the main query for the values passed into the nested query but can't seem to resolve it.
SELECT tblProjectLog.fldProjectID AS fldGroupedProjectID, Min(tblProjectLog.fldLogDate) AS fldFirstLogDate, (SELECT Min(tblProjectLog1.fldLogDate) AS fldMinLogDate
FROM tblProjectLog AS tblProjectLog1 INNER JOIN tblProjectDesignPhaseList AS tblProjectDesignPhaseList1 ON tblProjectLog1.fldProjectDesignPhaseID = tblProjectDesignPhaseList1.fldProjectDesignPhaseID
WHERE tblProjectLog1.fldProjectID=[fldGroupedProjectID] AND tblProjectDesignPhaseList1.fldProjectDesignPhase='Construction' AND tblProjectLog1.fldProjectIDLog>[fldFirstLogDate]) AS fldFirstConstruction

FROM tblProjectLog INNER JOIN tblProjectDesignPhaseList ON tblProjectLog.fldProjectDesignPhaseID = tblProjectDesignPhaseList.fldProjectDesignPhaseID
WHERE (((tblProjectDesignPhaseList.fldProjectDesignPhase)="Detail Design"))
GROUP BY tblProjectLog.fldProjectID, (SELECT Min(tblProjectLog1.fldLogDate) AS fldMinLogDate
FROM tblProjectLog AS tblProjectLog1 INNER JOIN tblProjectDesignPhaseList AS tblProjectDesignPhaseList1 ON tblProjectLog1.fldProjectDesignPhaseID = tblProjectDesignPhaseList1.fldProjectDesignPhaseID
WHERE tblProjectLog1.fldProjectID=[fldGroupedProjectID] AND tblProjectDesignPhaseList1.fldProjectDesignPhase='Construction' AND tblProjectLog1.fldProjectIDLog>[fldFirstLogDate])
ORDER BY tblProjectLog.fldProjectID;

Any further advice would be greatly appreciated as Id love to know how to do this without having to use a query on a query (which is what I normally would do).

Thanks
Justin

RE: Help with nested group by query in Ms Access

Can you make the Select statement without GROUP BY part where you get the correct data? Something like:

SELECT
tblProjectLog.fldProjectID AS fldGroupedProjectID,
Min(tblProjectLog.fldLogDate) AS fldFirstLogDate,
(SELECT Min(tblProjectLog1.fldLogDate) AS fldMinLogDate
FROM tblProjectLog AS tblProjectLog1
INNER JOIN tblProjectDesignPhaseList AS tblProjectDesignPhaseList1
ON tblProjectLog1.fldProjectDesignPhaseID = tblProjectDesignPhaseList1.fldProjectDesignPhaseID
WHERE tblProjectLog1.fldProjectID=[fldGroupedProjectID]
AND tblProjectDesignPhaseList1.fldProjectDesignPhase='Construction'
AND tblProjectLog1.fldProjectIDLog>[fldFirstLogDate]) AS fldFirstConstruction
FROM tblProjectLog INNER JOIN tblProjectDesignPhaseList ON tblProjectLog.fldProjectDesignPhaseID = tblProjectDesignPhaseList.fldProjectDesignPhaseID
WHERE (((tblProjectDesignPhaseList.fldProjectDesignPhase)="Detail Design"))
GROUP BY tblProjectLog.fldProjectID, (SELECT Min(tblProjectLog1.fldLogDate) AS fldMinLogDate
FROM tblProjectLog AS tblProjectLog1 INNER JOIN tblProjectDesignPhaseList AS tblProjectDesignPhaseList1 ON tblProjectLog1.fldProjectDesignPhaseID = tblProjectDesignPhaseList1.fldProjectDesignPhaseID
WHERE tblProjectLog1.fldProjectID=[fldGroupedProjectID] AND tblProjectDesignPhaseList1.fldProjectDesignPhase='Construction' AND tblProjectLog1.fldProjectIDLog>[fldFirstLogDate])

ORDER BY tblProjectLog.fldProjectID;

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Help with nested group by query in Ms Access

(OP)
Hi Andy, thanks you set me on the right track. I went back a level and removed the grouping from the main query (I introduced main project table for this). What I then did is have two nested queries for each of the fields to get the data from the log tables. The only limitation is I couldn't add criteria to ensure the log date for first construction is greater than the log date for first detail design. This won't be an issue anyway as it should always go into construction after anyway (database restriction). Its funny I think I had tunnel vision trying to have the group by in the main query as soon as I moved back a level it was fine:)

SELECT tblProject.fldProjectID, (SELECT Min(tblProjectLog.fldLogDate) AS fldFirstDetailDesign
FROM tblProjectLog INNER JOIN tblProjectDesignPhaseList ON tblProjectLog.fldProjectDesignPhaseID = tblProjectDesignPhaseList.fldProjectDesignPhaseID
WHERE (((tblProjectLog.fldProjectID)=[tblProject].[fldProjectID]) AND ((tblProjectDesignPhaseList.fldProjectDesignPhase)="Detail Design"))) AS fldFirstDetailDesign, (SELECT Min(tblProjectLog.fldLogDate) AS fldFirstConstruction
FROM tblProjectLog INNER JOIN tblProjectDesignPhaseList ON tblProjectLog.fldProjectDesignPhaseID = tblProjectDesignPhaseList.fldProjectDesignPhaseID
WHERE (((tblProjectLog.fldProjectID)=[tblProject].[fldProjectID]) AND ((tblProjectDesignPhaseList.fldProjectDesignPhase)="Construction"))) AS fldFirstConstruction
FROM tblProject;


Thanks
Justin

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close