Jump to content

Another 'Between two date' calculation


This topic is 4401 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

This topic is 4401 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.