Another 'Between two date' calculation

[Erik010409]

Hi

Hope someone can help.

I have a repeating field which shows jobs done for each customer, with the date done.

Average Date Job Cost

7 17/1/11 500 lts £10

2 10/1/11 200 lts £10

7 8/1/11 500 lts £10

75876 1/1/11 600 lts £10

I also have a field which works out the amount of days since the last job done (Jobs::Date- Jobs 2::Date) which works fine EXCEPT for the first Job/date (oldest) which doesn't have a previous date to work with. So results in a figure of 733487 (or near).

WHY ?

I would like to find the days between jobs to work out how long a delivery lasts, I will then take an 'average of averages' and work out approx. when a next delivery would be needed.

Hope this makes a little sense. It sounded great in my head !!!

Any help would be appreciated, either with what I have done or suggesting a better way.

Many thanks

Erik

[comment]

the first Job/date (oldest) which doesn't have a previous date to work with. So results in a figure of 733487 (or near).

WHY ?

Because if there is no previous date, you are not subtracting anything from the job date, so you're getting the same date - only expressed as a number.

I have a repeating field which shows jobs done for each customer,

I hope not - at least your subsequent description suggests you have a portal, not a repeating field.

[Erik010409]

Because if there is no previous date, you are not subtracting anything from the job date, so you're getting the same date - only expressed as a number.

I hope not - at least your subsequent description suggests you have a portal, not a repeating field.

Sorry

The 'WHY' was an explanation of what I was trying to do. I realise that I have no previous date, just not sure how to get around the issue.

You are correct it is a portal.

[comment]

Try something like =

Case ( Jobs 2::Date ; Date - Jobs 2::Date ) 

[Erik010409]

Try something like =

Case ( Jobs 2::Date ; Date - Jobs 2::Date ) 

Many thanks for that. That works nicley.

Is it possible to do a count in a portal ?

i.e. how many times have I done a job for this customer, by count (job date)

[comment]

Do you have a Customers table? If you do, you can count related jobs by =

Count ( Jobs::CustomerID )

assuming the relationship is:

Customers::CustomerID = Jobs::CustomerID

[Erik010409]

Do you have a Customers table? If you do, you can count related jobs by =

Count ( Jobs::CustomerID )

assuming the relationship is:

Customers::CustomerID = Jobs::CustomerID

Thanks very much for that, does the trick nicely.

I made this database 2 years ago ish and seem to have lost all memory of how I did it !

Thanks for your help.