UNION query problem

Posted on 2007-03-29
Last Modified: 2010-03-20
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

looking like:
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!

please help

i'm using:

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.ComplaintNo)
FROM tblSC_Report
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.

Thanks G

i'm using Access 2003 and my experience (nor brain power!) is that high
Question by:GrahamLaws
LVL 50

Accepted Solution

Lowfatspread earned 250 total points
ID: 18819040
is all you are now missing is month where there where no activity?

if so then you need a calendar table added to your query...

set up a table to contain all periods that are of interest to you ...

periodDescription  char(7)  
Year smallint
month smallint
primary key (year,month)

then add it to your query as ...


Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question