Joining 3 queries and displaying results in seperate columns

[dragonfly352758]

Hi,
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.

They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).

My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.

What I have is a join query (Which I have named Mech Final Equipment H63 Joined)

Using this SQL:

SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
SELECT DISTINCTROW [Mech Final Equipment 6 Mth].[Final Equipment], Sum([Mech Final Equipment 6 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 6 Mth]
GROUP BY [Mech Final Equipment 6 Mth].[Final Equipment]
UNION
SELECT DISTINCTROW [Mech Historical Final Equipment].[Final Equipment], Sum([Mech Historical Final Equipment].[Sum Of Down]) AS Duration
FROM [Mech Historical Final Equipment] 
GROUP BY [Mech Historical Final Equipment].[Final Equipment];

Which returns a table that looks like this:

Final Equipment, Duration

Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004

And so on.


What I need the table to look like is this

Final Equipment, 3 Mth, 6 Mth, Historical

Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004

And so on, like a cross tab.

I tried to do a crosstab query but I don't have enough fields.

Can anyone help me?

Any help is appreciated.

A~:banghead:

 

[nanscombe]

I think you were very close.

I simply added an extra field, "Title", to each of your queries to act as the headings for the crosstab, as you will see from the attached file.

Mech Final Equipment H63 Joined_Crosstab

Final Equipment		Total Of Duration	3 Mth	6 Mth	Historical
Ancillary Equipment	2413			225	401	1787
Brush Unit		11775			1252	2519	8004

I've just left the total field in, it could be removed.

 

[JHB]

You can do it by using 2 queries.
1. query.

SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS
[3 Mth], 0 as [6 Mth], 0 as [Historical] FROM [Mech Final Equipment 3 Mth] GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]
UNION
SELECT DISTINCTROW [Mech Final Equipment 6 Mth].[Final Equipment], 0 as [3 Mth], Sum([Mech Final Equipment 6 Mth].[Sum Of Down]) AS [6 Mth], 0 as [Historical]
FROM [Mech Final Equipment 6 Mth] GROUP BY [Mech Final Equipment 6 Mth].[Final Equipment]
UNION
SELECT DISTINCTROW [Mech Historical Final Equipment].[Final Equipment], 0 as [3 Mth], 0 as [6 Mth], Sum([Mech Historical Final Equipment].[Sum Of Down]) AS Duration
FROM [Mech Historical Final Equipment] GROUP BY [Mech Historical Final Equipment].[Final Equipment];

Then run a query on the above query.

SELECT [Final Equipment], Max(YourQuery.[3 Mth]) AS [3 Mth], Max(YourQuery.[6 Mth]) AS [6 Mth], Max(YourQuery.Historical) AS [Historical]
FROM YourQuery
GROUP BY [Final Equipment];

 

[dragonfly352758]

I think you were very close.

I simply added an extra field, "Title", to each of your queries to act as the headings for the crosstab, as you will see from the attached file.

Mech Final Equipment H63 Joined_Crosstab

Final Equipment        Total Of Duration    3 Mth    6 Mth    Historical
Ancillary Equipment    2413            225    401    1787
Brush Unit        11775            1252    2519    8004

I've just left the total field in, it could be removed.

Thanks nanscombe, However I cannot add a "title" field to the query. All of my queries are run of an imported excel spreadsheet, that contain thousands of fields. It would not be practical to add another field, and I wouldn't know where to put it. My filters and Critera narrow the info down.

Thanks for trying to help though. I really appreciate it.

A~

 

[nanscombe]

As you saw from my attached file all I did was add an extra field to the end of the query by adding an Title: "3 Mth", Title: "6 Mth" or Title: "Historic" in it's own column.


ETA: Hang on, I'll try it a slightly different way ...

 

[nanscombe]

Right. Rather than adding the "Title" column to each query, I've amended your Union query to do the job.

SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration[COLOR="red"], "3 Mth" AS Title[/COLOR]
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment][COLOR="Red"], '3 Mth'[/COLOR]
UNION
SELECT DISTINCTROW [Mech Final Equipment 6 Mth].[Final Equipment], Sum([Mech Final Equipment 6 Mth].[Sum Of Down]) AS Duration[COLOR="red"], "6 Mth" AS Title[/COLOR]
FROM [Mech Final Equipment 6 Mth]
GROUP BY [Mech Final Equipment 6 Mth].[Final Equipment][COLOR="red"], '6 Mth'[/COLOR]
UNION
SELECT DISTINCTROW [Mech Historical Final Equipment].[Final Equipment], Sum([Mech Historical Final Equipment].[Sum Of Down]) AS Duration[COLOR="red"], "Historic" AS Title[/COLOR]
FROM [Mech Historical Final Equipment] 
GROUP BY [Mech Historical Final Equipment].[Final Equipment][COLOR="red"], 'Historic'[/COLOR];

Then the Crosstab query, without the total this time, becomes

TRANSFORM Sum([Mech Final Equipment H63 Joined].Duration) AS SumOfDuration
SELECT [Mech Final Equipment H63 Joined].[Final Equipment]
FROM [Mech Final Equipment H63 Joined]
GROUP BY [Mech Final Equipment H63 Joined].[Final Equipment]
PIVOT [Mech Final Equipment H63 Joined].Title;

Giving the result

Final Equipment		3 Mth	6 Mth	Historic
Ancillary Equipment	225	401	1787
Brush Unit		1252	2519	8004

 

[dragonfly352758]

Thanks JHB, that worked perfectly!!