Date Calculation

[sonejson01]

Hi

I have a similar question to Garyaj posted earlier on this site. I have looked for an answer to my question but this is the nearest i can find and is not quite it.

I have an order databse in which there is a portal. I have created a relationship with this portal using an order id number.

The portal features a date field then a pulldown menu, that show four stages - order taken, goods picked, goods despatched, payment processed. It's objective is to record the date at which each of these stages is reached.

I would like to be able to have a field that measures the number of days, possibly even working days, between the events for each order. i.e. if an order was picked on the 10th of Jan and despatched on the 12th the field would show 2 as the answer.

Any help appreciated.

[comment]

What is the purpose of having these events as records in a separate table?

[sonejson01]

Hi

Apologies for the delay in getting back to you - I have been away on a short break.

In answer to your question I am considering adding in a few more stages to the 4 we already use. I could only think the other way of doing it would be to add each stage as a separate field to the main order database, which seemed a bit clunky. Also, I am able to pull through other details to portal database from a value database.

I am new to this and am trying to find a way of garnering information that I can present to somebody. Please excuse my ignorance if the structure is wrong.

Any suggestions appreciated.

Thanks Again.

[comment]

I don't know if the structure is wrong. It depends on what you plan to do with the data once you have it the way you want. Offhand, I would say that if every order has four (or any other fixed number) distinct events, then I can't see why they should be in a portal. That's why I asked if there's some other purpose to keeping them there - but I'm afraid I wasn't able to understand your reply.

[sonejson01]

Thanks for the reply. Sorry I am being a bit too complicated with my reply.

Basically, the objective of the exercise is to be able to analyse the number of days that pass between each stage across the orders. So for example, I might like to look at a bunch of orders that came were taken in December to see how long it took to pick and despatch them. This query might be by postcode or taking an average across from the date calcualtion I outlined in my first post.

Hope this helps.

Thanks.

[comment]

the objective of the exercise is to be able to analyse the number of days that pass between each stage across the orders.

Yes, I think you've made that part pretty clear. The thing is, this is MUCH easier to do with fields than with related records. Fields have names, and can be easily identified. Related records are an unordered set, and from the viewpoint of the parent record they all look alike - unless you make a special effort to distinguish between them.

So, unless you need to produce a report like:

Orders Taken:

• 1/1/2008

• 1/2/2008

• 1/3/2008

Goods Picked:

• 1/5/2008

• 1/7/2008

• 1/8/2008

Goods Despatched:

• 1/8/2008

• 1/8/2008

• 1/9/2008

Payments Processed:

• 1/15/2008

• 1/20/2008

• 1/22/2008

I'd suggest you keep the dates in dedicated fields of the Orders table.

[sonejson01]

Hi

Thanks for the response. I guess the report I am looking for is:

Order 1

Orders Taken: 1/1/2008

Goods Picked: 1/5/2008 Calculation: 4 [days]

Goods Despatched: 1/8/2008 Calculation: 3 [days]

etc.

I think this fits what you are saying

[comment]

Yes.