Good day,
I have a bit of a problem trying to create a running total in my report, I get so far and then when I view my report I noticed that some records were not actually producing a running total.
I have two tables one that stores the history of text files that exist and the fields are as follow:
tblFileExistHistory
Fields
strFileExists [Data Type – Text]
lngRecordCount [Data Type – Number]
dtmDateStamp [Data Type – Date/Time]
dtmTimeStamp [Data Type – Date/Time]
tblManufacturerTextFilesListings
Fields
lngManuID [DataType – AutoNumber]
strManufacturer [Data Type – Text]
strAssocTextFiles [Data Type – Text]
strScheme [Data Type – Text]
I have two Queries as follows:
qryRegFileExistsHisCurYear – This query pulls together all the data for the current year
qryRegVolsByMonth – This query uses the qryRegFileExistsHisCurYear query and the tblManufacturerTextFilesListings table to produce a crosstab query displaying the records as shown below:
Image attached
I have a report which is based on the above query which is where I am having the problem displaying my running total.
rptRegsVolsByMonth
within the report I have added in an extra row of fields to create a running total from left to right [Jan through to Dec] and I have named them as follows:
txtRunCountJanFeb
txtRunCountFebMar
txtRunCountFebMar
txtRunCountFebMar
txtRunCountMayJun
txtRunCountMayJun
txtRunCountJulAug
txtRunCountAugSep
txtRunCountSepOct
txtRunCountOctNov
txtRunCountNovDec
So txtRunCountJanFeb has this expression in the control source “=[Sum Of Jan]+[Sum Of Feb]” This takes the value from the Sum Of Jan field and adds the value of Sum Of Feb field to produced the running total from Jan to Feb.
The next field txtRunCountFebMar has this expression in the control source “=[txtRunCountJanFeb]+[Sum Of Mar]” this takes the value of txtRunCountJanFeb and adds it to the value of Sum Of Mar to produce a running total from Jan through to Mar. I use this method for the remainder of the fields, however when I preview my report this works on the whole except when you view page 8, you’ll notice that for the Manufacturer Electrolux [09410] that the running total isn’t working, because there are not value in Jan and Feb for this manufacturer, but there is a value in Mar, so what I want is for the Running Total field in Feb to do is display “0” [zero] and not be blank and in the running total field under Mar to display the number “2” and not be blank.
If you go to page 9 you’ll see under the Galaxy Manufacturer a similar problem only this time there is no value in Feb and Mar, but in the Running Total field under Feb and Mar there shouldbe the value of 1, which clearly isn’t happening.
This is the problem I’m having, if anyone can shed some light on how I might resolve this I would appreciate it very much.
I have also posted an example database with the above tables, queries and report in.
Regards
John
I have a bit of a problem trying to create a running total in my report, I get so far and then when I view my report I noticed that some records were not actually producing a running total.
I have two tables one that stores the history of text files that exist and the fields are as follow:
tblFileExistHistory
Fields
strFileExists [Data Type – Text]
lngRecordCount [Data Type – Number]
dtmDateStamp [Data Type – Date/Time]
dtmTimeStamp [Data Type – Date/Time]
tblManufacturerTextFilesListings
Fields
lngManuID [DataType – AutoNumber]
strManufacturer [Data Type – Text]
strAssocTextFiles [Data Type – Text]
strScheme [Data Type – Text]
I have two Queries as follows:
qryRegFileExistsHisCurYear – This query pulls together all the data for the current year
qryRegVolsByMonth – This query uses the qryRegFileExistsHisCurYear query and the tblManufacturerTextFilesListings table to produce a crosstab query displaying the records as shown below:
Image attached
I have a report which is based on the above query which is where I am having the problem displaying my running total.
rptRegsVolsByMonth
within the report I have added in an extra row of fields to create a running total from left to right [Jan through to Dec] and I have named them as follows:
txtRunCountJanFeb
txtRunCountFebMar
txtRunCountFebMar
txtRunCountFebMar
txtRunCountMayJun
txtRunCountMayJun
txtRunCountJulAug
txtRunCountAugSep
txtRunCountSepOct
txtRunCountOctNov
txtRunCountNovDec
So txtRunCountJanFeb has this expression in the control source “=[Sum Of Jan]+[Sum Of Feb]” This takes the value from the Sum Of Jan field and adds the value of Sum Of Feb field to produced the running total from Jan to Feb.
The next field txtRunCountFebMar has this expression in the control source “=[txtRunCountJanFeb]+[Sum Of Mar]” this takes the value of txtRunCountJanFeb and adds it to the value of Sum Of Mar to produce a running total from Jan through to Mar. I use this method for the remainder of the fields, however when I preview my report this works on the whole except when you view page 8, you’ll notice that for the Manufacturer Electrolux [09410] that the running total isn’t working, because there are not value in Jan and Feb for this manufacturer, but there is a value in Mar, so what I want is for the Running Total field in Feb to do is display “0” [zero] and not be blank and in the running total field under Mar to display the number “2” and not be blank.
If you go to page 9 you’ll see under the Galaxy Manufacturer a similar problem only this time there is no value in Feb and Mar, but in the Running Total field under Feb and Mar there shouldbe the value of 1, which clearly isn’t happening.
This is the problem I’m having, if anyone can shed some light on how I might resolve this I would appreciate it very much.
I have also posted an example database with the above tables, queries and report in.
Regards
John
