Running Totals Query Access97

[JJM0926]

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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yyyy",[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");

[Tom van Stiphout]

On Wed, 20 Jun 2007 14:24:19 -0000, JJM0926 <jj*****@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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yyyy",[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]

On Jun 20, 9:30 am, Tom van Stiphout <[e-mail]:

On Wed, 20 Jun 2007 14:24:19 -0000, JJM0926 <[e-mail]:

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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yyyy",[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]

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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yyyy",[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]

On Jun 20, 10:02 am, salad <[e-mail]:

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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yyyy",[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]

JJM0926 wrote:

On Jun 20, 10:02 am, salad <[e-mail]:

>>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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yyyy",[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.

[JJM0926]

On Jun 20, 5:11 pm, salad <[e-mail]:

JJM0926 wrote:

On Jun 20, 10:02 am, salad <[e-mail]:

>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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate
FROM [Quotes & Orders]
WHERE (((DatePart("yyyy",[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.

[salad]

JJM0926 wrote:

On Jun 20, 5:11 pm, salad <[e-mail]:

>>JJM0926 wrote:

>>>On Jun 20, 10:02 am, salad <[e-mail]:

>>>>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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
>Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
>Format([Inbound Date],"mmm") AS FDate

>FROM [Quotes & Orders]

>WHERE (((DatePart("yyyy",[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.IDate, QueryTest1.Rep, QueryTest1.YearDate,
QueryTest1.MonDate, QueryTest1.SumPrice,
NZ(DSum("SumPrice","QueryTest1","Idate < #" & [Idate] & "#"),0) AS
RunTotByDate, NZ(DSum("SumPrice","QueryTest1","Year([Idate]) &
Month([Idate]) < " & Year([Idate]) & Month([Idate])),0) AS RunTotByMonth
FROM QueryTest1
WHERE (((QueryTest1.Rep)=[Enter Rep]) AND ((QueryTest1.YearDate)=[Enter
Year]));

[JJM0926]

On Jun 21, 1:07 pm, salad <[e-mail]:

JJM0926 wrote:

On Jun 20, 5:11 pm, salad <[e-mail]:

>JJM0926 wrote:

>>On Jun 20, 10:02 am, salad <[e-mail]:

>>>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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
Format([Inbound Date],"mmm") AS FDate

>>>>FROM [Quotes & Orders]

>>>>WHERE (((DatePart("yyyy",[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.IDate, QueryTest1.Rep, QueryTest1.YearDate,
QueryTest1.MonDate, QueryTest1.SumPrice,
NZ(DSum("SumPrice","QueryTest1","Idate < #" & [Idate] & "#"),0) AS
RunTotByDate, NZ(DSum("SumPrice","QueryTest1","Year([Idate]) &
Month([Idate]) < " & Year([Idate]) & Month([Idate])),0) AS RunTotByMonth
FROM QueryTest1
WHERE (((QueryTest1.Rep)=[Enter Rep]) AND ((QueryTest1.YearDate)=[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]

JJM0926 wrote:

On Jun 21, 1:07 pm, salad <[e-mail]:

>>JJM0926 wrote:

>>>On Jun 20, 5:11 pm, salad <[e-mail]:

>>>>JJM0926 wrote:

>>>>>On Jun 20, 10:02 am, salad <[e-mail]:

>>>>>>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","Datepart('m',[Inbound Date])<=" & [Amonth] & "And
>>>Datepart('yyyy',[Inbound date])<=" & [ayear] & "") AS RunTot,
>>>Format([Inbound Date],"mmm") AS FDate

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

>>>>>>>WHERE (((DatePart("yyyy",[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.IDate, QueryTest1.Rep, QueryTest1.YearDate,
QueryTest1.MonDate, QueryTest1.SumPrice,
NZ(DSum("SumPrice","QueryTest1","Idate < #" & [Idate] & "#"),0) AS
RunTotByDate, NZ(DSum("SumPrice","QueryTest1","Year([Idate]) &
Month([Idate]) < " & Year([Idate]) & Month([Idate])),0) AS RunTotByMonth
FROM QueryTest1
WHERE (((QueryTest1.Rep)=[Enter Rep]) AND ((QueryTest1.YearDate)=[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.