hi i have this question on the go:
but i'm stuck, and have spent the last 2 days trying to get this query to work, i'm close but i'm not there.
i need to link 4 tables:
Deliveries (all deliveries made by a supplier)
Complaints (all complaints raised against supplier)
i want to output a query showing a by month summary of supplier performance
Month Deliveries OnTime AvgLeadTime Complaints
Jan2007 86 80 12.1 2
Feb2007 0 0 0
Mar2007 10 10 8.0 0
now this needs to be generated for all suppliers regardless of whether we had delivieries or not, i.e a LEFT JOIN on tSuppliers
and i need to UNION ALL the tDeliveries and tComplaints
and then do a BETWEEN on the Period table
if that makes any sense! its 8pm here and i'm weary of this!
SELECT l.sAcc, tP.Period, Sum(l.Deliveries) AS SumOfDeliveries, Sum(l.[On Time]) AS [SumOfOn Time], Sum(l.LeadTime) AS SumOfLeadTime, Sum(l.Complaints) AS SumOfComplaints
FROM [SELECT qDel.sAcc, Format([DueDate],"yyyy-mm"
) AS Period
, Count(qDel.Prod) AS [Deliveries]
, Sum(qDel.OnTime) AS [On Time]
, Avg(qDel.LeadTime) AS [LeadTime]
, 0 as Complaints
GROUP BY qDel.sAcc, Format([DueDate],"yyyy-mm"
SELECT tblSC_Report.Account, Format([Date],"yyyy-mm") AS Period, 0 , 0 , 0, Count(tblSC_Report.Complai
GROUP BY tblSC_Report.Account, Format([Date],"yyyy-mm")
]. AS l, tP
WHERE (((l.Period) Between [tp].[pfrm] And [tp].[pto]))
GROUP BY l.sAcc, tP.Period
ORDER BY l.sAcc, tP.Period;
see the other post, for examples of what worked and didnt.
i'm using Access 2003 and my experience (nor brain power!) is that high