Re: On Time Delivery Query

John,
You could apply my calculation and bind the value to a field name like
ShipStatus.
In your query design grid...
ShipStatus :
IIf([ShipDate]<=DateAdd("d",-3,[DueDate]),"Early",IIf([ShipDate]>=DateAdd("d",-2,[DueDate])
And [ShipDate]<=[DueDate],"On Time","Late"))

Place the "bound" field ShipStatus on your report.
In any legitimate Footer, this would yield the Early count for whatever
Group.
=Sum(IIf([Status]="On Time",1,0))
Use that for each ShipStatus value.

Now, in each group, =Count(*) would yield the total count of all records.
So... given that you know how many records there are, and the count of
each ShipStatus type, you can now calculate the percentages of each
ShipStatus.
Which (I'll bet a nickel) is probably what you're really looking for
after all...

Given what I know about this report, I would consider breaking out the
Status values... each in it's own column.
Early
Early
Late
OnTime
On time
Early
Totals 3 2 1

When/if you break this report by month or week, this layout has better
visual impact than just a 1column dump. You might notice a pattern of
Lates early in each week, or certain times of the month... yadayada. Just a
suggestion.

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:E91A8B20-6A03-42CF-BC94-94D14A294304@xxxxxxxxxxxxxxxx
Is there any way to summarize the total of Early, On Time and Late on the
query or report?
Thanks again!
John


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:B1171988-68AA-447B-9AB8-48A8E9C9088B@xxxxxxxxxxxxxxxx
WOW.....Thanks for the quick response and it works. I was amazed!


"Al Campagna" <newsgroups@xxxxxxxxxxx> wrote in message
news:%23RfXlnnbIHA.5164@xxxxxxxxxxxxxxxxxxxxxxx
John,
=
IIf([ShipDate]<=DateAdd("d",-3,[DueDate]),"Early",IIf([ShipDate]>=DateAdd("d",-2,[DueDate])
And [ShipDate]<=[DueDate],"On Time","Late"))

Please check my syntax for "finger checks", but that should do it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message
news:AE539D76-7D35-4062-8F8B-1CCC160A6AC9@xxxxxxxxxxxxxxxx
I'm trying to create a query for "On Time Delivery". I have query
fields of PO, Date Received, Due Date and Date Shipped. I'd like to
calculate EARLY (shipped 3 days early from the due date), On Time
(Shipped on Due Date or 2 days early) and LATE (Shipped past Due Date)
and on a business days only (excluding Saturday and Sunday). These are
all coming from 3 different tables. I would appreciate any ideas.

Thanks,

John







.

Relevant Pages

  • Re: On Time Delivery Query
    ... Microsoft Access MVP ... and you'll never work a day in your life." ... The totals for each group should be done on the report footers, ... You could apply my calculation and bind the value to a field name like ShipStatus. ...
    (microsoft.public.access.reports)
  • Re: On Time Delivery Query
    ... Microsoft Access MVP ... and you'll never work a day in your life." ... The totals for each group should be done on the report footers, ... like ShipStatus. ...
    (microsoft.public.access.reports)
  • Re: On Time Delivery Query
    ... If ShippedDate is between DueDate -2 days before and DueDate + 2 days after = OnTime ... and you'll never work a day in your life." ... The totals for each group should be done on the report footers, ... You could apply my calculation and bind the value to a field name like ShipStatus. ...
    (microsoft.public.access.reports)
  • Re: On Time Delivery Query
    ... The totals for each group should be done on the report footers, ... Repeat that process with another unbound text control (ex. ... that will yield a count of all records in that group. ... You could apply my calculation and bind the value to a field name like ShipStatus. ...
    (microsoft.public.access.reports)
  • Re: On Time Delivery Query
    ... The totals for each group should be done on the report footers, ... that will yield a count of all records in that group. ... You could apply my calculation and bind the value to a field name like ShipStatus. ...
    (microsoft.public.access.reports)