Reading the last value of a field

[rubenh]

Dear All,

I am doing a database on inputting readings (number of pages printed) of photocopiers we have and then I subtract the last value from the previous value. I want to store the result in another field within the table. Can someone help me with this please.

Thanks

 

[CJ_London]

you do these calculations in a query, you don't store them.

From your description it sounds like you have got an 'excel hat' on. In databases there is no such thing as last or first, previous or next unless you can define an order.

to do what you want, you will need to use a subquery to get the 'last' value. However insufficient information provided to provide an example of what this might look like.

Provide some example data including field and table names and the outcome you require from that example data

 

[rubenh]

Infact currently I am working with an excel speadsheet but would love to do it in a database since the number of photocopiers are constantly increasing.

Ok let me explain.

We have machines (photocopiers) on a pay per click contract. example 0.1c per black page printed and 0.5c per colour page printed. There are various reading as below

A4 black = 1 page
A3 black = 2 pages (2 x A4)
A4 black duplex = 2 pages (2 x A4)
A3 black duplex = 4 pages (4 x A4)

same for the colour printing.

As you know you can issue a status report of the machine to give you these numbers of pages.

Since we request the readings of each machine once a month then i will need to subtract from the previous readings to calculate the number of pages printed during that month and then we issue an invoice as per rate eg. (0.1c for black and 0.5c for colour). Rates vary from one customer to another.


Some data examples
Black A4
Counter - 456 Prev. reading - 355 Tot. No. Of Pgs this month - 101

Black A3
Counter - 45 Prev. Reading - 22 Tot. No. of Pgs this month - 23
Here I need to multiply by 2 the tot. no of pages this month since A3 = 2 x A4

Black A4 Duplex
Counter - 123 Prev. Reading - 95 Tot. No. of Pgs this month - 28
Here I need to multiply by 2 the tot. no of pages this month since A4 = 2 x A4 (Front and Back)

Black A3 Duplex
Counter - 60 Prev. Reading - 45 Tot. No. of Pgs this month - 15
Here I need to multiply by 2 the tot. no of pages this month since A4 = 4 x A4 (Front and Back (2*A4)

Do you think you have a better picture of what i want?

 

[CJ_London]

so each machine has 4 readings - all charged at the same price or different prices? - and are those prices specific to a machine model/customer/something else?


in a database I would expect you to have as a minimum the following tables - possibly more if I had a fuller understanding of the pricing structure

tblCustomers
CustomerPK
CustomerName


tblMachines
MachinePK
MachineName
MachineSerialNo
CustomerFK - link to PK in table customers

tblPrices edit:this assumes one price for all machines - add a MachineFK field to extend to a specific machine/customer
PricePK
PriceUnitDescription
Price

tblReadings
ReadingPK
MachineFK - link to PK in tblMachines
PriceFK - link to PK in tblPrices
ReadingDate
Reading

The basic query you will need based on the above is as follows - note the use of aliases (C & P)

SELECT C.*, P.ReadingDate, P.Reading, nz(P.reading,0)-C.reading as Usage
FROM tblReadings C INNER JOIN tblReading P ON C.MachineFK=P.MachineFK AND C.PriceFK=P.PriceFK
WHERE C.ReadingDate=(SELECT TOP 1 ReadingDate FROM tblReadings WHERE MachineFK=C.MachineFK AND PriceFK=C.PriceFK ORDER BY ReadingDate Desc) 
            AND P.ReadindDate=(SELECT TOP 1 ReadingDate FROM tblReadings WHERE MachineFK=C.MachineFK AND PriceFK=C.PriceFK AND ReadingDate<C.ReadingDate ORDER BY ReadingDate Desc)

you can make this query faster/more efficient by being able to define the previous record more closely - for example if billing is every month, never late or early etc then you can do away with the subqueries and in your where clause you would just have

WHERE P.month=C.month-1 OR (P.month=12 and C.month=1)

edit: get this basic query going first, once done you can link back to the other tables from the C table to get other info such as machine/price description etc

 

[rubenh]

As already stated... the price may vary. I can have 10 of the same machines all with different pricing.

 

[CJ_London]

per my edit - add a machineFK field to tblPrices

there are lots of other things to consider as well, so my suggestion is very basic

what if you have a price increase?
what if a contract comes to an end?
what if you have to adjust the count for some reason perhaps due to mechanical failure?
I've not included any detail about the customer such as invoice address, contact details

etc.

I asked you to provide some example data including field and table names and the outcome you require from that example data

you provided some, but not all, so I had to make the rest up.

Either way your question was about reading the last value, not about pricing

 

[rubenh]

Forget the pricing since once I have the subtraction in place I can work out that. All I need is how to read the value of the previous record.

 

[CJ_London]

All I need is how to read the value of the previous record.

already provided in my second post

good luck with your project