Running Total Problem

[John Ortt]

Can anyone see the problem with my Running Total Query below?

(I have used the same formula for Month on the query qryStatsTotalClosed as
CurrentMonth on this one)

Thanks in advance,

John


SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',Month," &
[CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;

 

[Douglas J. Steele]

Month is a reserved word, and shouldn't be used as a field name, and you
can't refer to computed fields like that in queries: you need to repeat the
calculation.

For a good discussion on names to avoid for fields and variables, see what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[John Ortt]

Thanks Doug,

I have ammended the code to exclude the month keyword but it still doesn't
seem to be working. Any further ides.
I was wonderring if it could be something to do with the date format but
both dates are worked out in the same way.

I have copied the new code below.

Any further advice would be greatly appreciated.

Thanks

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',theMonth," &
[CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;

Month is a reserved word, and shouldn't be used as a field name, and you
can't refer to computed fields like that in queries: you need to repeat
the calculation.

For a good discussion on names to avoid for fields and variables, see what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can anyone see the problem with my Running Total Query below?

(I have used the same formula for Month on the query qryStatsTotalClosed
as CurrentMonth on this one)

Thanks in advance,

John


SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',Month," &
[CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;

 

[Douglas J. Steele]

What's the purpose of Datediff('d',theMonth," & [CurrentMonth] & ")>0")? I
suspect there's an easier way of arriving at the same thing.

I note that you missed my comment that you can't refer to computed fields by
name like that: you have to repeat the calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,

I have ammended the code to exclude the month keyword but it still doesn't
seem to be working. Any further ides.
I was wonderring if it could be something to do with the date format but
both dates are worked out in the same way.

I have copied the new code below.

Any further advice would be greatly appreciated.

Thanks

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',theMonth," &
[CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;

Month is a reserved word, and shouldn't be used as a field name, and you
can't refer to computed fields like that in queries: you need to repeat
the calculation.

For a good discussion on names to avoid for fields and variables, see
what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can anyone see the problem with my Running Total Query below?

(I have used the same formula for Month on the query qryStatsTotalClosed
as CurrentMonth on this one)

Thanks in advance,

John


SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',Month," &
[CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;

 

[John Ortt]

Sorry Doug,

I did spot your comment but I still couldn't get it to work. I pasted the
wrong version of the query.

The one below is the one where I have removed the reference to the
calculated field.

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',theMonth," &
DateAdd('d',1-Day([Date Raised]),[Date Raised]) & ")>0") AS RunTot
FROM tblQueryInfo;

As to why I need it I have a database which logs faults along with when they
were raised and when they were fixed.

I have created two queries for the number of faults raised and the number of
faults closed (By month).

I would like to do a running sum on these values to give the number of
faults outstanding each month.

I want to chart this on a six month rolling basis.

As to the Datediff('d',theMonth," & [CurrentMonth] & ")>0")? I am simply
trying to find all the date values which are less than or equal to the month
on the table (see above).

Another problem I am encountering is the absence of certain months causing
the chart to miss a month.

I have read that this can be overcome by using a lookup table or by
generating one with code.
I am keen that the database is self sufficient so I think code is the best
way to do this.

Thanks for bearing with me on this Doug, it is much appreciated.

All the best,

John

What's the purpose of Datediff('d',theMonth," & [CurrentMonth] & ")>0")?
I suspect there's an easier way of arriving at the same thing.

I note that you missed my comment that you can't refer to computed fields
by name like that: you have to repeat the calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,

I have ammended the code to exclude the month keyword but it still
doesn't seem to be working. Any further ides.
I was wonderring if it could be something to do with the date format but
both dates are worked out in the same way.

I have copied the new code below.

Any further advice would be greatly appreciated.

Thanks

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',theMonth," &
[CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;

Month is a reserved word, and shouldn't be used as a field name, and you
can't refer to computed fields like that in queries: you need to repeat
the calculation.

For a good discussion on names to avoid for fields and variables, see
what Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Can anyone see the problem with my Running Total Query below?

(I have used the same formula for Month on the query
qryStatsTotalClosed as CurrentMonth on this one)

Thanks in advance,

John


SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',Month," &
[CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;

 

[Douglas J. Steele]

You should be able to use

DSum("[Queries Closed]","qryStatsTotalClosed","Month([theMonth]) <= "
Month([Date Raised])) AS RunTot

or

DSum("[Queries Closed]","qryStatsTotalClosed","[theMonth] <= "
DateSerial(Year([Date Raised]), Month([DateRaised], 1)) AS RunTot


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry Doug,

I did spot your comment but I still couldn't get it to work. I pasted the
wrong version of the query.

The one below is the one where I have removed the reference to the
calculated field.

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',theMonth," &
DateAdd('d',1-Day([Date Raised]),[Date Raised]) & ")>0") AS RunTot
FROM tblQueryInfo;

As to why I need it I have a database which logs faults along with when
they were raised and when they were fixed.

I have created two queries for the number of faults raised and the number
of faults closed (By month).

I would like to do a running sum on these values to give the number of
faults outstanding each month.

I want to chart this on a six month rolling basis.

As to the Datediff('d',theMonth," & [CurrentMonth] & ")>0")? I am simply
trying to find all the date values which are less than or equal to the
month on the table (see above).

Another problem I am encountering is the absence of certain months causing
the chart to miss a month.

I have read that this can be overcome by using a lookup table or by
generating one with code.
I am keen that the database is self sufficient so I think code is the best
way to do this.

Thanks for bearing with me on this Doug, it is much appreciated.

All the best,

John

What's the purpose of Datediff('d',theMonth," & [CurrentMonth] & ")>0")?
I suspect there's an easier way of arriving at the same thing.

I note that you missed my comment that you can't refer to computed fields
by name like that: you have to repeat the calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug,

I have ammended the code to exclude the month keyword but it still
doesn't seem to be working. Any further ides.
I was wonderring if it could be something to do with the date format but
both dates are worked out in the same way.

I have copied the new code below.

Any further advice would be greatly appreciated.

Thanks

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',theMonth," &
[CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;



Month is a reserved word, and shouldn't be used as a field name, and
you can't refer to computed fields like that in queries: you need to
repeat the calculation.

For a good discussion on names to avoid for fields and variables, see
what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Can anyone see the problem with my Running Total Query below?

(I have used the same formula for Month on the query
qryStatsTotalClosed as CurrentMonth on this one)

Thanks in advance,

John


SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS
CurrentMonth, DSum("[Queries
Closed]","qryStatsTotalClosed","Datediff('d',Month," & [CurrentMonth]
& ")>0") AS RunTot
FROM tblQueryInfo;

 

[John Ortt]

Still No luck Doug,

I have pasted my resulting code below:

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Month([theMonth]) <= " &
Month([Date Raised])) AS RunTot
FROM tblQueryInfo;

I have tried running the Month([Date Raised]) and Month([theMonth]) in
separate queries so it isn't an (obvious) problem with the date formats.

Any other ideas?

You should be able to use

DSum("[Queries Closed]","qryStatsTotalClosed","Month([theMonth]) <= "
Month([Date Raised])) AS RunTot

or

DSum("[Queries Closed]","qryStatsTotalClosed","[theMonth] <= "
DateSerial(Year([Date Raised]), Month([DateRaised], 1)) AS RunTot


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry Doug,

I did spot your comment but I still couldn't get it to work. I pasted
the wrong version of the query.

The one below is the one where I have removed the reference to the
calculated field.

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',theMonth," &
DateAdd('d',1-Day([Date Raised]),[Date Raised]) & ")>0") AS RunTot
FROM tblQueryInfo;

As to why I need it I have a database which logs faults along with when
they were raised and when they were fixed.

I have created two queries for the number of faults raised and the number
of faults closed (By month).

I would like to do a running sum on these values to give the number of
faults outstanding each month.

I want to chart this on a six month rolling basis.

As to the Datediff('d',theMonth," & [CurrentMonth] & ")>0")? I am simply
trying to find all the date values which are less than or equal to the
month on the table (see above).

Another problem I am encountering is the absence of certain months
causing the chart to miss a month.

I have read that this can be overcome by using a lookup table or by
generating one with code.
I am keen that the database is self sufficient so I think code is the
best way to do this.

Thanks for bearing with me on this Doug, it is much appreciated.

All the best,

John

What's the purpose of Datediff('d',theMonth," & [CurrentMonth] & ")>0")?
I suspect there's an easier way of arriving at the same thing.

I note that you missed my comment that you can't refer to computed
fields by name like that: you have to repeat the calculation.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Thanks Doug,

I have ammended the code to exclude the month keyword but it still
doesn't seem to be working. Any further ides.
I was wonderring if it could be something to do with the date format
but both dates are worked out in the same way.

I have copied the new code below.

Any further advice would be greatly appreciated.

Thanks

SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS CurrentMonth,
DSum("[Queries Closed]","qryStatsTotalClosed","Datediff('d',theMonth,"
& [CurrentMonth] & ")>0") AS RunTot
FROM tblQueryInfo;



message Month is a reserved word, and shouldn't be used as a field name, and
you can't refer to computed fields like that in queries: you need to
repeat the calculation.

For a good discussion on names to avoid for fields and variables, see
what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Can anyone see the problem with my Running Total Query below?

(I have used the same formula for Month on the query
qryStatsTotalClosed as CurrentMonth on this one)

Thanks in advance,

John


SELECT DateAdd("d",1-Day([Date Raised]),[Date Raised]) AS
CurrentMonth, DSum("[Queries
Closed]","qryStatsTotalClosed","Datediff('d',Month," & [CurrentMonth]
& ")>0") AS RunTot
FROM tblQueryInfo;