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:

_{(source: filmot.com)}

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

+1 for the question -- I expect your standard of 'brain-fried' is different from mine, as my version of this post would be less coherent. — Smandoli
– Smandoli, Commented Aug 28, 2010 at 20:13

Fionnuala · Accepted Answer · 2010-08-30 14:50:32Z

1

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;

edited Aug 30, 2010 at 14:50

answered Aug 27, 2010 at 21:07

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

40 Comments

masfenix Over a year ago

This produces an error. Can you have "Select .. AS A" in the select part? or is that only for the FROM part?

Fionnuala Over a year ago

I tested in Access and it worked for me. Do you have the counter table set up?

David-W-Fenton Over a year ago

The syntax for derived tables will depend on your SQL mode. If SQL 92, the above should work, but if SQL 89 (the legacy default), you need FROM [SELECT...]. As Alias.

Fionnuala Over a year ago

Eh? I am using the legacy default. There is absolutely nothing wrong with Nz([a].[Count],0) AS a, which is what I think masfenix is asking about, it has worked in Access for yonks. I even tested with Access 2000.

Fionnuala Over a year ago

It is not exactly a derived table, it relies on a counter or number table containing integers to get the dates, so not so different to formatting. Such tables are enormously useful for any, he he, number of things, as I am sure you know.

|

Leon · Accepted Answer · 2010-08-27 21:05:30Z

0

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

answered Aug 27, 2010 at 21:05

Leon

3,4011 gold badge25 silver badges20 bronze badges

Collectives™ on Stack Overflow

ACCESS SQL Query needed

2 Answers 2

40 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

40 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related