Construct a big Pivot Table for charting purposes

[tonez90]

Hi I have a number of pivot table queries which I wish to combine so I can develop one pivot table for multiple table results. Here is an example of one pivot query-note each query works

TRANSFORM Nz(Count(fsqlmonth([DateNotified])),0) AS [The Value]
SELECT TBL_Inquiries.refyear
FROM TBL_Inquiries
Where TBL_Inquiries.refyear=2009
GROUP BY TBL_Inquiries.RefYear
PIVOT fsqlmonth(TBL_Inquiries.DateNotified) In (1,2,3,4,5,6,7,8,9,10,11,12);

Now I have 6 of these to interogate individual tables. They are all the same to give me figures for each month for the year.
What I want to do is construct just one table with these queries combined for a year.
Example:

Incidents Jan Feb Mar .... -> dec (where data is used in each month)
Complaints Jan Feb Mar .... -> dec (where data is used in each month)
Next series
next series
and so on for the six tables.

I want to use this table in a charting process. I am lousy at pivot tables tec so any assistance or examples would be helpful.

Tony

Also the fsqlmonth is:
Public Function fSQLMonth(varDate)
If Not IsNull(varDate) Then fSQLMonth = DatePart("m", varDate)
End Function

 

[namliam]

Also the fsqlmonth is:
Why do this in a function? You can do this directly in the query...

Regardless I think you can resolve this with a union query.
SELECT TBL_Inquiries.refyear FROM TBL_Inquiries
UNION ALL
Select ... from AnotherTable1
UNION ALL
Select ... from AnotherTable2
UNION ALL
Select ... from AnotherTable3
UNION ALL
Select ... from AnotherTable4

If you then save this union query as qryUnionQuery ... You can use that union as a base for your crosstab:
TRANSFORM Nz(Count(fsqlmonth([DateNotified])),0) AS [The Value]
SELECT qryUnionQuery.refyear
FROM qryUnionQuery
Where TBL_Inquiries.refyear=2009
GROUP BY qryUnionQuery.RefYear
PIVOT fsqlmonth(qryUnionQuery.DateNotified) In (1,2,3,4,5,6,7,8,9,10,11,12);

I hope you get the idea....

Good luck !

 

[tonez90]

Thanks for that. I am still having a bit of trouble fitting where the Datenotified comes into play. I have constructed the first query to provide all records for the pivot table but am having trouble doing the transform and pivot side of things.
The first Query (Union) is:
SELECT TBL_Inquiries.refyear, fsqlmonth([DateNotified]) as Dnotified, "inq" as tablename FROM TBL_Inquiries
UNION ALL
Select TBL_Accidents.refyear, fsqlmonth([DateNotified]) as Dnotified, "acc" as tablename FROM TBL_Accidents
UNION ALL
Select TBL_Complaints.refyear,fsqlmonth([DateNotified]) as Dnotified, "com" as tablename FROM TBL_Complaints
UNION ALL
Select TBL_NoticeOfWorks.refyear, fsqlmonth([DateNotified]) as Dnotified, "now" as tablename FROM TBL_NoticeOfWorks;


What I would like to get out of the process is a table like this:
(lets say for a year 2009) - with each month as a count opf the records for that month and that table
Table Count(1) count(2) ..... count(12)
inq 1 34 67 90 .... (for the twelve months)
acc 23 45 9 89 ..... (for the twelve months)
com 1 3 23 56 ..... (for the twelve months)
now 345 675 67 908 .... (for the twelve months)


I hopw someone can help me.

Thanks in advance

 

[namliam]

So you make a new query with this query as your source, pivot it, put count(*) as value, DNotified as Column heading and TableName as row heading.... Dont seem that difficult?

 

[tonez90]

I have now found a solution. Hope someone else can use the solution to learn from (I certainly did and thansk to those who helped):

the union query:
SELECT TBL_Inquiries.refyear, fsqlmonth([DateNotified]) as Dnotified, "inq" as tablename FROM TBL_Inquiries
UNION ALL
Select TBL_Accidents.refyear, fsqlmonth([DateNotified]) as Dnotified, "acc" as tablename FROM TBL_Accidents
UNION ALL
Select TBL_Complaints.refyear,fsqlmonth([DateNotified]) as Dnotified, "com" as tablename FROM TBL_Complaints
UNION ALL Select TBL_NoticeOfWorks.refyear, fsqlmonth([DateNotified]) as Dnotified, "now" as tablename FROM TBL_NoticeOfWorks;

The Count query:
TRANSFORM count(Dnotified) AS [The Value]
SELECT qryUnionQuery.refyear, qryUnionQuery.tablename
FROM qryUnionQuery
WHERE TBL_Inquiries.refyear=2010
GROUP BY qryUnionQuery.tablename, qryUnionQuery.RefYear
PIVOT qryUnionQuery.DNotified In (1,2,3,4,5,6,7,8,9,10,11,12);

 

[namliam]

Well done

 

[tonez90]

Just another related question. How do I reference each of the values to populate a textbox on the screen??

 

[namliam]

How do you normaly do it?

 

[tonez90]

I have never done that before. What I was looking at is placing an individual figure say Feb(month 2) in a text box. The query is mainly used for a calendar year summary so I was looking at placing the values on a form in seperate text boxes.

Tony

 

[namliam]

Just use the wizard to build a form on your crosstab.

 

[tonez90]

Thanks for all your help