By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,375 Members | 3,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,375 IT Pros & Developers. It's quick & easy.

I Surrender- Issue working out set diffing information sets from one table

P: 49
Ok Guys here the issue!
I can do this task using 3 crosstabs individually but:-

I have to report information on multiple training programmes. Each report is setup for periods 1 to 12 (period = 1 per month)

Crosstab 1 reports quantities of people starting training each period

Crosstab 2 reports quantities of people leaving training each period

Crosstab 3 reports quantities of people achieving their training programme for each period.

As stated I can do this by running each crosstab. I have to try and consolidate this system so that either through SQL or another process I can do the job.
Any advice from anyone who may have done something similar in the past??
Gareth
May 8 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Is this for a report? You can use subreports.

But if you want it all in one query I suppose you could use a union query, as long as the columns are all the same name and you have a row field to determine which row of records corresponds to which crosstab.
May 8 '07 #2

JConsulting
Expert 100+
P: 603
create a select query for each of your crosstabs. For a union...the number of fields output for each member has to match.

then paste the SQL into a final query so that it resembles this.

SELECT 'firstCrosstab' as Source,[_Answers_Crosstab].UserID, [_Answers_Crosstab].[Total Of ID], [_Answers_Crosstab].[1], [_Answers_Crosstab].[2], [_Answers_Crosstab].[5]
FROM _Answers_Crosstab
union all
SELECT 'SecondCrosstab' as Source,[_Answers_Crosstab].UserID, [_Answers_Crosstab].[Total Of ID], [_Answers_Crosstab].[1], [_Answers_Crosstab].[2], [_Answers_Crosstab].[5]
FROM _Answers_Crosstab
union all
SELECT 'ThirdCrosstab' as Source,[_Answers_Crosstab].UserID, [_Answers_Crosstab].[Total Of ID], [_Answers_Crosstab].[1], [_Answers_Crosstab].[2], [_Answers_Crosstab].[5]
FROM _Answers_Crosstab;

J
May 8 '07 #3

JConsulting
Expert 100+
P: 603
create a select query for each of your crosstabs. For a union...the number of fields output for each member has to match.

then paste the SQL into a final query so that it resembles this.

SELECT 'firstCrosstab' as Source,[_Answers_Crosstab].UserID, [_Answers_Crosstab].[Total Of ID], [_Answers_Crosstab].[1], [_Answers_Crosstab].[2], [_Answers_Crosstab].[5]
FROM _Answers_Crosstab
union all
SELECT 'SecondCrosstab' as Source,[_Answers_Crosstab].UserID, [_Answers_Crosstab].[Total Of ID], [_Answers_Crosstab].[1], [_Answers_Crosstab].[2], [_Answers_Crosstab].[5]
FROM _Answers_Crosstab
union all
SELECT 'ThirdCrosstab' as Source,[_Answers_Crosstab].UserID, [_Answers_Crosstab].[Total Of ID], [_Answers_Crosstab].[1], [_Answers_Crosstab].[2], [_Answers_Crosstab].[5]
FROM _Answers_Crosstab;

J
results look thusly

Source UserID Total Of ID 1 2 5
firstCrosstab 0 2 3 1 2
SecondCrosstab 0 2 3 1 2
ThirdCrosstab 0 2 3 1 2
May 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.