Help with nested group by query in Ms Access 1

[juddymar58]

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

 

[Andrzejek]

First think that pops is your bold portion of your statement:
[tt]
(SELECT Min(tblProjectLog1.fldLogDate) AS fldMinLogDate, tblProjectDesignPhaseList1.fldProjectDesignPhase FROM ...) AS fldFirstConstruction[/tt]

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.

 

[juddymar58]

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

 

[Andrzejek]

Can you make the Select statement without GROUP BY part where you get the correct data? Something like:
[tt]
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;
[/tt]


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.

 

[juddymar58]

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