Calculate number of days with parameters

[Zuzu]

Hello all

First off, love this forum.... has provided so much assistance for me in learning Access!

I am having trouble writing a query to calculate the number of days between certain events.

Currently i have a table set up that contains:

tblMove
Object
Start Date
Move Date
Status (can be either 'On Hire', 'Off Hire', or 'Returned')

Now, i want to be able to count the number of days between the start date and the move date when the status is changed to 'Off Hire' - which in below example 1 would be 9 days.

However, if there is no status change to 'Off Hire', then i want the result to display the days between the last record set and the start date, which in below example 2 would be 14 days

example 1
Object A......01/01/15......02/01/15......On Hire
Object A......01/01/15......06/01/15......On Hire
Object A......01/01/15......10/01/15......Off Hire
Object A......01/01/15......15/01/15......Returned

example 2
Object A......01/01/15......02/01/15......On Hire
Object A......01/01/15......06/01/15......On Hire
Object A......01/01/15......10/01/15......On Hire
Object A......01/01/15......15/01/15......Returned

Or, to put it in simpler terms..... i want the query to run through the results and return the number of days when it hits the first recordset with 'Off Hire' status, otherwise, return the the number of days from the last recordset in the query.

Hopefully someone understands what i am trying to do and can give me some advice.

Regards

Zuzu

 

[plog]

the last record set

Without specifically telling us how to order your data there is no such thing as 'last'. You need to better define this. As in, "the record with the latest Move Date value".

Even after you solve that, you still need an autonumber primary key for your data. You need a way to uniquely identify your data. For example:

Object, StartDate, MoveDate, Status
A, 1/1/15, 2/1/15, On Hire
A, 2/1/15, 10/1/15, On Hire
A, 7/1/15, 9/1/15, Returned



For that data what do you expect the query to return? Don't explain, show me with data--and I mean everything--show me the number of records, the fields and the values you expect.

 

[Zuzu]

Hi Plog

Thanks for your response...... will try to explain it a bit better for you...

I want to track the number of days an object has been on site for hire charges purposes.

The object has a start date, and then incurs charges until the status is changed to 'Off Hire'. If there is no status change to 'Off Hire', then the charges will continue up to date ().

tblMove
MoveID
Object
Start Date
Move Date
Status (can be either 'On Hire', 'Off Hire', or 'Returned')



example 1
ID....Object.......StartDate.......MoveDate......Status
1......Object A......01/01/15......02/01/15......On Hire
2......Object A......01/01/15......06/01/15......On Hire
3......Object A......01/01/15......10/01/15......Off Hire
4......Object A......01/01/15......15/01/15......Returned

Result i want: Object......Start Date......Last Move Date (when status = 'off hire'......= 9 Days

example 2
ID....Object.......StartDate.......MoveDate......Status
1......Object A......01/01/15......02/01/15......On Hire
2......Object A......01/01/15......06/01/15......On Hire
3......Object A......01/01/15......10/01/15......On Hire
4......Object A......01/01/15......15/01/15......On Hire

Result i want: Object......Start Date......Date ()......= x Days

Hope this is a bit better explained.

Cheers

Zuzu

 

[plog]

will try to explain it a bit better for you..

Thats explicitly what I ask you not to do. Please read my prior post.

 

[sneuberg]

In the example sequences the start date is alway the same. Is that always that way? What are these objects? What happens to them after one of these sequences? Does whatever represented Object A turn in to Object B?

 

[Zuzu]

Hi Sneuberg

I work on a site that makes use of hire equipment.

So in my previous example, Object A could be a forklift that we have hired.

Hire charges start the day that it is delivered (eg 01/07/15). We then use it for 10 days - after 10 days we 'Off Hire' the object, and therefore stop paying hire charges. However, it might not be returned for another week after that (of which we are not charged for hire costs). The examples i have below track the usage of that object on site, and the different 'Move Dates' represent a move from one location to another.

The query i want is to simply work out how many days this unit is hired for (when status changes to 'off hire'). If there is no 'off-hire' status, then tell me how many days that unit has been accruing hire charges from the start date up until today.

I should also mention that tblMove is just recording the movements of the object whilst it is on site.

Once the status of the unit is changed to 'returned', the object is shipped off site.

Cheers

Zuzu

 

[arnelgp]

SELECT T1.[MoveID], T1.[Object], T1.[StartDate], T1.[MoveDate], T1.[Status], DATEDIFF("d", T1.[StartDate], IIF(T1.[Status] <> "Off Hire", DMAX("MoveDate", "tblMove", "[Object]= '" & T1.[Object] & "' AND [STATUS] <> "Off Hire"), T1.[MoveDate])) AS CountOfDays FROM tblMove AS T1 ORDER BY T1.[Object], T1.[MoveID];

 

[sneuberg]

I’ve attached a database which has a query which I believe does what you want. However this database is a reorganized version of what you seem to have. Your examples show one table with the object’s name and the start date being repeated. This is not in what they call normal form. This repetition can lead to errors, is not efficient for data entry, and is not necessary if you split this structure into two tables. In the attached database I put the data in two tables. The table tblHire has a Hire ID, the Equipment Description and the Start Date. The related tblMove has the Move Id, the Hire ID as the foreign key and Status and the Status Date. A third table in the database, Status is included as a row source for the status combo box.

I’ve included a form so that you see how a form is set up for this two table system. The frmHire has the fields from the tblHire and a subform, sfrmMove, which has the status and status date field from the tblMove. I hope you will agree that this would make data entry easier as you do not have to repeat the entries for the Equipment Description and the Start Date. I didn’t add any error checking, for example to make sure dates are later than the start date. If you need help for this sort of checking let me know.

The query and report produce the output you want. I think.

If you have a lot of existing data that would have to been divided between these tables let me know and I will write code that will assist you in doing this.

 

[Zuzu]

ArnelG -

Thanks for your help mate.... didn't achieve exactly what i wanted, but i have learnt a few things pulling apart your query.



Sneuberg -

Really appreciate the help you have given me.... i have been banging my head against a brick wall trying to work out how to achieve this.

It is people like you that make forums such as this extremely useful and enjoyable to read.

Being new to databases, i will enjoy taking your example apart and applying it to what i have already started. Has been a great experience so far learning new things, and the help that i can get from this site will hopefully enable me to get a useful database working to assist in my role.

Once again... Cheers!!!


Zuzu

 

[sneuberg]

I've attached a new version of the Hire database. I fixed bug in the form and the query was change to an outer join so that it would include records where no status records had been entered.