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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Patrick MatthewsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.