Running totals in a query?

[Tony Williams]

I have a database that hold s figures for each quarter. I want to create a
query whereby the user can ask for the running total of the data upto the
quarter they enter into a prompt. The database stores the quarter name in
txtmonthlabel (a date field) and the quarters totals in txtdomic (a number
field) EG If the user enters March 2004 they get figures upto March 2004, if
they enter June 2004 they get total upto June 2004, in other words the query
would add March's figures to June's figures to give the totals, not just
show June's figures.
Can this be done in a query and what would the prompt criteria be?
TIA
Tony Williams

[Allen Browne]

You have a GROUP BY query, so drag the date field into the grid.
In the Total row, under this field, choose Where.
In the Criteria row, enter:

= [Quarter Starting Date] And < DateAdd("q", 1, [Quarter Starting Date])

To ensure the user enters a valid date, choose Paramters from the Query
menu, and enter:
[Quarter Starting Date] Date

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Williams" <[e-mail]> wrote in message
news:cn**********@hercules.btinternet.com...I have a database that hold s figures for each quarter. I want to create a
query whereby the user can ask for the running total of the data upto the
quarter they enter into a prompt. The database stores the quarter name in
txtmonthlabel (a date field) and the quarters totals in txtdomic (a number
field) EG If the user enters March 2004 they get figures upto March 2004,
if
they enter June 2004 they get total upto June 2004, in other words the
query
would add March's figures to June's figures to give the totals, not just
show June's figures.
Can this be done in a query and what would the prompt criteria be?
TIA
Tony Williams

[Tony Williams]

Thanks Allen, have followed your guide but when I enter say June 2003 I only
get the figures for the quarterJune 2003, I don't get the figures for
January to June 2003. In other words I should be getting the total of
March's figures and June's Figures
Any help?
TIA
Tony Williams
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

You have a GROUP BY query, so drag the date field into the grid.
In the Total row, under this field, choose Where.
In the Criteria row, enter:

>= [Quarter Starting Date] And < DateAdd("q", 1, [Quarter Starting

Date])

To ensure the user enters a valid date, choose Paramters from the Query
menu, and enter:
[Quarter Starting Date] Date

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Williams" <[e-mail]> wrote in message
news:cn**********@hercules.btinternet.com...

I have a database that hold s figures for each quarter. I want to create a query whereby the user can ask for the running total of the data upto the quarter they enter into a prompt. The database stores the quarter name in txtmonthlabel (a date field) and the quarters totals in txtdomic (a number field) EG If the user enters March 2004 they get figures upto March 2004, if
they enter June 2004 they get total upto June 2004, in other words the
query
would add March's figures to June's figures to give the totals, not just
show June's figures.
Can this be done in a query and what would the prompt criteria be?
TIA
Tony Williams

[John Winterbottom]

"Tony Williams" <[e-mail]> wrote in message
news:cn**********@hercules.btinternet.com...

I have a database that hold s figures for each quarter. I want to create a
query whereby the user can ask for the running total of the data upto the
quarter they enter into a prompt. The database stores the quarter name in
txtmonthlabel (a date field) and the quarters totals in txtdomic (a number
field) EG If the user enters March 2004 they get figures upto March 2004,
if
they enter June 2004 they get total upto June 2004, in other words the
query
would add March's figures to June's figures to give the totals, not just
show June's figures.
Can this be done in a query and what would the prompt criteria be?

You can do this easily, (for example with a subquery). Post your table
structure with some sample data and the output you need and someone will be
able to help.

[Tony Williams]

Thanks John what's a subquery tho?
Tony
"John Winterbottom" <as******@hotmail.com> wrote in message
news:30*************@uni-berlin.de...

"Tony Williams" <[e-mail]> wrote in message
news:cn**********@hercules.btinternet.com...

I have a database that hold s figures for each quarter. I want to create a query whereby the user can ask for the running total of the data upto the quarter they enter into a prompt. The database stores the quarter name in txtmonthlabel (a date field) and the quarters totals in txtdomic (a number field) EG If the user enters March 2004 they get figures upto March 2004, if
they enter June 2004 they get total upto June 2004, in other words the
query
would add March's figures to June's figures to give the totals, not just
show June's figures.
Can this be done in a query and what would the prompt criteria be?

You can do this easily, (for example with a subquery). Post your table
structure with some sample data and the output you need and someone will

be able to help.

[Allen Browne]

So you want all the values so far in this calendar year?
Ask the user for the ending date:

Between DateSerial(Year([EndDate]),1,1) And [EndDate]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Williams" <[e-mail]> wrote in message
news:cn**********@titan.btinternet.com...

Thanks Allen, have followed your guide but when I enter say June 2003 I
only
get the figures for the quarterJune 2003, I don't get the figures for
January to June 2003. In other words I should be getting the total of
March's figures and June's Figures
Any help?
TIA
Tony Williams
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

You have a GROUP BY query, so drag the date field into the grid.
In the Total row, under this field, choose Where.
In the Criteria row, enter:

>= [Quarter Starting Date] And < DateAdd("q", 1, [Quarter Starting

Date])

To ensure the user enters a valid date, choose Paramters from the Query
menu, and enter:
[Quarter Starting Date] Date
"Tony Williams" <[e-mail]> wrote in message
news:cn**********@hercules.btinternet.com...

>I have a database that hold s figures for each quarter. I want to create a > query whereby the user can ask for the running total of the data upto the > quarter they enter into a prompt. The database stores the quarter name in > txtmonthlabel (a date field) and the quarters totals in txtdomic (a number > field) EG If the user enters March 2004 they get figures upto March 2004, > if
> they enter June 2004 they get total upto June 2004, in other words the
> query
> would add March's figures to June's figures to give the totals, not
> just
> show June's figures.
> Can this be done in a query and what would the prompt criteria be?
> TIA
> Tony Williams

[Tony Williams]

Thanks Allen worked like a dream. If you get chance though could you explain
what's happening in that statement? Although it works I would like to try
and understand why it works.
Thanks
Tony
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

So you want all the values so far in this calendar year?
Ask the user for the ending date:

Between DateSerial(Year([EndDate]),1,1) And [EndDate]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Williams" <[e-mail]> wrote in message
news:cn**********@titan.btinternet.com...

Thanks Allen, have followed your guide but when I enter say June 2003 I
only
get the figures for the quarterJune 2003, I don't get the figures for
January to June 2003. In other words I should be getting the total of
March's figures and June's Figures
Any help?
TIA
Tony Williams
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

You have a GROUP BY query, so drag the date field into the grid.
In the Total row, under this field, choose Where.
In the Criteria row, enter:
>= [Quarter Starting Date] And < DateAdd("q", 1, [Quarter Starting
Date])

To ensure the user enters a valid date, choose Paramters from the Query
menu, and enter:
[Quarter Starting Date] Date
"Tony Williams" <[e-mail]> wrote in message
news:cn**********@hercules.btinternet.com...
>I have a database that hold s figures for each quarter. I want to create

a

> query whereby the user can ask for the running total of the data upto

the

> quarter they enter into a prompt. The database stores the quarter

name in

> txtmonthlabel (a date field) and the quarters totals in txtdomic (a

number

> field) EG If the user enters March 2004 they get figures upto March

2004,

> if
> they enter June 2004 they get total upto June 2004, in other words

the > query
> would add March's figures to June's figures to give the totals, not
> just
> show June's figures.
> Can this be done in a query and what would the prompt criteria be?
> TIA
> Tony Williams

[John Winterbottom]

"Tony Williams" <[e-mail]> wrote in message
news:cn*********@hercules.btinternet.com...

Thanks John what's a subquery tho?

From Access Help:

"SQL Subqueries
A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO,
INSERT...INTO, DELETE, or UPDATE statement or inside another subquery"

This example shows cumulative sales in the Northwind database:

----------------------------------------
select o.orderID, o.orderDate,
(
select sum(d2.unitPrice*d2.quantity*(100-d2.discount)/100)
from [Order Details] as d2
inner join orders as o2 on d2.orderID = o2.orderID
where o2.orderDate <= o.orderDate
)
from orders as o
----------------------------------------

This is a special case of subquery called a correlated subquery - the line

"where o2.orderDate <= o.orderDate"

restricts rows in the inner query based on the value of a column in the
outer query. A correlated subquery can be used to find running totals,
sliding aggregates etc.

[Allen Browne]

Since EndDate doesn't match anything else, Access assumes it's a parameter
and pops up a dialog for you to enter a value.

The Year() function extracts the year from a date.
The DateSerial() function builds a date from a year, month, and day.

The criteria therefore reads the year of the date you entered, and builds a
date for the 1st day of the year. The WHERE clause therefore asks for all
dates between the first of the year and the date you entered.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tony Williams" <[e-mail]> wrote in message
news:cn**********@titan.btinternet.com...

Thanks Allen worked like a dream. If you get chance though could you
explain
what's happening in that statement? Although it works I would like to try
and understand why it works.
Thanks
Tony
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

So you want all the values so far in this calendar year?
Ask the user for the ending date:

Between DateSerial(Year([EndDate]),1,1) And [EndDate]
"Tony Williams" <[e-mail]> wrote in message
news:cn**********@titan.btinternet.com...

> Thanks Allen, have followed your guide but when I enter say June 2003 I
> only
> get the figures for the quarterJune 2003, I don't get the figures for
> January to June 2003. In other words I should be getting the total of
> March's figures and June's Figures
> Any help?
> TIA
> Tony Williams
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:41***********************@per-qv1-newsreader-01.iinet.net.au...
>> You have a GROUP BY query, so drag the date field into the grid.
>> In the Total row, under this field, choose Where.
>> In the Criteria row, enter:
>> >= [Quarter Starting Date] And < DateAdd("q", 1, [Quarter Starting
>> Date])
>>
>> To ensure the user enters a valid date, choose Paramters from the
>> Query
>> menu, and enter:
>> [Quarter Starting Date] Date
>>
>>
>> "Tony Williams" <[e-mail]> wrote in message
>> news:cn**********@hercules.btinternet.com...
>> >I have a database that hold s figures for each quarter. I want to create > a
>> > query whereby the user can ask for the running total of the data
>> > upto
> the
>> > quarter they enter into a prompt. The database stores the quarter name > in
>> > txtmonthlabel (a date field) and the quarters totals in txtdomic (a
> number
>> > field) EG If the user enters March 2004 they get figures upto March
> 2004,
>> > if
>> > they enter June 2004 they get total upto June 2004, in other words the >> > query
>> > would add March's figures to June's figures to give the totals, not
>> > just
>> > show June's figures.
>> > Can this be done in a query and what would the prompt criteria be?
>> > TIA
>> > Tony Williams