possibly an impossible problem

[jamiec1]

table months contains
01/01/2002
01/02/2002
01/03/2002
etc

QRY:

SELECT format(JobRecords.DateTimeCreated, "yyyy/mm&quot AS Mnth, count(JobRecords.DateTimeCreated) AS Tot
FROM ProblemCategory INNER JOIN (CustomerContracts INNER JOIN (CoveredSites INNER JOIN JobRecords ON CoveredSites.SiteID = JobRecords.SiteID) ON CustomerContracts.CustomerID = CoveredSites.CustomerID) ON ProblemCategory.ProbCatID = JobRecords.ProbCatID
WHERE (((CustomerContracts.CustomerID)=1))
GROUP BY format(JobRecords.DateTimeCreated, "yyyy/mm"


QRY2:

SELECT format(months.months,"yyyy/mm&quot AS MTH
FROM months;

QRY3 :

SELECT QRY2.MTH, IIf(IsNull([QRY.Tot]),0,[QRY.Tot]) AS Total
FROM QRY RIGHT JOIN QRY2 ON QRY.Mnth=QRY2.MTH;

QRY3 outputs

2001/01 0
2001/02 0
2001/03 0
2001/04 20
2001/05 34
etc

Is possible to keep it all in one query because i am trying to output the record to an asp page and will need to pass in the variable customerID. Not sure if it is going to be possible though as it seems like it has to be split into seperate querys to run ..

 

[jhall156]

If in your first query you change your count to a sum and then put a 1 for the true part and a 0 for the false part I think it will work. JHall

 

[MichaelRed]

Not reviewed in detail, but it looks like it could be done in a single crosstab query - except for the strange format of the date join. Not at all sure you can get that one in except as a group by clause.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over