Creating a query based on query that shows me on time deliveries

[JeremyT]

Hello,

I am very new to access and trying to figure out how to create a % on time deliveries query. I have successfully created a query that shows me total deliveries by month. I based it on another query that have numbers converted to dates due to our ODBC using yyyymmdd as number records. I just need to add a column on the right of the total deliveries in January how many were on time. Here is an image of what I have thus far.


Query where I have converted dates as well as other delivery info that I based my totals query on:
qryDeliveryinfo image
qryDeliveryinfo design view


Query that I want to add the column for on time deliveries:
qryTotalDeliveriesMonthly image
qryTotalDeliveriesMonthly design view


I have a column in my query that has fields such as scheduled del date[SCADATECONV] and a field that has scheduled del time[SCATIME]. I also have fields that show actual del date[ACADATECONV] and actual del time[ACATIME]. The exact times are numerical values. IE 150000(3PM). To throw more complexity to this we consider "on time" as 30 minutes within the scheduled del time. Here is the code for my initial query(based on another query I created with all the info I needed):

SELECT Format([ACADATECONV],"mmmm yyyy") AS MonthYear, Count([qry_% on time].STOPID) AS CountOfSTOPID
FROM [qry_% on time]
GROUP BY Format([ACADATECONV],"mmmm yyyy"), Format([ACADATECONV],"mmmm yyyy");


Your help is sincerely appreciated. Let me know if you require any other info for your recommendation.



Jeremy

[June7]

So you need total deliveries and then the count of on-time delieveries and divide to calculate a percentage. Think I would do this on a report instead of in query. The total deliveries can be calculated in a group (group by month?) footer and another textbox can count the deliveries that meet the on-time criteria. Another textbox can divide for the percentage.

[JeremyT]

Yes, but I need to know the syntax for doing this. Preferably in my already created query. I can create a report based on that query. I would like to add a column to my query qryTotalDeliveriesMonthly image. On the new column I would like to show out of those total deliveries how many were on time (we consider on time within 30 minutes of scheduled time). In my qryDeliveryinfo image I show all the delivery information needed to create this, but do not know how to write the expression to do it. Help would be greatly appreciated.

The logic is that I want is: Count the number of deliveries by month are on time. This is defined by saying if the scheduled delivery date (table SCADATECONV) is equal to the actual delivery date (table ACADATECONV) AND the scheduled delivery time (table SCATIMECONV) is within 30 minutes of actual delivery time (table ACATIMECONV). Thank you!

PSI managed to convert the text to time in the time tables.

Jeremy

[June7]

If you do the aggregate calcs on report instead of query, the detail info is available for the report. However, either way, need an expression that will accrue only the records that meet the time criteria. So in the aggregate query would be something like:

Sum(IIf(condition expression here, 1, 0))

The conditional expression would be along the lines of:

SCADATECONV=ACADATECONV AND DateAdd("n",30,SCATIMECONV) > ACATIMECONV

Why have the date and time values in separate fields?