Crosstab query

[Deutz]

Hi and thanks in advance.



I am using Access 2003 and have a table that stores dollar values for each of six departments for the twelve months of the year.

So my table has columns:

DeptNm Jan Feb Mar … Dec
Sales $105 $200 $95 $400
Store $85 $45 $88 $79
...
...
...

This means I have a lot of columns stretching across the screen and would like rearrange them to have the 6 Depts across the top as columns and the 12 months as rows.

I tried to make this work with a crosstab query but could not figure out how to do it. Wondering if this is possible to do with a crosstab query or perhaps some other way?

Thanks
Deutz

[rpeare]

Assume your table for this is named tbl_Data

create this query:

SELECT DeptNm, "Jan" as MonthNm, 1 as SORT,  jan as Net FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Feb" as MonthNm, 2 as SORT,  Feb as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Mar" as MonthNm, 3 as SORT,  Mar as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Apr" as MonthNm, 4 as SORT,  Apr as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "May" as MonthNm, 5 as SORT,  May as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Jun" as MonthNm, 6 as SORT,  Jun as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Jul" as MonthNm, 7 as SORT,  Jul as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Aug" as MonthNm, 8 as SORT,  Aug as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Sep" as MonthNm, 9 as SORT,  Sep as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Oct" as MonthNm, 10 as SORT,  Oct as Net  FROM Tbl_Data
UNION ALL
SELECT DeptNm, "Nov" as MonthNm, 11 as SORT,  Nov as Net  FROM Tbl_Data
UNION ALL SELECT DeptNm, "Dec" as MonthNm, 12 as SORT,  Dec as Net  FROM Tbl_Data;

Call it Qry_CrosstabSetup

then create this query

TRANSFORM Sum(Qry_CrosstabSetup.Net) AS SumOfjan
SELECT Qry_CrosstabSetup.MonthNm, Qry_CrosstabSetup.SORT
FROM Qry_CrosstabSetup
GROUP BY Qry_CrosstabSetup.MonthNm, Qry_CrosstabSetup.SORT
ORDER BY Qry_CrosstabSetup.SORT
PIVOT Qry_CrosstabSetup.DeptNm;

and run it

[Deutz]

Well I must say I'm impressed with your reply, rpeare. That was exactly what I was looking for.

I can see now that I needed to union the data into a suitable format before trying to create the crosstab query.

Thanks heaps!

[AndreT]

The solution is great if you know how many months you have, and you can fix it to 12 months.

I have a more generalized situation with a matrix of 2 sets of list, no guarantee how many elements in each list, and don't even know what the values of the elements? but I want a inversed matrix?

I posted a thread under "Want a list from Xtab", can anyone help?

[Deutz]

One other question. As stated, I got this working no problems but I am now trying to add a WHERE clause to the union query which the crosstab query does not like.

I inserted this WHERE clause after each SELECT statement in the union query:

WHERE EmployeeNm = Forms!frmDept!txtEmployeeNm

The union query returns all the records correctly but when I run the crosstab query I get the error msg "The Microsoft Jet database does not recognise 'Forms!frmDept!txtEmployeeNm' as a valid field name or expression." The form was open and there was an employee name in the text box when I ran the query.

I thought I might need to add this form referece as a query parameter but that option was greyed out in SQL view.

Any idea what I am missing here?

Thanks

Deutz

[Deutz]

Well I solved my problem by using a function (returns the value in the textbox) in my union query instead of the textbox reference.

So I replaced WHERE EmployeeNm = Forms!frmDept!txtEmployeeNm
with WHERE EmployeeNm = GetEmployeeNm ()

and this works fine.

Like AndreT, for future reference, I also would like to know what you would do if you had much larger than 12 elements?

Thanks again for your excellent solution.

Deutz

[rpeare]

A crosstab query is only limited by the number of fields it can display (I think) which is 255 fields. If you wanted a representation of 5 years in a sequential order as long as you organized your data and applied labels that would produce your columns in the order you wanted you are not really limited in the size of the crosstab, however, if you intend to build a report, or export the data you will likely have problems.

Reports are based on having a static number of fields to use, with a crosstab that is not the case unless you specifically limit your data and are very cautious about adding new elements to your dataset (items that would be a column header) Each change in items that could affect the column headers will mean you have to program a change in your report.

Exporting crosstabs is a real pain in the ass if you have *any* criteria. If you are reporting on an entire database you will likely not have any problem. What I usually do when I have to export one is dump the contents to a table then export the table, it's ugly, but it works and I really do not need to do this very often (maybe twice in the last 10 years)

[AndreT]

Deutz, thanks for the function approach. I've always have problem with Xtab using reference or parameters, now I know how to get around them easily. Excellent.

[Paul H]

rpeare,

You seem to be onto the problem I am having exporting a crosstab query. I have come at this from 6 different directions and it has defeated all comers. It doesn't seem to matter whether I am trying to export from the query or from a table created from the query results, my code fails when exporting.

I've used the transfer spreadsheet method. It works sometimes. Since I am using a data range and using dates as column headers, there always a different number of columns. If I export to the same spreadsheet with a smaller date range the code works. If I go back to the larger date range, I get an error "Too many columns".

When I use the transfer text method, it fails at the specification level.

If I can just get the data out of my database, I've got a spreadsheet that handles it just fine. I just can't seem to automate this procedure with the varying number of columns and column headers. I can go back and give you specific error messages, but I've run through this problem so many times I've lost track of them