Hello,
I am trying to create a query that will show me the % of orders delivered ontime. The problem I have is that orders can be invoiced more than once.
To combat this I have tried to calculate the total items sent and mark them as ontime or late in one query and then create a second query that has the distinct order totals for each customer by order.
When I try to join the two queries together to get a figure for all items delivered and the original quantities ordered by customer the query displays incorrect figures. When I play around with the joins I can get the ordered quantity column to display the correct figure but the invoiced/delivered figure is rubbish.
Here is the query I am currently using:
SELECT qry_OTIF_Accounts_Selected.Decription, Sum(IIf([Week]<=Date() And [OnTime]="On_Time",Nz([Sent]),0)) AS Total_Sent_P, Sum(IIf([Week]<=Date(),Nz([Qty_Ordered]),0)) AS Total_Ordered_P, Round(IIf([Total_Sent_P]=0,0,([Total_Sent_P]/[Total_Ordered_P])*100),2) AS OTIF_P
FROM qryOrder_Quantities RIGHT JOIN qry_OTIF_Accounts_Selected ON qryOrder_Quantities.Account = qry_OTIF_Accounts_Selected.Account
GROUP BY qry_OTIF_Accounts_Selected.Decription, qry_OTIF_Accounts_Selected.Sequence
ORDER BY qry_OTIF_Accounts_Selected.Sequence;
This is an example output:
Decription Total_Sent_P Total_Ordered_P OTIF_P
Customer A 9140 6158 148.42
Customer B 5306 3352 158.29
Customer C 999 2480 40.28
Can any one tell me where I am going wrong?
I am trying to create a query that will show me the % of orders delivered ontime. The problem I have is that orders can be invoiced more than once.
To combat this I have tried to calculate the total items sent and mark them as ontime or late in one query and then create a second query that has the distinct order totals for each customer by order.
When I try to join the two queries together to get a figure for all items delivered and the original quantities ordered by customer the query displays incorrect figures. When I play around with the joins I can get the ordered quantity column to display the correct figure but the invoiced/delivered figure is rubbish.
Here is the query I am currently using:
SELECT qry_OTIF_Accounts_Selected.Decription, Sum(IIf([Week]<=Date() And [OnTime]="On_Time",Nz([Sent]),0)) AS Total_Sent_P, Sum(IIf([Week]<=Date(),Nz([Qty_Ordered]),0)) AS Total_Ordered_P, Round(IIf([Total_Sent_P]=0,0,([Total_Sent_P]/[Total_Ordered_P])*100),2) AS OTIF_P
FROM qryOrder_Quantities RIGHT JOIN qry_OTIF_Accounts_Selected ON qryOrder_Quantities.Account = qry_OTIF_Accounts_Selected.Account
GROUP BY qry_OTIF_Accounts_Selected.Decription, qry_OTIF_Accounts_Selected.Sequence
ORDER BY qry_OTIF_Accounts_Selected.Sequence;
This is an example output:
Decription Total_Sent_P Total_Ordered_P OTIF_P
Customer A 9140 6158 148.42
Customer B 5306 3352 158.29
Customer C 999 2480 40.28
Can any one tell me where I am going wrong?
