Calculating days between dates on separate rows.

[Newie]

Hi,

I had a quick look to see if this was covered but failed, don't know if anyone can help?

I'd like to know if it's possible to find the number of days difference between the events in the following Query example ie: between 03/09/2012 and 07/10/2012

Id............Name..................From................To
12345.......SMITH John.........01/09/2012........03/09/2012
12345.......SMITH John.........07/10/2012........20/10/2012

Any help would be much appreciated !

Newie

 

[RainLover]

Do you want to do this as a User Defined Function. (VBA Code) Or do you want to do this using SQL in a query.

You have posted some good information on the table but I would like to see more records.
Does it make a difference as to the Name. How do you wish to sort the records in the table.
Can you post your Table as part of a separate Database.

Please POST using Access 2003. I don't have 2007 or 2010.

http://support.microsoft.com/kb/290136 This article may help.

 

[Newie]

Hi Rain !

Thanks for your speedy reply although not sure I can resolve this as I have Access 2002 (will attempt to attach sample anyway).
I had already come across that link example before I posted and tried to edit the formula & Fields with no luck.
Also realised after I logged out that I should have mentioned there would be more names and I'd like it to calculate over the Id number (Over Group), starting again with each new ID (Person) rather than over all entries (Running Sum).
I'm self taught by picking up and studying a database at work which only used Queries so that's all I know but to be honest, if I can get an answer from anyone I really don't mind what method it takes !!!
I can see that this is a tricky one !

Best,

Newie

 

[RainLover]

Received your Table. 2002 is fine. Thanks for the additional Info.

My time zone is vastly different to yours, so I will have a proper look tomorrow unless someone else does this for you.

This can be done in a query.

Suggestions.

Don't use spaces in your naming. You need a primary key to identify each record as unique. Suggest you use Autonumber. This applies to all of your tables. I have attached a paper on Naming Conventions. I suggest that you read it.

Back in a while.

 

[Newie]

Hi Rain,

Thanks for the tips within your Naming Conventions attachment.

Very informative & very useful......I shall be following it's recommendations in future !

Best,

Newie

 

[RainLover]

Hi Rain,

Thanks for the tips within your Naming Conventions attachment.

Very informative & very useful......I shall be following it's recommendations in future !

Best,

Newie

It would be good if you started using these conventions NOW. If not you will come across problems with your code later in your development.

I have not had enough time to address your problem throughly but I am attaching a database that does a similar thing.

If you can't work it out then post back what you have and we will see if we can fix it for you. Don't forget Access help can be very useful in these situations.

 

[Newie]

I have not had enough time to address your problem throughly but I am attaching a database that does a similar thing.

Hi Rain,

That's great !

There's enough for me to see how it works in the Query formula, that was the initial stage which I just couldn't get past...... but you've cracked it!
I've just been spending the past couple of hours expanding data, tweaking and re-naming Fields to suit my data (not forgetting to OMIT spaces between names of course!) so now I can move forward.

Many thanks for your help, it's much appreciated, people like me are sure glad there are people like you around !

Best,

Newie

 

[RainLover]

It is a pleasure to help people like yourself.