Access How do I write a Formula to suntract in a query or report? 1

[rickes]

I am lost. I have an access database that I am keeping track of belt changes. I want it to automatically give me the total hours on a belt from the time it was put on to the time it was changed. I would like to do this in a report or in a query. It looks something like this (top row is column header):

EQUIPMENT BELT DATE HOURMETER REASON TOT.HOURS ON BELT
press 1 upper 12/01/04 100 torn
press 1 upper 1/30/05 150 torn
press 1 upper 3/15/05 250 plugged

What formula do i need to write in column(TOT.HOURS ON BELT)in a query or in a report to subract record 1 (HOURMETER) from record 2 (HOURMETER) and so on down the column everytime i add a new record to show me the TOT. HOURS ON BELT for each belt changed? For Instance, Record 1 should read 50 Hrs in TOT. HOURS ON BELT(150-100), Record 2, 100 Hrs, Etc.
I need all the help I can get.
Thanks so much..
RICKES

 

[THWatson]

Rickes
I don't know the name of your table, so I made up a small table called Table7, with 3 columns. The columns were RecordID, HourMeter and ChangeDate.

Then I made a query. The SQL for the query is below.

SELECT A.RecordID, A.HourMeter, A.ChangeDate, [A].[HourMeter]-(SELECT B.HourMeter FROM Table7 B WHERE B.ChangeDate = (SELECT Max(C.ChangeDate) FROM Table7 C WHERE C.ChangeDate < A.ChangeDate)) AS [Total Hours On Belt]
FROM Table7 AS A;

Plug the above into a new query, then change the table name and fields to match yours. Then you can switch to Design view and add any other columns you wish.

You will notice the sqare brackets around "Total Hours On Belt". That's because Hours is a reserved word in Access. But the brackets go away when the column shows when you run the query.

This should at least give you a start.

Tom

 

[rickes]

I dont understand what the A B and C infront of my field names are for? I get errors when i just take your query and change the field and table names to what mine are. a window will pop up saying that it will only display one result and when i click ok it displays #NAMES? in all the feilds?
my dates field is not my primary key, some dates are the same to distiguish between different types of equipment of when their belts had been changed. Such as.. Belt Press 1 Upper Belt 10/22/04 and Belt Press 1 Lower Belt 10/22/04. Notice the dates are the same but the equipment is different. Does this cause a problem trying to find the MAX value of the Date when there are duplicates?

 

[THWatson]

rickes
The A, B and C are alias table names. I think I misread the fields in your table, and didn't know there was more than one belt, and thought that what you meant by Record referred to subsequent readings for the belt.

From your posting, maybe I'm not understanding the HourMeter readings. If an upper belt had 100 hours on it and then was changed, and the next upper belt had 150 hours on it and then was changed, and the next belt had 250 hours on and then was changed...what does the difference in values represent?

How many belts are there? Two - Upper and Lower?

Can you post the exact table structure (fields and data type)?

Also, what exactly do you want to achieve what you want to achieve in the way of a report?

Tom

 

[rickes]

RecordID Equipment Belt ChangeDate HourMeter Hours on Belt Reason Reason for Other
6 Belt Press 1 Upper Belt 11/12/2003 20765 Torn
7 Belt Press 1 Upper Belt 6/14/2004 24803 Torn
8 Belt Press 1 Upper Belt 3/28/2005 29816 Torn
9 Belt Press 1 Lower Belt 10/11/2004 26758 Torn
10 Belt Press 2 Upper Belt 2/7/2004 23012 Torn
11 Belt Press 2 Upper Belt 2/22/2005 28974 Torn
12 Belt Press 2 Lower Belt 12/1/2003 21669 Torn

 

[rickes]

Sorry I did not explain last post. That is from a table called BeltReplacement. the bold row is the field names. There are 7 records.. If you notice, there are 2 different pieces of equipment, each with an upper belt and lower belt. The next field(ChangeDate), is the field when that belt was changed. The (HourMeter) field is the field where I put the hour meter reading from the press on the day the belt was changed. the (hours on Belt) is where I want to be able to put a formula in that subtracts Record 6 from record 7 ( since both are from Belt Press 1 and upper belts and the following date when it was changed again) This will give me the total hours that the belt lasted on record 6 before I had to do another belt change. Then it would have to subtract record 7 from 8. Paying special attention to the different Belt Presses and whether it is an upper belt or lower belt. Should I take this field out of this table and make it in a query? and if so, how do I write it? I hope this helps you more. I could always email you the file so you could take a look at it and we could be in contact through email if you like.
Thanks so very much for your help,
Rickes

 

[THWatson]

Rickes
Just picked this up late tonight. Now I understand.

This is a bit tricky...and complicated. But I'm close.

I'll work on it again in the morning when I'm fresh.

Tom

 

[THWatson]

Rickes
Got it!

Here's the SQL. Providing I have the field names from the table correctly, you should just be able to paste the SQL into a query (in SQL view) and run it.

SELECT A.Equipment, A.Belt, A.ChangeDate, A.HourMeter, A.Reason, [A].[HourMeter]-(Select B.HourMeter FROM BeltReplacement B WHERE B.ChangeDate = (Select Max(C.ChangeDate) FROM BeltReplacement C WHERE C.ChangeDate < A.ChangeDate AND A.Equipment = C.Equipment AND A.Belt = C.Belt)) AS [Total Hours on Belt]
FROM BeltReplacement AS A
ORDER BY A.Equipment, A.Belt DESC , A.ChangeDate;

I tested further by adding more fields to the table for each of the Presses and Belts, and everything seemed to work okay.

Let me know how it looks to you.

By the way, I wouldn't store the "Hours On Belt" value in the table. It's a calculated value, so you don't need to store it. And you can base any report you want on the query.

There may be other ways to do this. I tried several other ways, but couldn't find one. The tricky thing is grouping both the Presses and their respective Belts, and only subtracting the values when they are the same Press and Belt.

Good luck.

Tom

 

[rickes]

Tom,
Excellent Job. I ran your SQL in a new query. It worked great, except it posts the Hours on Belt in the record below it. For instance, RecordID 6, Belt Press 1, Upper Belt, 11/12/2003 should show it had 4038 Total hours on Belt. But the way the SQL shows it, it is putting the 4038 in RecordID 7. and like wise for the rest of them. Is there anyway, the total hours on belt can be put in the respective record?

As you can tell, I am totally lost with this. And I do thank you for all your help so far. This truly is a challenge.
Thanks,
Rickes

 

[THWatson]

Rickes
Drat. Thought I had it. But I see what you mean.

Let me think about it, and I will get back to you.

Tom

 

[THWatson]

Rickes
Okay, try this SQL. This should do it.

SELECT A.Equipment, A.Belt, A.ChangeDate, A.HourMeter, A.Reason, [A].[HourMeter]-(Select B.HourMeter FROM BeltReplacement B WHERE B.ChangeDate = (Select Min(C.ChangeDate) FROM BeltReplacement C WHERE C.ChangeDate > A.ChangeDate AND A.Equipment = C.Equipment AND A.Belt = C.Belt)) AS Difference, [Difference]*-1 AS [Total Hours on Belt]
FROM BeltReplacement AS A
ORDER BY A.Equipment, A.Belt DESC , A.ChangeDate;

You will notice that I had to add a column to the query because the Difference is a negative value, so the new column takes that difference and multiples it by *1 to change it to a positive value. I thought that I should be able to get this in the formula itself, but can't quite get that.

If I figure out a different way, I'll post again, but this works and the column you are still most interested in is the "Total Hours on Belt" column.

Tom

 

[rickes]

Tom,

Your the man... I ran it the way you gave it to me and it works great. Thanks so very much for your efforts.

Rickes

 

[THWatson]

Rickes
Thanks for the star. Glad we got it.

Tom

 

[THWatson]

Rickes
I knew there was a way that I could do the formula without that extra column. It's by using the ABS function, which converts a value to a positive number. In my pondering of this, I had forgotten about ABS.

So, here's a new SQL, in case you want to use it...
SELECT A.Equipment, A.Belt, A.ChangeDate, A.HourMeter, A.Reason, ABS([A].[HourMeter]-(Select B.HourMeter FROM BeltReplacement B WHERE B.ChangeDate = (Select Min(C.ChangeDate) FROM BeltReplacement C WHERE C.ChangeDate > A.ChangeDate AND A.Equipment = C.Equipment AND A.Belt = C.Belt))) AS [Total Hours on Belt]
FROM BeltReplacement AS A
ORDER BY A.Equipment, A.Belt DESC , A.ChangeDate;

Use it if you wish. It eliminates one column in the query. Or, if the horse I sent you has become part of the family, you can stay with it too. Your call.

Best regards.

Tom