Calculate the difference between two rows in a query

[PeterS]

Hello
Please help urgently. I am new to Access and have no idea about Sql codes.

I have an access query called [Excessive Hour Customer Qry] with the following fields:
1. ForkliftID-Fk from table Excessive Hour Forklift Tbl
2. CustomerName from Customer Tbl
3. Service Date from Forklift Service Tbl
4. Hour from Forklift Service Tbl

There are paramaters on the CustomerName and Service Date fields.

What I need to do is calculate the difference in hours. eg

ForkliftID-Fk Customer Name Service Date Hours
15 Barnlab 15/10/2018 1050
15 Barnlab 15/11/2018 1100

I need a new field that tells me that the difference betwwn the two fows is 50

This is my SQL statement



SELECT [Excessive Hour Forklift Tbl].[ForkliftID-Fk], [Customer Tbl].CustomerName, [Forklift Service Tbl].ServiceDate, [Forklift Service Tbl].Hours
FROM ([Customer Tbl] INNER JOIN (([Forklift Tbl] INNER JOIN [Excessive Hour Forklift Tbl] ON [Forklift Tbl].ForkliftID_Pk = [Excessive Hour Forklift Tbl].[ForkliftID-Fk]) INNER JOIN [Excessive Hour Customer Tbl] ON [Excessive Hour Forklift Tbl].[ExcessiveHourForkliftID-Pk] = [Excessive Hour Customer Tbl].[ExcessHoursCustomerID-Pk]) ON [Customer Tbl].[CustomerID-PK] = [Forklift Tbl].[CustomerID-Fk]) INNER JOIN [Forklift Service Tbl] ON [Forklift Tbl].ForkliftID_Pk = [Forklift Service Tbl].ForkliftID_Fk
WHERE ((([Customer Tbl].CustomerName) Like "*" & [Customer Name] & "*") AND (([Forklift Service Tbl].ServiceDate) Between [Start Date] And [End Date]));


Please can you help me

[ranman256]

rows cannot see each other, so... either:
make 2 queries that each pull 1 row, and a 3rd query to calc the difference
or
pull both records in 1 query and use VB to calc the difference.

[isladogs]

Or use a subquery.
Allen Browne has an example explaining how to do this. See 'get the value in another record' at http://allenbrowne.com/subquery-01.html

[rpeare]

PeterS.zip

Here is a way to do it all in one query, however, it uses domain functions which are memory hogs so you would best be served by limiting your data as I've done in this example in a prequery.

it is still not super efficient but without a better idea of your data structure it should work.

[PeterS]

Hi rpearse
Thank you for your example. I have tried to emmulate what you did but I am obviously doing something wrong. I have attached my database for you to see. Please can you assist me? Sorry for the inconvenience but I am a total novice.

[isladogs]

Here's a different approach using one query and no subqueries or domain functions.
It should be much faster

The idea is to use 2 copies of the same table linking current and previous values

SELECT Table2.id, Table2.NField AS New, Table2_1.NField AS Prev, [table2_1].[NField]-[Table2].[NField] AS Diff
FROM Table2, Table2 AS Table2_1
WHERE (((Table2_1.id)=[Table2].[id]-1));

[PeterS]

Hi ridders52
I like what you sent but I am ashamed to say that I could not get it to work. My basic understanding od SQL if still infantile. Would you be able to look at my database , attached, and point me in the right direction? When I tried to duplicate what you did, I fell very short.

[isladogs]

You can't easily use that approach with your setup nor would the method suggested bt rpeare be a good idea IMO.
I could probably get it to work but it would take longer than I can spare today.
Suggest you try the subquery approach based on the example at Allen Browne's website - see previous link

BTW there are many issues with your current setup including the use of spaces in object names.
Using mutliple parameters in queries isn't user friendly - 3 parameters for that query alone.
Instead, use combos or listboxes on forms to select the required items - customer name, start & end dates

[ssanfu]

Also note that "HOUR" is a reserved word (and a built in function) in Access and shouldn't be used as object names.

[rpeare]

Your query is messed up because you have no way to reliably sort them, the inference from your original post was that you had a table with multiple forklift service records, which I assume was a number of hours of operation. To go with that you would assume the hours would go up and never go down. There's no way, in your example database, to do that other than sorting on the nfield and then doing your domain function based on the nfield rather than the primary key (and sorting it by date as I did in my original example)

EDIT: it also looks like your query is calculating correctly, you've just got your operation muddled, you just have to change your operation from x - y to y - x

[PeterS]

Hi

I have constructed the query (Query1)as suggested and at least I am getting some kind of a result.

The result that I am looking for is:

When I open the query, I want to select a ForkliftID...lets say number 15....
When the query opens I would like to see a list of all the service records for forklift number 15 with a calculation field that shows me the difference between the operating hours of each record. ....What i mean by that is the following:

Forklift 15.....service date 01/08/2018......operating hours 1000.....hours used....0
Forklift 15.....

service date 04/09/2018......operating hours 1250.....hours used....250

Forklift 15.....service date 30/09/2018......operating hours 1300.....hours used....50
Forklift 15.....service date 01/11/2018......operating hours 1400.....hours used....100

Please can you help me figure this out. I am a total novice and struggle with coding. I have attached my database so you can see exaclty what I have done. Please can you look at query1

Thanks in advance for all the help

[ssanfu]

There are a couple of things I would correct: "DATE" is a reserved word (and a built in function) in Access and shouldn't be used as object names.

You have a dash in some field names - these should be changed to the underscore.
There are slashes in some field names - delete the slash or change to the underscore
Naming rules:
-------------
Object names should be letters and numbers.
Do not begin an object name with a number.
NO spaces, punctuation or special characters (exception is the underscore) in object names



You are displaying Autonumber fields on the forms.
About AutoNumber fields:
------------------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

Microsoft Access Tables: Primary Key Tips and Techniques

Autonumbers--What they are NOT and What They Are



In the table "ForkliftTbl", here are "CustomerID-Fk" numbers of 0 (zero). Because "CustomerTbl.CustomerID-Pk" is an autonumber type field, there cannot be a "CustomerID-Pk" of zero. Therefore RI cannot be set between the tables of "CustomerTbl" and "ForkliftTbl".


Here is how I would possible design your dB:


I added a field for the forklift ID number. In the forklift table, there are 8 RHINO-HELI/ CPCD25 forklifts.
How do you tell which forklift is contracted to which customer?



About your question. Colin (ridders52) provided a link to get the previous row.
Here it is again http://www.allenbrowne.com/subquery-...#AnotherRecord




And just for good measure, here is a dB I wrote a long time ago to calculate miles per gallon. (don't laugh - it was a log time ago, before I much experience).
I wrote a UDF to get the previous reading (GetPreviousOdometer - in Module1)



After the dB opens, click the button "List Fill ups". Then click on "Show Vehicle records". BTW, the data is sorted descending by fill up date.

[PeterS]

Thank you for the relationships and table adjustments. I have started again using your guidance and it seems to be working much better. Please have a look and see what you think.

Basically what I now need to do is get an overhours reading on the forklifts. In short I have the following tables:

1. ForkliftServiceTbl

In this table we capture the hours of each forklift, so there are different dates, forklift numbers and customers.
eg 01/06/2018 Forklift15 Barnlab 1000
02/06/2018 Forklift16 VCJ 1500
02/06/2018 Forklift17 Reef 1300
30/06/2018 Forklift34 Barnlab 500
05/07/2018 Forklift15 Barnlab 1300

In this example you can see that Forklift15 for customer Barnlab is listed twice but ate different dates. You can also see that the customer Barnlab has two forklifts, Forklift15 and Forklift34.

Bearing in mind that I have no clue what I am doing, how would I calculate the hours?
eg Forklift15

the maths is easy (

05/07/2018 Forklift15 Barnlab 1300) - (

01/06/2018 Forklift15 Barnlab 1000) = 300

[ssanfu]

First, you should fix a few things.

What is table "HighUsageTbl" for?? The design doesn't make any sense.

There are problems with the queries "HighUsageQry" & "NextServiceQry".
The design for "HighUsageQry"



the design for "NextServiceQry"




I deleted all of the records in table "ForkliftServiceTbl" because the field "ForkliftServiceTbl.CustForkliftID_Fk" had values that did not match values in "jnctCustForkliftTbl.CustForkliftID_PK". Now RI is set.

Bearing in mind that I have no clue what I am doing, how would I calculate the hours?

Did you look at the link to Allen Browne's site (Post #12)? You could use the sub query route, but I wonder how many times a forklift will be serviced in a time period (don't know the time period(s). Do you do monthly reports? Quarterly reports? Yearly?
I would probably go the UDF route.

[PeterS]

Hi ssanfu
After I thought I was making progress

To answer your question regarding what is the HighUsageTbl for?.......I thought that it would be appropriate to create a table for customers who run up high operating hours on the forklifts. In essence it is just a table that contains a list of customers who's hours need to be watched and managed.

Regarding your question about how many times a forklift will be serviced.....The principal is that a forklift should be serviced after every 250 hours of operation. With that in mind the frequency of services is relyant on hours operated and not a time period. For example....Barnlab may have forklift 15 serviced on 01/09/2018 on 1000 hours and then again on the 25/09/2018 at 1250 hours. Then they may have a slow month with low operating hours and only need to have it serviced again on 03/11/2018 at 1500 hours.

With those questions answered, does what I am trying to do make make more sense?

I appologies for my incometance and I am very grateful for your time and help.