BigJohnson
07-10-2006, 06:39 AM
Hi guys & gals....
I have a final SQL query to upsize from Access, and I can't figure this out, can anyone please assist.
Essentially, priority description down the left, months of the year across the top, and then a count of records as the data.
Here's my existing Access version - thanks all.
TRANSFORM Sum(OnTime)
SELECT tblDefStd.DefStdDesc FROM (tblDevCom INNER JOIN tblSites ON tblDevCom.DevComId = tblSites.DevComId) INNER JOIN (tblSiteAdd INNER JOIN (tblDefStd INNER JOIN (tblDefCatType INNER JOIN tblDefect ON tblDefCatType.DCTId = tblDefect.DCTId) ON tblDefStd.DefStdId = tblDefCatType.DefStdId) ON tblSiteAdd.AddID = tblDefect.AddID) ON tblSites.SiteId = tblSiteAdd.SiteID WHERE (DatePart('yyyy',[DateRaised])=::PickYear::) AND (tblDevCom.ComId = ::ComId::) AND (tblDevCom.Dev_Id LIKE '%::Dev_Id::%') GROUP BY tblDefStd.DefStd, tblDefStd.DefStdDesc ORDER BY tblDefStd.DefStd PIVOT DatePart('m',[DateRaised]) In (1,2,3,4,5,6,7,8,9,10,11,12);"
I have a final SQL query to upsize from Access, and I can't figure this out, can anyone please assist.
Essentially, priority description down the left, months of the year across the top, and then a count of records as the data.
Here's my existing Access version - thanks all.
TRANSFORM Sum(OnTime)
SELECT tblDefStd.DefStdDesc FROM (tblDevCom INNER JOIN tblSites ON tblDevCom.DevComId = tblSites.DevComId) INNER JOIN (tblSiteAdd INNER JOIN (tblDefStd INNER JOIN (tblDefCatType INNER JOIN tblDefect ON tblDefCatType.DCTId = tblDefect.DCTId) ON tblDefStd.DefStdId = tblDefCatType.DefStdId) ON tblSiteAdd.AddID = tblDefect.AddID) ON tblSites.SiteId = tblSiteAdd.SiteID WHERE (DatePart('yyyy',[DateRaised])=::PickYear::) AND (tblDevCom.ComId = ::ComId::) AND (tblDevCom.Dev_Id LIKE '%::Dev_Id::%') GROUP BY tblDefStd.DefStd, tblDefStd.DefStdDesc ORDER BY tblDefStd.DefStd PIVOT DatePart('m',[DateRaised]) In (1,2,3,4,5,6,7,8,9,10,11,12);"