Running Totals Query Access97

**Tom van Stiphout** · Jun 20 '07, 02:35 PM

Re: Running Totals Query Access97

On Wed, 20 Jun 2007 14:24:19 -0000, JJM0926 <jjm0926@gmail. comwrote:

You're starting the column expression for RunTot with a double-quote,
whereas the similar AYear does not.

-Tom.

>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. Thanks.
>
>SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
>Date]) AS AMonth, DSum("Contract Price","Quotes &
>Orders","Datep art('m',[Inbound Date])<=" & [Amonth] & "And
>Datepart('yyyy ',[Inbound date])<=" & [ayear] & "") AS RunTot,
>Format([Inbound Date],"mmm") AS FDate
>FROM [Quotes & Orders]
>WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
>GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
>Format([Inbound Date],"mmm")
>HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
>& Orders].[Sales Rep])=[Enter Salesman]))
>ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
>Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");

**JJM0926** · Jun 20 '07, 02:55 PM

Re: Running Totals Query Access97

On Jun 20, 9:30 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:

On Wed, 20 Jun 2007 14:24:19 -0000, JJM0926 <jjm0...@gmail. comwrote:
>
You're starting the column expression for RunTot with a double-quote,
whereas the similar AYear does not.
>
-Tom.
>
>
>

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. Thanks.

>

SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
Date]) AS AMonth, DSum("Contract Price","Quotes &
Orders","Datepa rt('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy' ,[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
Format([Inbound Date],"mmm")
HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
& Orders].[Sales Rep])=[Enter Salesman]))
ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");- Hide quoted text -

>
- Show quoted text -

When I remove the double quotes I get a syntax error and it will not
even allow me to run the query. Thanks.

**salad** · Jun 20 '07, 03:05 PM

Re: Running Totals Query Access97

JJM0926 wrote:

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. Thanks.
>
SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
Date]) AS AMonth, DSum("Contract Price","Quotes &
Orders","Datepa rt('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy' ,[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
Format([Inbound Date],"mmm")
HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
& Orders].[Sales Rep])=[Enter Salesman]))
ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");
>

AYear and Amonth haven't been defined when using them in dsum. BTW, why
are you using DatePart when Year(Datefield) and Month(Datefield ) is more
readable and does the same thing?

The more I look at your query the bigger the mess it becomes. And your
orderby line is goofy.

I'd suggest you create a query that creates AYear and Amonth columns and
asks for the salesperson and year and does any and all filtering (Ex:
Query1). Then create another query (Query2) and make the first query
the data table you total and group on.

**JJM0926** · Jun 20 '07, 04:15 PM

Re: Running Totals Query Access97

On Jun 20, 10:02 am, salad <o...@vinegar.c omwrote:

JJM0926 wrote:

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. Thanks.

>

SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
Date]) AS AMonth, DSum("Contract Price","Quotes &
Orders","Datepa rt('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy' ,[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
Format([Inbound Date],"mmm")
HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
& Orders].[Sales Rep])=[Enter Salesman]))
ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");

>
AYear and Amonth haven't been defined when using them in dsum. BTW, why
are you using DatePart when Year(Datefield) and Month(Datefield ) is more
readable and does the same thing?
>
The more I look at your query the bigger the mess it becomes. And your
orderby line is goofy.
>
I'd suggest you create a query that creates AYear and Amonth columns and
asks for the salesperson and year and does any and all filtering (Ex:
Query1). Then create another query (Query2) and make the first query
the data table you total and group on.- Hide quoted text -
>
- Show quoted text -

This is the method that I got from Microsoft's website. I'm just
trying to figure out why I am getting the #error in my RunTot column.
As I mentioned I'm kinda new to access and sql statements. All I did
was take Microsoft's code and change them to match my column
headings. Thanks.

**salad** · Jun 20 '07, 10:15 PM

Re: Running Totals Query Access97

JJM0926 wrote:

On Jun 20, 10:02 am, salad <o...@vinegar.c omwrote:
>

>>JJM0926 wrote:
>>

>>>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. Thanks.

>>

>>>SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>>Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
>>>Date]) AS AMonth, DSum("Contract Price","Quotes &
>>>Orders","Dat epart('m',[Inbound Date])<=" & [Amonth] & "And
>>>Datepart('yy yy',[Inbound date])<=" & [ayear] & "") AS RunTot,
>>>Format([Inbound Date],"mmm") AS FDate
>>>FROM [Quotes & Orders]
>>>WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
>>>GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>>Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
>>>Format([Inbound Date],"mmm")
>>>HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
>>>& Orders].[Sales Rep])=[Enter Salesman]))
>>>ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
>>>Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");

>>
>>AYear and Amonth haven't been defined when using them in dsum. BTW, why
>>are you using DatePart when Year(Datefield) and Month(Datefield ) is more
>>readable and does the same thing?
>>
>>The more I look at your query the bigger the mess it becomes. And your
>>orderby line is goofy.
>>
>>I'd suggest you create a query that creates AYear and Amonth columns and
>>asks for the salesperson and year and does any and all filtering (Ex:
>>Query1). Then create another query (Query2) and make the first query
>>the data table you total and group on.- Hide quoted text -
>>
>>- Show quoted text -

>
>
This is the method that I got from Microsoft's website. I'm just
trying to figure out why I am getting the #error in my RunTot column.
As I mentioned I'm kinda new to access and sql statements. All I did
was take Microsoft's code and change them to match my column
headings. Thanks.
>

I assume there can be multiple records for the inbound date for a sales rep.

Query1. This select all of the records and rolls up the totals for the
rep you filter on by the inbound date.
SELECT [Inbound Date], [Sales Rep], Year([Inbound Date]) &
Month([Inbound Date]) As YyyyMm, Sum(Contract Price) As CPSum
FROM [Quotes & Orders]
WHERE Year([Inbound Date])=[Enter Year] And [Sales Rep] = [Enter Salesman]
Group By [Inbound Date], [Sales Rep], Year([Inbound Date]) &
Month([Inbound Date])

Query2. This creates the running sum
Select [Inbound Date], [Sales Rep], YyyyMm, CPSum,
DSum("CPSum","Q uery1","YyyyMm < " & [YyyyMm]) As RunTot
From Query1
Order By [Inbound Date]

This is just a suggestion. Lose the spaces in your field names.
Instead of
Inbound Date or Sales Rep
use
InboundDate or SalesRep

If you work in Access for a while you'll discover you add a lot of work
and will expend a lot of energy by creating field names with spaces.

**JJM0926** · Jun 21 '07, 02:05 PM

Re: Running Totals Query Access97

On Jun 20, 5:11 pm, salad <o...@vinegar.c omwrote:

JJM0926 wrote:

On Jun 20, 10:02 am, salad <o...@vinegar.c omwrote:

>

>JJM0926 wrote:

>

>>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. Thanks.

>

>>SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
>>Date]) AS AMonth, DSum("Contract Price","Quotes &
>>Orders","Date part('m',[Inbound Date])<=" & [Amonth] & "And
>>Datepart('yyy y',[Inbound date])<=" & [ayear] & "") AS RunTot,
>>Format([Inbound Date],"mmm") AS FDate
>>FROM [Quotes & Orders]
>>WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
>>GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
>>Format([Inbound Date],"mmm")
>>HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
>>& Orders].[Sales Rep])=[Enter Salesman]))
>>ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
>>Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");

>

>AYear and Amonth haven't been defined when using them in dsum. BTW, why
>are you using DatePart when Year(Datefield) and Month(Datefield ) is more
>readable and does the same thing?

>

>The more I look at your query the bigger the mess it becomes. And your
>orderby line is goofy.

>

>I'd suggest you create a query that creates AYear and Amonth columns and
>asks for the salesperson and year and does any and all filtering (Ex:
>Query1). Then create another query (Query2) and make the first query
>the data table you total and group on.- Hide quoted text -

>

>- Show quoted text -

>

This is the method that I got from Microsoft's website. I'm just
trying to figure out why I am getting the #error in my RunTot column.
As I mentioned I'm kinda new to access and sql statements. All I did
was take Microsoft's code and change them to match my column
headings. Thanks.

>
I assume there can be multiple records for the inbound date for a sales rep.
>
Query1. This select all of the records and rolls up the totals for the
rep you filter on by the inbound date.
SELECT [Inbound Date], [Sales Rep], Year([Inbound Date]) &
Month([Inbound Date]) As YyyyMm, Sum(Contract Price) As CPSum
FROM [Quotes & Orders]
WHERE Year([Inbound Date])=[Enter Year] And [Sales Rep] = [Enter Salesman]
Group By [Inbound Date], [Sales Rep], Year([Inbound Date]) &
Month([Inbound Date])
>
Query2. This creates the running sum
Select [Inbound Date], [Sales Rep], YyyyMm, CPSum,
DSum("CPSum","Q uery1","YyyyMm < " & [YyyyMm]) As RunTot
From Query1
Order By [Inbound Date]
>
This is just a suggestion. Lose the spaces in your field names.
Instead of
Inbound Date or Sales Rep
use
InboundDate or SalesRep
>
If you work in Access for a while you'll discover you add a lot of work
and will expend a lot of energy by creating field names with spaces.- Hide quoted text -
>
- Show quoted text -

Thanks Salad. Your first query kinda worked, except it thru in the
year with the month in the same column. Is there a way to separate
the year in one column and the month in another? Your second query
didn't quite work so well. When I ran it I still got #error on
RunTot. This the error message that it gave me. The expression you
entered as a query parameter produce this error: "The object doesn't
contain the Automation object 'Enter Year." Thanks again for you help.

**salad** · Jun 21 '07, 06:15 PM

Re: Running Totals Query Access97

JJM0926 wrote:

On Jun 20, 5:11 pm, salad <o...@vinegar.c omwrote:
>

>>JJM0926 wrote:
>>

>>>On Jun 20, 10:02 am, salad <o...@vinegar.c omwrote:

>>

>>>>JJM0926 wrote:

>>

>>>>>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. Thanks.

>>

>>>>>SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>>>>Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
>>>>>Date]) AS AMonth, DSum("Contract Price","Quotes &
>>>>>Orders","D atepart('m',[Inbound Date])<=" & [Amonth] & "And
>>>>>Datepart(' yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
>>>>>Format([Inbound Date],"mmm") AS FDate
>>>>
>>>>>FROM [Quotes & Orders]
>>>>
>>>>>WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
>>>>>GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>>>>Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
>>>>>Format([Inbound Date],"mmm")
>>>>>HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
>>>>>& Orders].[Sales Rep])=[Enter Salesman]))
>>>>>ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
>>>>>Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");

>>

>>>>AYear and Amonth haven't been defined when using them in dsum. BTW, why
>>>>are you using DatePart when Year(Datefield) and Month(Datefield ) is more
>>>>readable and does the same thing?

>>

>>>>The more I look at your query the bigger the mess it becomes. And your
>>>>orderby line is goofy.

>>

>>>>I'd suggest you create a query that creates AYear and Amonth columns and
>>>>asks for the salesperson and year and does any and all filtering (Ex:
>>>>Query1). Then create another query (Query2) and make the first query
>>>>the data table you total and group on.- Hide quoted text -

>>

>>>>- Show quoted text -

>>

>>>This is the method that I got from Microsoft's website. I'm just
>>>trying to figure out why I am getting the #error in my RunTot column.
>>>As I mentioned I'm kinda new to access and sql statements. All I did
>>>was take Microsoft's code and change them to match my column
>>>headings. Thanks.

>>
>>I assume there can be multiple records for the inbound date for a sales rep.
>>
>>Query1. This select all of the records and rolls up the totals for the
>>rep you filter on by the inbound date.
>>SELECT [Inbound Date], [Sales Rep], Year([Inbound Date]) &
>>Month([Inbound Date]) As YyyyMm, Sum(Contract Price) As CPSum
> FROM [Quotes & Orders]
> WHERE Year([Inbound Date])=[Enter Year] And [Sales Rep] = [Enter Salesman]
>>Group By [Inbound Date], [Sales Rep], Year([Inbound Date]) &
>>Month([Inbound Date])
>>
>>Query2. This creates the running sum
>>Select [Inbound Date], [Sales Rep], YyyyMm, CPSum,
>>DSum("CPSum", "Query1","YyyyM m < " & [YyyyMm]) As RunTot
> From Query1
> Order By [Inbound Date]
>>
>>This is just a suggestion. Lose the spaces in your field names.
>>Instead of
> Inbound Date or Sales Rep
>>use
> InboundDate or SalesRep
>>
>>If you work in Access for a while you'll discover you add a lot of work
>>and will expend a lot of energy by creating field names with spaces.- Hide quoted text -
>>
>>- Show quoted text -

>
>
Thanks Salad. Your first query kinda worked, except it thru in the
year with the month in the same column. Is there a way to separate
the year in one column and the month in another? Your second query
didn't quite work so well. When I ran it I still got #error on
RunTot. This the error message that it gave me. The expression you
entered as a query parameter produce this error: "The object doesn't
contain the Automation object 'Enter Year." Thanks again for you help.
>

I create a table called Test. I then created 2 queries. The field
names are slightly different but you should be able to figure them out.

I actually created 2 running tot columns. First col is a running date
between dates. The other is a running sum between months. The reason
this was done is because there is a group on Idate, thus making a totals
record for each date.

QueryTest1
SELECT Test.IDate, Test.Rep, Sum(Test.Price) AS SumPrice, Year([Idate])
AS YearDate, Month([Idate]) AS MonDate
FROM Test
GROUP BY Test.IDate, Test.Rep, Year([Idate]), Month([Idate]);

QueryTest2
SELECT QueryTest1.IDat e, QueryTest1.Rep, QueryTest1.Year Date,
QueryTest1.MonD ate, QueryTest1.SumP rice,
NZ(DSum("SumPri ce","QueryTest1 ","Idate < #" & [Idate] & "#"),0) AS
RunTotByDate, NZ(DSum("SumPri ce","QueryTest1 ","Year([Idate]) &
Month([Idate]) < " & Year([Idate]) & Month([Idate])),0) AS RunTotByMonth
FROM QueryTest1
WHERE (((QueryTest1.R ep)=[Enter Rep]) AND ((QueryTest1.Ye arDate)=[Enter
Year]));

**JJM0926** · Jun 21 '07, 08:25 PM

Re: Running Totals Query Access97

On Jun 21, 1:07 pm, salad <o...@vinegar.c omwrote:

JJM0926 wrote:

On Jun 20, 5:11 pm, salad <o...@vinegar.c omwrote:

>

>JJM0926 wrote:

>

>>On Jun 20, 10:02 am, salad <o...@vinegar.c omwrote:

>

>>>JJM0926 wrote:

>

>>>>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. Thanks.

>

>>>>SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>>>Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
>>>>Date]) AS AMonth, DSum("Contract Price","Quotes &
>>>>Orders","Da tepart('m',[Inbound Date])<=" & [Amonth] & "And
>>>>Datepart('y yyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
>>>>Format([Inbound Date],"mmm") AS FDate

>

>>>>FROM [Quotes & Orders]

>

>>>>WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
>>>>GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>>>Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
>>>>Format([Inbound Date],"mmm")
>>>>HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
>>>>& Orders].[Sales Rep])=[Enter Salesman]))
>>>>ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
>>>>Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");

>

>>>AYear and Amonth haven't been defined when using them in dsum. BTW, why
>>>are you using DatePart when Year(Datefield) and Month(Datefield ) is more
>>>readable and does the same thing?

>

>>>The more I look at your query the bigger the mess it becomes. And your
>>>orderby line is goofy.

>

>>>I'd suggest you create a query that creates AYear and Amonth columns and
>>>asks for the salesperson and year and does any and all filtering (Ex:
>>>Query1). Then create another query (Query2) and make the first query
>>>the data table you total and group on.- Hide quoted text -

>

>>>- Show quoted text -

>

>>This is the method that I got from Microsoft's website. I'm just
>>trying to figure out why I am getting the #error in my RunTot column.
>>As I mentioned I'm kinda new to access and sql statements. All I did
>>was take Microsoft's code and change them to match my column
>>headings. Thanks.

>

>I assume there can be multiple records for the inbound date for a sales rep.

>

>Query1. This select all of the records and rolls up the totals for the
>rep you filter on by the inbound date.
>SELECT [Inbound Date], [Sales Rep], Year([Inbound Date]) &
>Month([Inbound Date]) As YyyyMm, Sum(Contract Price) As CPSum
FROM [Quotes & Orders]
WHERE Year([Inbound Date])=[Enter Year] And [Sales Rep] = [Enter Salesman]
>Group By [Inbound Date], [Sales Rep], Year([Inbound Date]) &
>Month([Inbound Date])

>

>Query2. This creates the running sum
>Select [Inbound Date], [Sales Rep], YyyyMm, CPSum,
>DSum("CPSum"," Query1","YyyyMm < " & [YyyyMm]) As RunTot
From Query1
Order By [Inbound Date]

>

>This is just a suggestion. Lose the spaces in your field names.
>Instead of
Inbound Date or Sales Rep
>use
InboundDate or SalesRep

>

>If you work in Access for a while you'll discover you add a lot of work
>and will expend a lot of energy by creating field names with spaces.- Hide quoted text -

>

>- Show quoted text -

>

Thanks Salad. Your first query kinda worked, except it thru in the
year with the month in the same column. Is there a way to separate
the year in one column and the month in another? Your second query
didn't quite work so well. When I ran it I still got #error on
RunTot. This the error message that it gave me. The expression you
entered as a query parameter produce this error: "The object doesn't
contain the Automation object 'Enter Year." Thanks again for you help.

>
I create a table called Test. I then created 2 queries. The field
names are slightly different but you should be able to figure them out.
>
I actually created 2 running tot columns. First col is a running date
between dates. The other is a running sum between months. The reason
this was done is because there is a group on Idate, thus making a totals
record for each date.
>
QueryTest1
SELECT Test.IDate, Test.Rep, Sum(Test.Price) AS SumPrice, Year([Idate])
AS YearDate, Month([Idate]) AS MonDate
FROM Test
GROUP BY Test.IDate, Test.Rep, Year([Idate]), Month([Idate]);
>
QueryTest2
SELECT QueryTest1.IDat e, QueryTest1.Rep, QueryTest1.Year Date,
QueryTest1.MonD ate, QueryTest1.SumP rice,
NZ(DSum("SumPri ce","QueryTest1 ","Idate < #" & [Idate] & "#"),0) AS
RunTotByDate, NZ(DSum("SumPri ce","QueryTest1 ","Year([Idate]) &
Month([Idate]) < " & Year([Idate]) & Month([Idate])),0) AS RunTotByMonth
FROM QueryTest1
WHERE (((QueryTest1.R ep)=[Enter Rep]) AND ((QueryTest1.Ye arDate)=[Enter
Year]));- Hide quoted text -
>
- Show quoted text -

Thanks Salad. The queries worked, but I think the calculation is
wrong. Shouldn't my first record SumPrice be the same as RunTotByDate
and RunTotMonth to start with? Would it be possible to email part of
the table? I don't know where the numbers are pulling from but I
don't believe they are correct. I would greatly appreciate it. Thanks.

**salad** · Jun 22 '07, 01:55 AM

Re: Running Totals Query Access97

JJM0926 wrote:

On Jun 21, 1:07 pm, salad <o...@vinegar.c omwrote:
>

>>JJM0926 wrote:
>>

>>>On Jun 20, 5:11 pm, salad <o...@vinegar.c omwrote:

>>

>>>>JJM0926 wrote:

>>

>>>>>On Jun 20, 10:02 am, salad <o...@vinegar.c omwrote:

>>

>>>>>>JJM0926 wrote:

>>

>>>>>>>I'm trying to create a running totals query in access 97. I have
>>>>>>>follow ed the directions on how to do it from Microsofts website
>>>>>>>articl e 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. Thanks.

>>

>>>>>>>SELECT [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>>>>>>Rep], DatePart("yyyy" ,[Inbound Date]) AS AYear, DatePart("m",[Inbound
>>>>>>>Date]) AS AMonth, DSum("Contract Price","Quotes &
>>>>>>>Orders", "Datepart(' m',[Inbound Date])<=" & [Amonth] & "And
>>>>>>>Datepart ('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
>>>>>>>Format ([Inbound Date],"mmm") AS FDate

>>

>>>>>>>FROM [Quotes & Orders]

>>

>>>>>>>WHERE (((DatePart("yy yy",[Inbound Date]))=[Enter Year]))
>>>>>>>GROUP BY [Quotes & Orders].[Inbound Date], [Quotes & Orders].[Sales
>>>>>>>Rep], DatePart("yyyy" ,[Inbound Date]), DatePart("m",[Inbound Date]),
>>>>>>>Format ([Inbound Date],"mmm")
>>>>>>>HAVING ((([Quotes & Orders].[Inbound Date]) Is Not Null) AND (([Quotes
>>>>>>>& Orders].[Sales Rep])=[Enter Salesman]))
>>>>>>>ORDER BY [Quotes & Orders].[Inbound Date], DatePart("yyyy" ,[Inbound
>>>>>>>Date]), DatePart("m",[Inbound Date]), Format([Inbound Date],"mmm");

>>

>>>>>>AYear and Amonth haven't been defined when using them in dsum. BTW, why
>>>>>>are you using DatePart when Year(Datefield) and Month(Datefield ) is more
>>>>>>readabl e and does the same thing?

>>

>>>>>>The more I look at your query the bigger the mess it becomes. And your
>>>>>>orderby line is goofy.

>>

>>>>>>I'd suggest you create a query that creates AYear and Amonth columns and
>>>>>>asks for the salesperson and year and does any and all filtering (Ex:
>>>>>>Query1) . Then create another query (Query2) and make the first query
>>>>>>the data table you total and group on.- Hide quoted text -

>>

>>>>>>- Show quoted text -

>>

>>>>>This is the method that I got from Microsoft's website. I'm just
>>>>>trying to figure out why I am getting the #error in my RunTot column.
>>>>>As I mentioned I'm kinda new to access and sql statements. All I did
>>>>>was take Microsoft's code and change them to match my column
>>>>>headings . Thanks.

>>

>>>>I assume there can be multiple records for the inbound date for a sales rep.

>>

>>>>Query1. This select all of the records and rolls up the totals for the
>>>>rep you filter on by the inbound date.
>>>>SELECT [Inbound Date], [Sales Rep], Year([Inbound Date]) &
>>>>Month([Inbound Date]) As YyyyMm, Sum(Contract Price) As CPSum
>>>FROM [Quotes & Orders]
>>>WHERE Year([Inbound Date])=[Enter Year] And [Sales Rep] = [Enter Salesman]
>>>>Group By [Inbound Date], [Sales Rep], Year([Inbound Date]) &
>>>>Month([Inbound Date])

>>

>>>>Query2. This creates the running sum
>>>>Select [Inbound Date], [Sales Rep], YyyyMm, CPSum,
>>>>DSum("CPSum ","Query1","Yyy yMm < " & [YyyyMm]) As RunTot
>>> From Query1
>>> Order By [Inbound Date]

>>

>>>>This is just a suggestion. Lose the spaces in your field names.
>>>>Instead of
>>> Inbound Date or Sales Rep
>>>>use
>>> InboundDate or SalesRep

>>

>>>>If you work in Access for a while you'll discover you add a lot of work
>>>>and will expend a lot of energy by creating field names with spaces.- Hide quoted text -

>>

>>>>- Show quoted text -

>>

>>>Thanks Salad. Your first query kinda worked, except it thru in the
>>>year with the month in the same column. Is there a way to separate
>>>the year in one column and the month in another? Your second query
>>>didn't quite work so well. When I ran it I still got #error on
>>>RunTot. This the error message that it gave me. The expression you
>>>entered as a query parameter produce this error: "The object doesn't
>>>contain the Automation object 'Enter Year." Thanks again for you help.

>>
>>I create a table called Test. I then created 2 queries. The field
>>names are slightly different but you should be able to figure them out.
>>
>>I actually created 2 running tot columns. First col is a running date
>>between dates. The other is a running sum between months. The reason
>>this was done is because there is a group on Idate, thus making a totals
>>record for each date.
>>
>>QueryTest1
>>SELECT Test.IDate, Test.Rep, Sum(Test.Price) AS SumPrice, Year([Idate])
>>AS YearDate, Month([Idate]) AS MonDate
>>FROM Test
>>GROUP BY Test.IDate, Test.Rep, Year([Idate]), Month([Idate]);
>>
>>QueryTest2
>>SELECT QueryTest1.IDat e, QueryTest1.Rep, QueryTest1.Year Date,
>>QueryTest1.Mo nDate, QueryTest1.SumP rice,
>>NZ(DSum("SumP rice","QueryTes t1","Idate < #" & [Idate] & "#"),0) AS
>>RunTotByDat e, NZ(DSum("SumPri ce","QueryTest1 ","Year([Idate]) &
>>Month([Idate]) < " & Year([Idate]) & Month([Idate])),0) AS RunTotByMonth
>>FROM QueryTest1
>>WHERE (((QueryTest1.R ep)=[Enter Rep]) AND ((QueryTest1.Ye arDate)=[Enter
>>Year]));- Hide quoted text -
>>
>>- Show quoted text -

>
>
Thanks Salad. The queries worked, but I think the calculation is
wrong. Shouldn't my first record SumPrice be the same as RunTotByDate
and RunTotMonth to start with? Would it be possible to email part of
the table? I don't know where the numbers are pulling from but I
don't believe they are correct. I would greatly appreciate it. Thanks.
>

I'm not sure if it should. The reason is that we are looking at records
"Before" the start date or "Before" the current month we are checking.
I guess it depends on adjusting it to meet your requirements. If I had
used <= instead of <, (like you did in the original post) then it should
work OK...I think.

Running Totals Query Access97

Running Totals Query Access97

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment