INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Union with different number of columns

Union with different number of columns

(OP)
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

RE: Union with different number of columns

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...

CODE

Select qry1.*
From qry1

Union ALL

Select qry2.*
From qry2 

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

RE: Union with different number of columns

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.

RE: Union with different number of columns

Your statement may look something like this:

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
 

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.

RE: Union with different number of columns

Quote:


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.

RE: Union with different number of columns

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close