Running totals in a form

[mcnaugg]

I want to design a small DB to record the monthly municipal costs. I would
like to enter the current monthly water and electric meter readings and have
the monthly useage calculated and idealy presented in the Form.
The more I think about this, the more I think it's a job for Excel.

Regards

Gareth

 

[Wayne-I-M]

You can display running totals on a continous form but what you discribe is
just what excel is for.
Access is a relational database - so if you have a number of properties and
in each property you have a number of meters that may change supplier and the
owner of the property may move between propertues and the meter suppliers may
or may not move with the property owner then you need access (as all the
"items" can be related) but if its just a speadsheet showing calculated
results then you need excel

 

[mcnaugg]

Hi Wayne

Thanks. It's as I suspected. I have a spreadsheet for this excercise but
wondered if I could "clean up" the process of entering the monthly info by
using a Access DB.

Regards

Gareth

 

[Wayne-I-M]

It all depends on what you are doing and why you are doing it

Excel is (I know it does more) bascally a number cruncher
Access is (I know it does more) basically relational data store

Like I said - what and why - the why being the more important. Access is
really good a making it simple to view complex inter-related data (in your
case the meters and the properties). Excel would be best if you don't need
to "vary" the relationship beween propertys and meters.

If you have a specific question about access then post it here and somoene
may answer

 

[Ken Sheridan]

Gareth:

A similar question cropped up some years ago in a magazine column to which I
occasionally contribute. I found I still have the query I came up with at
the time. Its based on a table Readings with columns ReadingDate , Reading
and ReadingType (e.g. Gas, Electricity etc):

SELECT R1.ReadingType, R1.ReadingDate, R1.ReadingDate -
(SELECT MAX(R2.ReadingDate)
FROM Readings As R2
WHERE R2.ReadingDate < R1.ReadingDate
AND ReadingType = R1.ReadingType)
AS DaysSinceLastReading, R1.Reading -
(SELECT MAX(R3.Reading)
FROM Readings As R3
WHERE R3.ReadingDate < R1.ReadingDate
AND ReadingType = R1.ReadingType)
AS Usage
FROM Readings AS R1
WHERE R1.ReadingDate >
(SELECT MIN(R4.ReadingDate)
FROM Readings As R4
WHERE ReadingType = R1.ReadingType)
ORDER BY R1.ReadingType, R1.ReadingDate;

The data can be entered in a form based on the Readings table and the usage
shown in a form, in continuous form or datasheet view, based on the above
query. If you have readings from more than one meter then you'd simply need
to add a Meter column to the table and correlate each of the subqueries on
the meter column as well as the ReadingType column.

Ken Sheridan
Stafford, England

 

[mcnaugg]

Hi Ken

Thanks for the reply. I think I'm trying to run before I can walk with this
question of mine. I think that I should stick with Excel for the time being.
I have just found the Data/Form in Excel which would seem to answer my
question.

Kind regards

Gareth

 

[mcnaugg]

Hi Wayne

Thanks for the reply. I think I will stay with Excel for the time being as
it seems the better solution to my question.

Kind regards

Gareth