Union with different number of columns

[Lavenderchan]

Hello,

I am currently to to summarized data so I can create a pie chart in Access 2010. What I have done is tried a union with different number of columns however I am unable to get it to work. If this is not the right approach suggestions are always welcome.

SELECT distinct [Cleveland Assigned REAC].reac_last_inspection_score
AS "No Score", null as "30-49", null AS "50-69", null AS "70-79", null as "80-89", null as
"90-100"
FROM [Cleveland Assigned REAC]
WHERE ((([Cleveland Assigned REAC].reac_last_inspection_score)<"0"));

Union


SELECT distinct [Cleveland Assigned REAC].reac_last_inspection_score
AS "30-49", null AS "50-69", null AS "70-79", null as "80-89", null as
"90-100"
FROM [Cleveland Assigned REAC]
WHERE ((([Cleveland Assigned REAC].reac_last_inspection_score) Between
"30" And "49"));

Union


SELECT distinct AVG([Cleveland Assigned
REAC].reac_last_inspection_score) AS "50-69", null AS "30-49" ,null AS "70-79", null as "80-89", null as "90-100"
FROM [Cleveland Assigned REAC]
WHERE ((([Cleveland Assigned REAC].reac_last_inspection_score) Between
"50" And "69"));
group by [Cleveland Assigned REAC].property_id, Cleveland Assigned
REAC].reac_last_inspection_score

Union


SELECT distinct AVG([Cleveland Assigned
REAC].reac_last_inspection_score) AS "70-79"
FROM [Cleveland Assigned REAC]
WHERE ((([Cleveland Assigned REAC].reac_last_inspection_score) Between
"70" And "79"));
group by [Cleveland Assigned REAC].property_id, Cleveland Assigned
REAC].reac_last_inspection_score

Union


SELECT distinct AVG([Cleveland Assigned
REAC].reac_last_inspection_score) AS 80-89
FROM [Cleveland Assigned REAC]
WHERE ((([Cleveland Assigned REAC].reac_last_inspection_score) Between
"80" And "89"));
group by [Cleveland Assigned REAC].property_id, Cleveland Assigned
REAC].reac_last_inspection_score

Union


SELECT distinct AVG([Cleveland Assigned
REAC].reac_last_inspection_score) AS 90-100
FROM [Cleveland Assigned REAC]
WHERE ((([Cleveland Assigned REAC].reac_last_inspection_score) Between
"90" And "100"));
group by [Cleveland Assigned REAC].property_id, Cleveland Assigned
REAC].reac_last_inspection_score

Thanks,
Keri

 

[lameid]

The one requirement for a union query is that they must have the same number of columns and columns of the same datatype in the same order in each select.

I did not look at your queries very closely but you could add column aliases for missing fields and use nulls, zero length strings and 0's as appropriate for the values.

When I was first learning Access I was having trouble getting columns right as I was weak in SQL at the time. The suggestion was to make a query for each query and then union the queries together...

Select qry1.*
From qry1

[red]Union ALL[/red]

Select qry2.*
From qry2

[red]Also worth noting is that you may want Union All instead of Union[/red]. Union All gives you all the records, Union has a distinct effect (removes duplicates) and may throw off your analysis.

 

[Andrzejek]

I would also suggest not to do all 6 Select statements at the time. It is hard to see what's wrong with all of them.
Start with 2 Select statements, make them work, then add another one.
Make them work, and add another one. And so on...

Aliases:
You have: ...AS "70-79", and that's fine
and then you have: ...AS 80-89 (80-89 = -9, is that what you want? Probably not)
...AS 90-100 (90-100 = -10, same deal here)

Hint: Field names for your outcome will come from the very first Select in your Union(s), all field names / aliases from all other Selects will be ignored.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Andrzejek]

Your statement may look something like this:

[pre]
SELECT distinct
reac_last_inspection_score AS "No Score",
null as "30-49",
null AS "50-69",
null AS "70-79",
null as "80-89",
null as "90-100"
FROM [Cleveland Assigned REAC]
WHERE reac_last_inspection_score)<"0"
Union
SELECT distinct null as "No Score"
reac_last_inspection_score AS "30-49",
null AS "50-69",
null AS "70-79",
null as "80-89",
null as "90-100"
FROM [Cleveland Assigned REAC]
WHERE reac_last_inspection_score Between "30" And "49"
Union
SELECT distinct null as "No Score",
null AS "30-49" ,
AVG(reac_last_inspection_score) AS "50-69",
null AS "70-79",
null as "80-89",
null as "90-100"
FROM [Cleveland Assigned REAC]
WHERE reac_last_inspection_score Between "50" And "69"
group by property_id, reac_last_inspection_score
Union
SELECT distinct null as "No Score",
null AS "30-49",
null AS "50-69",
AVG(reac_last_inspection_score) AS "70-79",
null as "80-89",
null as "90-100"
FROM [Cleveland Assigned REAC]
WHERE reac_last_inspection_score Between "70" And "79"
group by property_id, reac_last_inspection_score
Union
SELECT distinct null as "No Score",
null AS "30-49",
null AS "50-69",
null AS "70-79",
AVG(reac_last_inspection_score) AS "80-89",
null as "90-100"
FROM [Cleveland Assigned REAC]
WHERE reac_last_inspection_score Between "80" And "89"
group by property_id, reac_last_inspection_score
Union
SELECT distinct null as "No Score",
null AS "30-49",
null AS "50-69",
null AS "70-79",
NULL AS "80-89",
AVG(reac_last_inspection_score) AS "90-100"
FROM [Cleveland Assigned REAC]
WHERE reac_last_inspection_score Between "90" And "100"
group by property_id, reac_last_inspection_score
[/pre]

Assuming each individual Select statement works, is correct, and gives you the results you want.
I am not sure you can GROUP BY the field that is not in your Select portion of your SQL

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[lameid]

I am not sure you can GROUP BY the field that is not in your Select portion of your SQL

Yes you can. If you have a group by clause, every field in the Select clause must either be in the group by clause or use an aggregate function.

 

[Andrzejek]

You are right, lameid
After my post, I tried it and now I know (better). I don’t think I needed anything like that before.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.