Date range question

[Gary Nelson]

In Access2000, I have been asked to create a query / report that will show
an on-time percentage for orders received then shipped. The agreement we
have with our customer is that all orders will be received and shipped
within a three day span of time. I have already gotten the portion of the
query complete that measures the orders that have been processed and the
amount of days that it took to process them, as well as a True value for
those that were shipped on-time versus a False value for those not shipped
on-time.

The fields that I have are: JobNum - DateIn - ShipDate - DueDate

The user wants to enter a specific date range, and from that range, wants to
determine the on-time percentage. Any ideas on how this can be
accomplished?

 

[Les]

Gary,
I gave you a possible solution on your post from
yesterday. Not sure if you saw it. Basically, a
parameter query will allow entry of date ranges. It is
under your "help please" posting.

 

[Gary Nelson]

Good morning Les,

Thanks for your help, the date range is working fine. Now I am at the
portion of the query where I need to get the sum of records that were
shipped on-time within the selected date range.

For example: Date range 11/01/03 thru 11/05/03 there were 24 orders
shipped. Of the 24, there were 20 shipped within the three day alloted time
of three days. 4 were late. Date range 1/12/04 thru 1/15/04 there were 63
orders shipped. 62 were shipped on-time, 1 was late.

What I need to do now, is take the records that were on-time (designated by
a True value) and divide them by the total amount of orders within the date
range.

 

[Les]

Gary,
If you look at your other posting from yesterday -
"newbie needs help", I put some code in there that could
help you.
Instead of setting true/false, I used 1 or 0. That way,
I could sum the values to see how many met and how many
did not meet. I could then use my summed fields in the
percentage calculation.
If this is clear as mud, open your query, change it to
sql view instead of design view, and post the code here.

 

[Gary Nelson]

Got it - I tried the code you had posted - didn't work due to a missing
field on my part. Now works great - THANKS for your help.

 

[Les]

You're welcome. Good luck.

-----Original Message-----
Got it - I tried the code you had posted - didn't work due to a missing
field on my part. Now works great - THANKS for your help.



.