[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-10
4
Medium Priority
?
375 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:SusanLIMS
  • 2
  • 2
4 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22921414
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
 

Author Comment

by:SusanLIMS
ID: 22921632
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 22921679
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
 

Author Comment

by:SusanLIMS
ID: 22921944
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question