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
8  4118 
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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@hercules.bti nternet.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
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*********@Se eSig.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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@hercules.bti nternet.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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@hercules.bti nternet.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.
Thanks John what's a subquery tho?
Tony
"John Winterbottom" <as******@hotma il.com> wrote in message
news:30******** *****@uni-berlin.de... "Tony Williams" <tw@tcpinvalid. com> wrote in message
news:cn******** **@hercules.bti nternet.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.
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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@titan.btinte rnet.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*********@Se eSig.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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@hercules.bti nternet.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
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*********@Se eSig.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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@titan.btinte rnet.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*********@Se eSig.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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@hercules.bti nternet.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" <tw@tcpinvalid. com> wrote in message
news:cn******** *@hercules.btin ternet.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.unitPric e*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.
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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@titan.btinte rnet.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*********@Se eSig.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" <tw@tcpinvalid. com> wrote in message
news:cn******** **@titan.btinte rnet.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*********@Se eSig.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" <tw@tcpinvalid. com> wrote in message
>> news:cn******** **@hercules.bti nternet.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: paul |
last post by:
I have a query that takes monthly totals and rolls them up to get a
balance at a specific time code
eg:
dt bucket mon_ttl
--- ------ -------
199903 0192 -172527
199906 0192 546707
199909 0192 -278684
199910 0192 1100139
|
by: ddecoste |
last post by:
I have done a bunch of looking but I am confused as to the correct way
I should proceed with this problem.
I have query that returns: It is sorted by ordnum then, linenum, then
date and it creates a table called Temptable.
ordnum linenum qtyord Date qtyship
1234 1 500 2/1/05 150
1234 1 500 2/2/05 200
1234 2 300 2/5/05 130
|
by: u473 |
last post by:
Running Totals by date misbehaving
I applied to the letter the sample code given
on http://support.microsoft.com/?kbid=290136
but it seems the running total breaks when the day number
is less than the previous date
Example using one Table (Access 2000):
|
by: New Guy |
last post by:
I'm trying to work with a system that somebody else built and I am
confounded by the following problem:
There is a table of payments and a table of charges. Each client has
charges and payments during the month.
I'd like to get the totals of the payments and of the charges for each
client. When I run the following query, I get huge numbers that
appear as if the join is not working correctly.
|
by: KashMarsh |
last post by:
Trying to show running totals on a report, except it needs to show one
total amount and values being subtracted from it. For example,
the report shows a Total Inventory amount (TotInvAmt). And then
amounts for each month for the orders like JanAmt, FebAmt, etc. I want
to show under each month column the following:
(TotInvAmt)-(JanAmt)=B ----result shows under the Jan column
B-(FebAmt)=C ---------result shows under the Feb column...
| | |
by: BerkshireGuy |
last post by:
I have the following code:
Dim strSQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined,
intNumOfWasted, intNumOfApproved As String
Dim QDF As QueryDef
Dim PARAM As Parameter
|
by: Jana |
last post by:
Using Access 97.
Background:
I have a main report called rptTrustHeader with a subreport
rptTrustDetails in the Details section of the main report. The main
report is grouped by MasterClientID. There are also several other
subreports in the main report's footer. The rptTrustDetails subreport
has two grouping levels MasterClientID and ClientID, with headers and
footers for each grouping level. In the ClientID footer, I have an...
|
by: mochatrpl |
last post by:
I am looking for a way to make a query / report display the running
average for total dollars.
I have already set up a query to provide totals dollars per day from
which a report graphly shows the dollars per week. How do I then take
the dollars and get a running average for the year?
- Randy
|
by: JJM0926 |
last post by:
I'm trying to create a running totals query in access 97. I have
followed the directions on how to do it from Microsofts website
article id 138911. I took their code they had and replaced it with my
fields. When I try to run it I get #errors in my RunTot column. I'm
kinda new to this. Not sure if maybe I mistyped something wrong or is
there a better way to do this? I have pasted the code. Any help would
be greatly appreciated....
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
