Re: On Time Delivery Query

Thanks again - it works. Thanks so much for your help!

"Al Campagna" <newsgroups@xxxxxxxxxxx> wrote in message news:%23vo38A3bIHA.4684@xxxxxxxxxxxxxxxxxxxxxxx
John,
The totals for each group should be done on the report footers, not the query.

Place an unbound Text Control (ex. name = totEarly), in whatever Group Footer section you want totals for.
Make the ControlSource of that text control...
= Sum(IIf([Status]="On Time",1,0))
that will yield a count of Early in that group.

Repeat that process with another unbound text control (ex. name = totAll), with a ControlSource of...
= Sum(*)
that will yield a count of all records in that group.

If you still have problems, I need you to explain exactly what you did, where you did it, and the code involved.
--
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:OIEKrSybIHA.1376@xxxxxxxxxxxxxxxxxxxxxxx
I didn't work with the part where =Sum(IIf([Status]="On Time",1,0)). It shows as a text when you preview the report. Same thing with the =Count(*). Sorry I'm a newbie. I probably misunderstood some of the details.
Thanks again and I will try it again.
John

"John Torres" <johntorres@xxxxxxxxxxxxxxxxxxx> wrote in message news:82098A0F-D849-4E1C-98E0-32A687444E0F@xxxxxxxxxxxxxxxx
THANK YOU VERY MUCH.
I will try it tomorrow morning- I appreciate it so much!!!
John

"Al Campagna" <newsgroups@xxxxxxxxxxx> wrote in message news:ezcwptobIHA.1164@xxxxxxxxxxxxxxxxxxxxxxx
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
    ... 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: Calculation down a coumn
    ... In the query behind your report, ... In any Group or Report Footer of that report... ... will always yield the correct total for the group... ... > have subtotal at the end of the totals column. ...
    (microsoft.public.access.formscoding)
  • Re: subreport/report total question
    ... it gives me the exact same totals as ... the job footer should do). ... In the subreport, move your DayTotal text box into the Report Footer ... Page Header ...
    (microsoft.public.access.reports)
  • Re: subreport/report total question
    ... In the subreport, move your DayTotal text box into the Report Footer section, so it collects all values in the subreport. ... job totals always match the Work_Date totals because it gives me a job total ... > I have a Report with two subreports put in the detail section of the> main ...
    (microsoft.public.access.reports)
  • Re: Page totals error in report - doesnt add correctly
    ... display it on a form or report. ... query based on the Import Table. ... All Totals text boxes properties are set to General number with 2 decimal ...
    (microsoft.public.access.reports)