Union Cross Tabs

[eerich]

Hi Everyone.

I'm looking for help in union cross tabs. I need to combine one result that has, for example, 6 columns of results with a second cross tab that has just 1 column of results. The left 2 columns are identical which are in my select statements are indentical.

I receive an error stating the columns do not match when trying to execute the union statement to merge the result sets. Is there a way to make this work?

For my objective with this, my 2nd crosstab will always produce just one column values (NewBU), but my first crosstab will have variable columns. My report is being used to produce a before/after scenario of when you need to show for example: 5 businesses with various staffing levels and when consolidated, the new business would be comprised of x number of positions per level. Something like:

PositionID...Description....BU1....BU2....BU3....BU4....BU5.....NewBU
Admin....Administration....3.....2......2......4......2.......4
Mgr.....Manager......10.....10......10......10......10......25
Staff....Staff Clerk.....19.....26......21......23.....24......55

Is there a way to use the Union query successfully to join these two cross tabs?

Any help is appreciated.

 

[SkipVought]

Hi,

Can you get the query for the original cross tab?

 

[dhookom]

I expect you could use some DAO code to modify the SQL property of a saved union query. You would be able to count the fields in the dynamic query so you could add Null columns in the 1 result crosstab.

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

Skip -thanks. Here is the code.

Transform  count(OUP) as PplCount
Select PositionID, Description
FROM HRData
WHERE HRData.LegacyBU NOT IN (‘N’)
Group by PositionID, Description
Order by 1
Pivot LegacyBU

UNION

Transform count(CCo) as PplCount
Select PositionID, Description
FROM NewCo
Group by PositionID, Description
Pivot NewCo.Rank;

As stated, I receive an error stating the columns don't match because the top query has dynamic results, whereas the bottom one will just be a single column.

 

[dhookom]

I would try to union the query first in a saved query and then create a crosstab based on the union query.

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

Duane - thanks. I've read that posted on here already but can't figure out what exactly do you mean. I'm sorry for being confused on that, but could you elaborate?

 

[eerich]

Sorry - for not including in my last post..

How would I create a union query with results that won't be the same output, because I know that one result set will just return a single column of data, while the first result set would be dynamic. Union queries often require the same number of columns so I can't figure out how I'd make it work in a union query, and then some sort of way cross tab from that result. Can you provide an example?

 

[PHV]

I don't think you want to UNION your crosstab queries but JOIN ON PositionID, Description

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eerich]

PHV -

That worked!

Many thanks!!