Enumerate Grouped Records in a Query 2

[fishtek]

Im trying to enumerate grouped records in a query. Please see table below.

The query is grouped and sorted by ID then depth. When I run the query I would like Depth_Enum to enumerate the depth values based on the grouping.
Any suggestion would be appreciated.
Thank You

 

[genomon]

Post the SQL behind the query - specifically the ORDER BY clause...

Beir bua agus beannacht!

 

[fishtek]

Thanks genomon:

The SQL is as follows:

SELECT tblPhysData.ColNum AS ID, tblPhysData.Depth
FROM tblStationInfo INNER JOIN tblPhysData ON tblStationInfo.ColNum = tblPhysData.ColNum
ORDER BY tblPhysData.ColNum, tblPhysData.Depth;

So where I'm stuck is how to add a Depth_Enum field that enumerates the sorted depth value as shown in my example table (also known as group ranking?)

 

[dhookom]

Try something like:

SELECT tblPhysData.ID, tblPhysData.Depth, "Depth_" & Count([tblPhysData_1]![ID]) AS Expr1
FROM tblPhysData INNER JOIN tblPhysData AS tblPhysData_1 ON tblPhysData.ID = tblPhysData_1.ID
WHERE (((tblPhysData_1.Depth)<=[tblPhysData]![Depth]))
GROUP BY tblPhysData.ID, tblPhysData.Depth;

[pre]ID Depth Expr1
39 0.04 Depth_1
39 1.03 Depth_2
39 2.01 Depth_3
40 0.12 Depth_1
40 1.01 Depth_2
40 2.22 Depth_3
41 0.03 Depth_1
41 1.01 Depth_2
41 2.02 Depth_3[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[fishtek]

Thanks dhookom. When I insert the SQL code you provided into the query I get the following message:

Any suggestions?

Thank You

 

[dhookom]

Fishtek,
What is the exact SQL you tried in your query?

I had created a table with records as you suggested (without tblStationInfo) and it worked as expected for me.

Duane
Hook'D on Access
MS Access MVP

 

[fishtek]

dhookom:

I believe I had a minor syntax error. I reworked the query and all is working fine now. Thanks so much for your help!

 

[Andrzejek]

Don't you think Duane deserves a Star for the help....?

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.