Microsoft Access Query - Counter reset based on a field (Also SQL Server, Oracle)

I have a Microsoft Access dataset that has 2 fields.  I want to query the data and put a counter on the result set that resets by a grouping on field 1.  

I am also intereseted in a query that will work in SQL Server and Oracle since I have users in all environments.

Example Dataset:

Color      Shape
------------------------------------
Green      Square
Green      Triangle
Green      Hexagon
Blue      Square
Blue      Triangle
Red      Square
Red      Hexagon


Query output desired:
Color      Shape            Counter
------------------------------------------------------------------------------
Green      Square            1
Green      Triangle            2
Green      Hexagon            3
Blue      Square            1
Blue      Triangle            2
Red      Square            1
Red      Hexagon            2
SusanLIMSAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Which platforms have you tried it on?

Tweaking a little...

SELECT t1.Color, t1.Shape,
    (SELECT COUNT(t2.Color)
    FROM JunkTable t2
    WHERE t2.Color = t1.Color AND t2.Shape <= t1.Shape) AS Counter
FROM JunkTable t1
ORDER BY t1.Color, t1.Shape

That should be good for Access and SQL Server.  No idea about Oracle.
0
 
Patrick MatthewsCommented:
SELECT t1.Color, t1.Shape,
    (SELECT COUNT(t2.*)
    FROM SomeTable t2
    WHERE t2.Color = t1.Color AND t2.Shape <= t1.Shape) AS Counter
FROM SomeTable t1
ORDER BY t1.Color, t1.Shape
0
 
SusanLIMSAuthor Commented:
I just tried this (I actually created a new table since my real problem is considerably more complex).  I ran the following query against a table (JunkTable) that has only the columns COLOR and SHAPE.  I get a syntax error in query expression.  I'm pretty sure I took your query and only changed the table name.

Thanks.

SELECT t1.Color, t1.Shape,
    (SELECT COUNT(t2.*)
    FROM JunkTable t2
    WHERE t2.Color = t1.Color AND t2.Shape <= t1.Shape) AS Counter
FROM JunkTable t1
ORDER BY t1.Color, t1.Shape
0
 
SusanLIMSAuthor Commented:
That syntax worked beautifully in MS Access.  (first syntax you provided did not work in MS Access 2003).  Just as an FYI - following is the more complex query that resulted from this example.  Thanks so much for the help.


SELECT t1.Analysis, t1.Specimen_source
    (SELECT COUNT(t2.Analysis)
    FROM
(
SELECT X_SAMPLE_SUBC_ANALY.ANALYSIS, X_SAMPLE_SUBC_SPCSRC.SPECIMEN_SOURCE
FROM (X_SAMPLE_SUBC_ANALY INNER JOIN X_SAMPLE_SUBC_SPCSRC ON (X_SAMPLE_SUBC_ANALY.SUBCATEGORY = X_SAMPLE_SUBC_SPCSRC.SUBCATEGORY) AND (X_SAMPLE_SUBC_ANALY.X_SAMPLE_CATEGORY = X_SAMPLE_SUBC_SPCSRC.X_SAMPLE_CATEGORY)) INNER JOIN V_ANALYSIS_VERSION ON X_SAMPLE_SUBC_ANALY.ANALYSIS = V_ANALYSIS_VERSION.NAME) as T2

    WHERE t2.Analysis = t1.Analysis AND t2.Specimen_source <= t1.Specimen_source) AS Counter

FROM
(
SELECT X_SAMPLE_SUBC_ANALY.ANALYSIS, X_SAMPLE_SUBC_SPCSRC.SPECIMEN_SOURCE
FROM (X_SAMPLE_SUBC_ANALY INNER JOIN X_SAMPLE_SUBC_SPCSRC ON (X_SAMPLE_SUBC_ANALY.SUBCATEGORY = X_SAMPLE_SUBC_SPCSRC.SUBCATEGORY) AND (X_SAMPLE_SUBC_ANALY.X_SAMPLE_CATEGORY = X_SAMPLE_SUBC_SPCSRC.X_SAMPLE_CATEGORY)) INNER JOIN V_ANALYSIS_VERSION ON X_SAMPLE_SUBC_ANALY.ANALYSIS = V_ANALYSIS_VERSION.NAME) as T1

ORDER BY t1.Analysis, t1.Specimen_source
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.