ACCESS SQL Query needed.

Question

Okay this one has me stumped.. mainly because i have been working on this problem for the entire day and I (and boss) decided to go an alternative route. However my brain is fried.

I present to you the following picture:

Now the left hand table is the export table (it is not a physical table). This is what I want. The 5 right hand tables are where the data is coming from.

I want the data from the latest month + 11 previous months. (in some situations like the example shown, there is data only for the one month. So for the previous 11 months I *DO * want the 12 months to show with 0 as the count entries. Is that possible? Please look at my hand drawn picture at the end)

Note: Please note that the left hand side table is not an physical table. it is the result of a SQL SELECT statement

The current SQL statement looks like this. It grabs EVERYTHING (not 12 months) and it does a weird join which fails in my current example:

SELECT A.month, A.count, B.count, C.count, D.count, E.count
FROM [table name] AS A, [table name]  AS B, [table name]  AS C, [table name]  AS D, [table name] AS E
WHERE B.month=A.month and C.month=A.month and D.month=A.month and E.month=A.month
ORDER BY A.month DESC;

I am gonig to draw a picture of what I want the final to look like

+=============================================================+
|  month    | A.count | B.count | C.count | D.count | E.count |
+-------------------------------------------------------------+
|2010-08-01      3           2       0          5        18   |
|2010-07-01      0           0       0          0         0   |
|2010-06-01      0           0       0          0         0   |
|2010-05-01      0           0       0          0         0   |
|2010-04-01      0           0       0          0         0   |
|2010-03-01      0           0       0          0         0   |
|2010-02-01      0           0       0          0         0   |
|2010-01-01      0           0       0          0         0   |
|2010-12-01      0           0       0          0         0   |
|2010-11-01      0           0       0          0         0   |
|2010-10-01      0           0       0          0         0   |

etc

Answer 1

You can accomplish this by including sub-queries in your SELECT.

    SELECT MonthDate,
    (
      SELECT COUNT(*) FROM A WHERE A.Month = M.MonthDate
    ) [A.Count],
    (
      SELECT COUNT(*) FROM B WHERE B.Month = M.MonthDate
    ) [B.Count],
    (
      SELECT COUNT(*) FROM C WHERE C.Month = M.MonthDate
    ) [C.Count],
...
    FROM Months M

For more info on sub-queries see: http://allenbrowne.com/subquery-01.html

Answer 2

I have used a number table for this called Counter with a field Num containing sequential integers up to at least 12.

SELECT x.Mnth, Nz([a].[Count],0) AS a, 
       Nz([b].[Count],0) AS b, Nz([c].[Count],0) AS c, 
       Nz([d].[Count],0) AS d, Nz([e].[Count],0) AS e
FROM (((((SELECT DateAdd("m",-[Num],#2010/7/1#) AS Mnth 
          FROM [Counter] WHERE Num<=12)  AS x 
LEFT JOIN a ON x.Mnth = a.month) 
LEFT JOIN b ON x.Mnth = b.month) 
LEFT JOIN c ON x.Mnth = c.month) 
LEFT JOIN e ON x.Mnth = e.month) 
LEFT JOIN d ON x.Mnth = d.month
ORDER BY a.Count DESC;