Subtracting off the same field (1 Viewer)

[Tkandy]

Help Me please

I have designed a database which has a current date 1 field and a well reading (Readings) 9 fields which goes something like this [WellsDate] [MainMeter] [GasMeter] [Well 4] [Well 5] [Well 9][Well 10][Well 12] [Well 14][Rendering Heat]

I am trying to subtract the readings from the previous day for each meter

EG current date [Mainmeter] - previous date [Mainmeter] and so on

Excel is C3-c2 and you get a daily figure but i cannot get this from access

I have been trying this one for ages so if anyone could help this would be much appreciated

 

[jzwp22]

Welcome to AWF!


Before we worry about the calculation you want to do, your table structure needs some work. You are trying to design a relational database as if it were a spreadsheet. The logic behind a relational database is totally different. You might want to take a look at this site that explains the concept of normalization as it relates to relational databases.

If the 9 fields are all holding readings (like data) then they should be records in a table not fields. Are the wells/meters associated with a site and can each site have multiple wells/meters? Perhaps you can explain your application, so that we may be better able to help you.


Also, it is not a good idea to have spaces or special characters in your table and field names. It will make more work for you later on since you will have to enclose all table/field names in square brackets.

 

[Tkandy]

Thank you jzwp22 for replying i hope i explain this a bit better

I have built this on what our company uses and we are trying to track the readings of daily usage and weekly usage. I have set the tables up as Electricity readings, well readings, boiler room readings, water consumption.
with the approiate fields eg All the wells are in the Wells table with daily readings (is there are better way at setting this up?) Electricity readings for each department is in Electricty readings with there daily readings, they are all read on a daily basis.
there is only one site and we have multiple wells/meters same with electricity and so on that is why i have put all data relating to electricity in the same table,

Thank you for the suggesstion about gaps i will remove them

 

[CameronM]

As jzwp22 suggests, you may want to look at a small redesign of your tables, to really take advantage of an access database (which is quite different to a spreadsheet).

My first suggestion would be to remove the individual Wells from your table and set up a couple of related tables - one to store the names and ID's of the wells and another to record the readings. This way when you add a new well, you con't have to modify one big table, but rather just add it to the 'Wells' table.

A rough idea of the two tables would be:

tblWells
WellID (AutoIncrement Integer)
Name (Text)

tblWellReadings
ReadingDate (Date/Time)
WellID (reference to tblWells
ReadingValue (integer/decimal or whatever you read from the well)

It's a fairly task then to calculate the changes in reading from one reading to the next using a query in a report or a form.

 

[boblarson]

I think some reading is in order:

Database design & normalization principles

Database Normalization

 

[jzwp22]

You might be able to take Cameron's suggestions further. First a table to hold all items (you might have a better name) for which you are recording readings

tblItems
-pkItemID primary key, autonumber
-txtItemName (your name for each item)
-fkItemType

tblItemTypes (a record for each type of unit i.e. well, gas meter, electric meter)
-pkItemTypeID primary key, autonumber
-txtItemTypeDescription

tblReadings
-pkReadingID primary key, autonumber
-fkItemID foreign key to tblItems
-yourreading
-fkUnitsOfMeasureID foreign key to tblUnitsOfMeasure (if your readings have different units of measure)

tblUnitsOfMeasure ( a record for each different unit, kWh, liters etc.)
-pkUnitsOfMeasureID primary key, autonumber
-txtUnitsOfMeasure

I concur with Bob's suggestions of further reading since setting up the table structure correctly is the most important part of creating a successful database application.

 

[CameronM]

Here is a very rough database that illustrates the points being made, which may help.

It includes a couple of queries, one showing how you can return the value of a particular reading, the previous reading and the difference between the two.

This is a very rough hack and you should definately do some reading and sketch out your database design before jumping in boots and all.

 

[Tkandy]

Thank you so much for your guys help it is really much appreciated as i have been chucked in the deep end to do this i am going to have a read up and change some of my tables and i will get back to you on how i am doing.

Once again thank you for taking the time to help me on this massive project

 

[jzwp22]

You're welcome. Please post back if you have questions.