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

[garethfx]

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

[Rabbit]

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.

[JConsulting]

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

[JConsulting]

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