Previous Record Date Difference

dsthomson

Registered User.
Local time
Today, 10:24
Joined
Aug 23, 2013
Messages
20
Hello:
I am trying to use a query to find the previous record of a transaction and provide an additional field that computes the difference in the dates in days. (The days from TransferID 23 to TransferID 24)

TransferID Job Number Tool Number TranferDate DaysOnRent
22 1 10 9/1/13
22 1 12 9/1/13
23 2 10 10/18/13
23 2 12 10/18/13
24 3 10 12/15/13
24 3 12 12/15/13

I have seen other posts that reference the following link.
http://allenbrowne.com/subquery-01.html

This looks like a possible solution but I am having trouble figuring out exactly what this looks like in query design mode. (It may need to be adapted to work with Access 2010?)

Thank you!
 
Last edited:
Assuming all your transfer ID's are sequential, I've uploaded a solution. Follow the steps in order qrystep1..., qrystep2... etc. Make sure you understand what each are doing.
 

Attachments

Hi Geotch – Thanks for the post. I have been playing with the attachment that you posted and learned about sub queries.

I haven’t not figured out how to do what I need yet.
The information I want to sort is not sequential. If you look at my attachment you will see that I actually am using 2 separate tables for Transfers (Multiple tools can be moved in one transfer) and ToolTransfer_Sub (assigns a TransSub tracking number for each tools). I have combined the key information in ToolTransfer_Sub Query.

I want to be able to search Job numbers and find out how long each tool was at any job.

After figuring out how to find the previous date for each tool movement and its length of time at the job, I need to compare those periods of time to billing periods and bill for the overlapping time.
Any help would be appreciated.

Thank you
 

Attachments

My queries will help you do what you want. You just need to add Tool and Job in each query. Instead of using my table, use your qry to start.

I can help you further, but you need to understand what each query is doing. Look at qryStep1... can you tell me what that is doing?
 
Thanks again for the help. This is what I see each step doing:
qryStep1MaxID – Is finding the max TransSub for each tool (Col- ToolNum : MaxOfTransSub
qryStep2PreviousID – Is finding the Previous Transfer of each tool.
qryStep3maxIDdetails- Find Last Transfer Date for Each Tool Number.
(Uses TranSub to call Date) (Selecting Totals: Max, Min, Last or First makes no difference)
qryStep4minDetails-Finds Previous Transfers (Multiple) for Each Tool Number
qryStep5Final – Finds Last Transfer and Previous Transfers. Subtracts each Previous Transfer from the very Last Transfer and finds number of days between.

This is good for the Very Last and immediate previous dates but does not go further back with usefulness. A tool moved many times would cause lots of useless computations unless we find a way to reference differently?
 

Attachments

After figuring out how to find the previous date for each tool movement and its length of time at the job, I need to compare those periods of time to billing periods and bill for the overlapping time.

Ok, I can help. Have a few questions. I assume a tool is being billed for a job until it ends up at a different job? What happens when you don't have any other jobs? How do you stop billing for that tool? This would all be easier if you just had a check in and check out date for a tool and job.
 
Here's another trick to do what you want (I think). Follow the Macro which runs the queries starting with qry0_....

Basically you need to get an auto consecutive number by tool in date order. That's what the delete, append queries are doing. Once you have that you can link id to id-1 and you'll have all you need to do the calculations.

qry_3Final has the info you need if I understand your problem correctly.
 

Attachments

Geotch - this is genius- thanks.
I have gone through the steps to see how you did this.
Qry0_delete Temp
Deletes all row information except for the header from tbl_tmp
(I am not sure why it deletes the whole row when just autoID is referenced?)
Qry1_Main
Is joining information from Transfers with ToolTransfer_Sub
and appends information to tbl_temp. table adds new auto id to autoID field.

Qry2_
Copies tbl_temp and adds a column that subtracts 1 from autoID

Qry3_Final
Uses information from tbl_temp and and uses query 2 starting on line 2
Then compares Line 1 and 2 for = , if = then “use”, not “exclude”
Lastly if “Use than subtract date 1 from date 2
(I don't understand how 2ToolNumb etc. Is calling the 2nd row information?)

mcr1 puts it all together and runs the qry’s in sequence.

I guess that I don't need to understand the why's. You did understand what I was looking for and solved the problem. Thank you! (I am concerned that the autoID's are going to get absolutely huge).

To answer your previous questions:
What happens when you don't have any other jobs? -------The warehouse will be listed as job 1. It won't be billed.

This would all be easier if you just had a check in and check out date for a tool and job.---- - Yes it would be but that would only account for total time that a tool is out, not for monthly billing. A tool could be at a job for a couple of days or a couple of years. Transfers are generated via a "Transfer ticket" that is just an order form that would only have the Transfer (delivery) date. The return date won't be known till after the tool is removed from the job.
You may have a better idea of how to look at the big picture? If you do please let me know! Simpler is better.

This whole thing is going to get more complicated because various contracts have different billing criteria. Oh Boy!
This is a much bigger undertaking than I had originally thought. Your help on this thread should help be get back on track as I was totally stumped on this problem.

Now I need to figure out the next step of comparing dates and billing periods and figuring their overlapping time period to figure monthly billing. (Dive in if you are interested). (This is good stuff, I am enjoying learning about it). I appreciate the help!
 
Last edited:

Users who are viewing this thread

Back
Top Bottom