Running Count within a Group (Time-Zero Data)

M

Mike

Hi,

I am attempting to create a running count within a group in my query
(using design view). The goal is to time-zero the data so that rather
than a date with each data point, I have a running count on the number
of days of production. E.g.

ID # Date Production Days
1 1/1/09 5 1
1 1/2/09 8 2
1 1/3/09 4 3
2 4/3/08 2 1
2 4/4/08 5 2

etc...

Is there a simple way to achieve this (possibly using dcount)?
 
M

Mike

On my website,www.rogersaccesslibrary.com, there is a small sample database
called RunningSumInQuery.mdb, which illustrates how to do this.  You can
find it here:http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=279

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...

I guess what I am looking for is a dcount expression that will create
a running count of the date if the ID# matches what is above it. This
is simple in Excel, with simply using an if statement to see if the
ID# matches that above it and adding a counter if that is the case.
Is there an equivalent way to do this in an Access query in design
view?

Roger, the file you listed uses SQL, which I'm not very familiar with
(In addition, the query is rather large, so the SQL statement is
complex).
 
R

Roger Carlson

Unfortunately, Access is not positional like Excel, so there is no
equivalent type function. You need to do it in SQL if you're going to do it
in a query. Reports have a running sum function that you might be able to
use, but works only in a report.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


On my website,www.rogersaccesslibrary.com, there is a small sample
database
called RunningSumInQuery.mdb, which illustrates how to do this. You can
find it
here:http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=279

--
--Roger Carlson
MS Access MVP
Access Database Samples:www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...

I guess what I am looking for is a dcount expression that will create
a running count of the date if the ID# matches what is above it. This
is simple in Excel, with simply using an if statement to see if the
ID# matches that above it and adding a counter if that is the case.
Is there an equivalent way to do this in an Access query in design
view?

Roger, the file you listed uses SQL, which I'm not very familiar with
(In addition, the query is rather large, so the SQL statement is
complex).
 
V

vanderghast

SELECT idDateProd.id, idDateProd.date, idDateProd.production, COUNT(*)
FROM idDateProd INNER JOIN idDateProd AS idDateProd_1 ON idDateProd.id =
idDateProd_1.id
WHERE idDateProd.date >= idDateProd_1.date
GROUP BY idDateProd.id, idDateProd.date, idDateProd.production;


should do, as long as your 'id' match the group of sequential days.

I also assumed the first three columns were known, and that you were looking
for the fourth one.


Vanderghast, Access MVP
 
M

Mike

SELECT idDateProd.id, idDateProd.date, idDateProd.production, COUNT(*)
FROM idDateProd INNER JOIN idDateProd AS idDateProd_1 ON idDateProd.id =
idDateProd_1.id
WHERE idDateProd.date >= idDateProd_1.date
GROUP BY idDateProd.id, idDateProd.date, idDateProd.production;

should do, as long as your 'id' match the group of sequential days.

I also assumed the first three columns were known, and that you were looking
for the fourth one.

Vanderghast, Access MVP
Click to expand...

My current SQL is listed below. I would like to count the D_DATE
(where the first date is 1 and so on to time zero the data), grouping
by ENERTIA_NO. I apologize for the confusion, but I'm not very
familiar with the SQL format in Access. How can I modify this code to
include what you showed above?


SELECT dbo_AC_DAILY.PROPNUM, dbo_AC_PROPERTY.ENERTIA_NO,
dbo_AC_PROPERTY.MAP_QUAD, dbo_AC_PROPERTY.RESERVOIR,
dbo_AC_PROPERTY.SPUD_DATE, dbo_AC_PROPERTY.TIL_DATE,
dbo_AC_DAILY.D_DATE, dbo_AC_DAILY.GAS INTO WHOLE_KY_Hz_From2000
FROM (dbo_AC_DAILY INNER JOIN dbo_AC_PRODUCT ON dbo_AC_DAILY.PROPNUM =
dbo_AC_PRODUCT.PROPNUM) INNER JOIN dbo_AC_PROPERTY ON
dbo_AC_PRODUCT.PROPNUM = dbo_AC_PROPERTY.PROPNUM
WHERE (((dbo_AC_DAILY.D_DATE)>[SPUD_DATE]) AND
((dbo_AC_PROPERTY.WELLCLASS)="hor") AND ((dbo_AC_PROPERTY.STATE)="ky")
AND ((dbo_AC_PROPERTY.SPUD_DATE)>#1/1/2000#))
GROUP BY dbo_AC_DAILY.PROPNUM, dbo_AC_PROPERTY.ENERTIA_NO,
dbo_AC_PROPERTY.MAP_QUAD, dbo_AC_PROPERTY.RESERVOIR,
dbo_AC_PROPERTY.SPUD_DATE, dbo_AC_PROPERTY.TIL_DATE,
dbo_AC_DAILY.D_DATE, dbo_AC_DAILY.GAS
ORDER BY dbo_AC_PROPERTY.ENERTIA_NO, dbo_AC_PROPERTY.MAP_QUAD,
dbo_AC_PROPERTY.RESERVOIR;
 
V

vanderghast

You can write query based on another query. So, if the actual query does
what it should do, keep it unmodified and write ANOTHER query:

Bring your actual query TWICE in the new query, one will get an _1 append to
its name.

Change the query to a Total query.

Drag the ENERTIA_NO (probably what was your 'id' field, I assume, in the
first query), and D_DATE field from the not _1 query into the grid, keep
the proposed GROUP BY.

Drag D_DATE from the not _1 query a second time, change the GROUP BY to
WHERE, and, in the criteria:
= [queryName_1].D_DATE
Click to expand...
( use the real query name, with its _1 at the end of
the name)

Link the two queries, in the upper part of the query designer, by dragging
the ENERTIA_NO from one of them and dropping it on the same field of the
other query. A black line will now link the two queries.

In the grid, a new column, type:
COUNT(*)
and change the GROUP BY to EXPRESSION.


That's all.


That still assumes that ENERTIA_NO is such that sequentially un-interrupted
dates share the same value in that ENERTIA_NO.


Vanderghast, Access MVP


SELECT idDateProd.id, idDateProd.date, idDateProd.production, COUNT(*)
FROM idDateProd INNER JOIN idDateProd AS idDateProd_1 ON idDateProd.id =
idDateProd_1.id
WHERE idDateProd.date >= idDateProd_1.date
GROUP BY idDateProd.id, idDateProd.date, idDateProd.production;

should do, as long as your 'id' match the group of sequential days.

I also assumed the first three columns were known, and that you were
looking
for the fourth one.

Vanderghast, Access MVP
Click to expand...

My current SQL is listed below. I would like to count the D_DATE
(where the first date is 1 and so on to time zero the data), grouping
by ENERTIA_NO. I apologize for the confusion, but I'm not very
familiar with the SQL format in Access. How can I modify this code to
include what you showed above?


SELECT dbo_AC_DAILY.PROPNUM, dbo_AC_PROPERTY.ENERTIA_NO,
dbo_AC_PROPERTY.MAP_QUAD, dbo_AC_PROPERTY.RESERVOIR,
dbo_AC_PROPERTY.SPUD_DATE, dbo_AC_PROPERTY.TIL_DATE,
dbo_AC_DAILY.D_DATE, dbo_AC_DAILY.GAS INTO WHOLE_KY_Hz_From2000
FROM (dbo_AC_DAILY INNER JOIN dbo_AC_PRODUCT ON dbo_AC_DAILY.PROPNUM =
dbo_AC_PRODUCT.PROPNUM) INNER JOIN dbo_AC_PROPERTY ON
dbo_AC_PRODUCT.PROPNUM = dbo_AC_PROPERTY.PROPNUM
WHERE (((dbo_AC_DAILY.D_DATE)>[SPUD_DATE]) AND
((dbo_AC_PROPERTY.WELLCLASS)="hor") AND ((dbo_AC_PROPERTY.STATE)="ky")
AND ((dbo_AC_PROPERTY.SPUD_DATE)>#1/1/2000#))
GROUP BY dbo_AC_DAILY.PROPNUM, dbo_AC_PROPERTY.ENERTIA_NO,
dbo_AC_PROPERTY.MAP_QUAD, dbo_AC_PROPERTY.RESERVOIR,
dbo_AC_PROPERTY.SPUD_DATE, dbo_AC_PROPERTY.TIL_DATE,
dbo_AC_DAILY.D_DATE, dbo_AC_DAILY.GAS
ORDER BY dbo_AC_PROPERTY.ENERTIA_NO, dbo_AC_PROPERTY.MAP_QUAD,
dbo_AC_PROPERTY.RESERVOIR;
 
M

Mike

You can write query based on another query. So, if the actual query does
what it should do, keep it unmodified and write ANOTHER query:

Bring your actual query TWICE in the new query, one will get an _1 appendto
its name.

Change the query to a Total query.

Drag the ENERTIA_NO  (probably what was your 'id' field, I assume, in the
first query), and  D_DATE field from the not _1 query into the grid, keep
the proposed GROUP BY.

Drag D_DATE from the not _1 query a second time, change the GROUP BY to
WHERE, and, in the criteria:
    >= [queryName_1].D_DATE
                ( use the real query name, with its     _1    at the end of
the name)

Link the two queries, in the upper part of the query designer, by dragging
the ENERTIA_NO from one of them and dropping it on the same field of the
other query. A black line will now link the two queries.

In the grid, a new column, type:
    COUNT(*)
and change the GROUP BY to EXPRESSION.

That's all.

That still assumes that ENERTIA_NO is such that sequentially un-interrupted
dates share the same value in that ENERTIA_NO.

Vanderghast, Access MVP


SELECT idDateProd.id, idDateProd.date, idDateProd.production, COUNT(*)
FROM idDateProd INNER JOIN idDateProd AS idDateProd_1 ON idDateProd.id =
idDateProd_1.id
WHERE idDateProd.date >= idDateProd_1.date
GROUP BY idDateProd.id, idDateProd.date, idDateProd.production;
Click to expand...
should do, as long as your 'id' match the group of sequential days.
Click to expand...
I also assumed the first three columns were known, and that you were
looking
for the fourth one.
Click to expand...
Vanderghast, Access MVP
Click to expand...

My current SQL is listed below.  I would like to count the D_DATE
(where the first date is 1 and so on to time zero the data), grouping
by ENERTIA_NO.  I apologize for the confusion, but I'm not very
familiar with the SQL format in Access.  How can I modify this code to
include what you showed above?

SELECT dbo_AC_DAILY.PROPNUM, dbo_AC_PROPERTY.ENERTIA_NO,
dbo_AC_PROPERTY.MAP_QUAD, dbo_AC_PROPERTY.RESERVOIR,
dbo_AC_PROPERTY.SPUD_DATE, dbo_AC_PROPERTY.TIL_DATE,
dbo_AC_DAILY.D_DATE, dbo_AC_DAILY.GAS INTO WHOLE_KY_Hz_From2000
FROM (dbo_AC_DAILY INNER JOIN dbo_AC_PRODUCT ON dbo_AC_DAILY.PROPNUM =
dbo_AC_PRODUCT.PROPNUM) INNER JOIN dbo_AC_PROPERTY ON
dbo_AC_PRODUCT.PROPNUM = dbo_AC_PROPERTY.PROPNUM
WHERE (((dbo_AC_DAILY.D_DATE)>[SPUD_DATE]) AND
((dbo_AC_PROPERTY.WELLCLASS)="hor") AND ((dbo_AC_PROPERTY.STATE)="ky")
AND ((dbo_AC_PROPERTY.SPUD_DATE)>#1/1/2000#))
GROUP BY dbo_AC_DAILY.PROPNUM, dbo_AC_PROPERTY.ENERTIA_NO,
dbo_AC_PROPERTY.MAP_QUAD, dbo_AC_PROPERTY.RESERVOIR,
dbo_AC_PROPERTY.SPUD_DATE, dbo_AC_PROPERTY.TIL_DATE,
dbo_AC_DAILY.D_DATE, dbo_AC_DAILY.GAS
ORDER BY dbo_AC_PROPERTY.ENERTIA_NO, dbo_AC_PROPERTY.MAP_QUAD,
dbo_AC_PROPERTY.RESERVOIR;
Click to expand...

Thanks vanderghast. Worked perfectly.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

functions in queries 1
Selecting first record from a sorted query 6
Running sum running by date order 1
Calculate # of Hiolidays within a Date Range 1
Count query help 3
Help with totals function in query 2
Excel Formula - Count Dates from another Sheet 8
Looping through data with calculations 1

Top