UNION all select * from

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a shorter way to write this,
perhaps replacing the multiple WHERE
clause so it only needs to be written
once?

SELECT * FROM Volunteers_Inquiry
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Applying
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Assigned
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_onVacation
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_Retired
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_MIA
WHERE [VolunteerRoleClerical] Is Not Null
UNION ALL SELECT * FROM Volunteers_DoNotPlace
WHERE [VolunteerRoleClerical] Is Not Null

Cheers,
WebDude Out
 
One way to simplify the query is to simplify your data. From your SQL
statement, I'm guessing you have 8 different "conditions" re: Volunteers.
In a spreadsheet, you might use 8 different sheets.

In Access (you posted in an Access.queries newsgroup), you can use 1 table
by simply adding a "condition" field to document what your (apparent)
multi-table structure now tries to capture.
 
Arif said:
can u use inner join? if u can then query will be shorter than this.
arif.
Click to expand...


perhaps, i neglected to mention that
these are queries, not tables..

Volunteers_Inquiry
Volunteers_Applying
Volunteers_WaitingToBeAssigned
Volunteers_Assigned
Volunteers_onVacation
Volunteers_Retired
Volunteers_MIA

and that i have no idea what an INNERjoin does,
even with two MS ACCESS manuals
sitting right beside me.

Though I understand what an INTERSECT query does
and a UNION query and an EXCEPT query..the JOIN
queries seem to do a bit of everything.
InnerJoin,OuterJoin, Leftjoin, Right Join..
me confused.


cheers,
WebDude
 
Hi Jeff,

Yes, i am using Ms Access 2000.
I also neglected to mention that
these are queries, not tables..

Volunteers_Inquiry
Volunteers_Applying
Volunteers_WaitingToBeAssigned
Volunteers_Assigned
Volunteers_onVacation
Volunteers_Retired
Volunteers_MIA

There are several other tables
that are used to return the data
in those queries above.

And since each of those queries
are use throughout the entire database,
on several different forms,
they are packed with complicated calculated fields.

Also I use those same queries to generate reports and fancy graphs
to please my boss..who doesnt pay me, btw, cause im
volunteering.. BUT SILL :-D


Um,

Our principle table is called "Volunteers". And In that table, each record
includes the following usual fields; ID, FName,LName,telephone...(etc.)..
plus the following NUMBER fields;

[VolunteerRoleClerical]
[VolunteerRoleCreativePlay]
[VolunteerRoleClothingRoom]
[VolunteerRoleDriver]
[VolunteerRoleFamilySupport]
[VolunteerRoleTutor]
[VolunteerRoleOlderBrother]
[VolunteerRoleOlderSister]
[VolunteerRoleGraphicDesigner]
[VolunteerRoleProgramAnimation]
[VolunteerRoleOther]

For each field/role the volunteer ranks his/her preference for that role by
entering a number (1 to 11) in that Numberfield. For example, a volunteer
might enter a "1" in the [VolunteerRoleDriver] numberfield meaning that their
first choice for a role would be a Driver. Then the same person might enter
a "2" in the [VolunteerRoleOlderSister] field indicating her second choice
would be an OlderSister role...etc etc. This also allows for duplicate
rankings say, if she wanted to be a Tutor just as much as an OlderSister and
ProgramAnimator.. then shed rank them all as "1". Or if she just wanted to
be a Driver, then shed put a "1" in the driver field and leave all the other
fields null.


So, id like to be able to call up all the Volunteers who ranked OlderSister
as a role theyd like.. within the "Applying" AND "WaitingToBeAssigned"
queries...yet, id still like the option of looking thru the "Retired"
query of volunteers for an OlderSister if nothing else works out.. we get
over 100 NEW volunteers every year which is why we created those 8
catagories/queries (at the top of this post) and why my UNION query is so
compounded.

Cheers,
WebDude!
 
1) Just create another query:
select * from union_query where [] is not null.

2) Write it as a subquery:
select * from (select * from... ) where [] is not null

(david)
 
david@epsomdotcomdotau said:
1) Just create another query:
select * from union_query where [] is not null.

2) Write it as a subquery:
select * from (select * from... ) where [] is not null
Click to expand...

Hi Dave,

I understood your second suggestion and wrote it this way;


SELECT * FROM
(SELECT * FROM Volunteers_Inquiry
UNION ALL SELECT * FROM Volunteers_Applying
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
UNION ALL SELECT * FROM Volunteers_Assigned
UNION ALL SELECT * FROM Volunteers_onVacation
UNION ALL SELECT * FROM Volunteers_Retired
UNION ALL SELECT * FROM Volunteers_MIA
UNION ALL SELECT * FROM Volunteers_DoNotPlace)
WHERE [VolunteerRoleClerical] Is Not Null

...it worked perfectly!
you just shortened my code by half!
thanks a million! :-)

Cheers!
WebDude
 
You wouldn't have this issue if your tables were normalized ;-)

--
Duane Hookom
MS Access MVP


WebDude said:
david@epsomdotcomdotau said:
1) Just create another query:
select * from union_query where [] is not null.

2) Write it as a subquery:
select * from (select * from... ) where [] is not null
Click to expand...

Hi Dave,

I understood your second suggestion and wrote it this way;


SELECT * FROM
(SELECT * FROM Volunteers_Inquiry
UNION ALL SELECT * FROM Volunteers_Applying
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
UNION ALL SELECT * FROM Volunteers_Assigned
UNION ALL SELECT * FROM Volunteers_onVacation
UNION ALL SELECT * FROM Volunteers_Retired
UNION ALL SELECT * FROM Volunteers_MIA
UNION ALL SELECT * FROM Volunteers_DoNotPlace)
WHERE [VolunteerRoleClerical] Is Not Null

..it worked perfectly!
you just shortened my code by half!
thanks a million! :-)

Cheers!
WebDude
Click to expand...
 
From your description, you have multiple ("repeating") fields, each
describing a condition/choice/rating. A more normalized design would
recognize the many-to-many relationship and use three tables (your
Volunteers, your Roles, and a "junction" table with VolunteerRole (choices).

This third table would have as many rows for a VolunteerID as that volunteer
indicated Role choices.

To look up all the volunteers who indicated "OlderSister", start with the
junction table.

One major advantage here is that you can change the number of Roles (add a
new row in the Roles table) and not have to change anything else (assumes
you are using form/subform construction to associate Volunteers with Roles).

--
Regards

Jeff Boyce
<Office/Access MVP>

WebDude said:
Hi Jeff,

Yes, i am using Ms Access 2000.
I also neglected to mention that
these are queries, not tables..

Volunteers_Inquiry
Volunteers_Applying
Volunteers_WaitingToBeAssigned
Volunteers_Assigned
Volunteers_onVacation
Volunteers_Retired
Volunteers_MIA

There are several other tables
that are used to return the data
in those queries above.

And since each of those queries
are use throughout the entire database,
on several different forms,
they are packed with complicated calculated fields.

Also I use those same queries to generate reports and fancy graphs
to please my boss..who doesnt pay me, btw, cause im
volunteering.. BUT SILL :-D


Um,

Our principle table is called "Volunteers". And In that table, each record
includes the following usual fields; ID, FName,LName,telephone...(etc.)..
plus the following NUMBER fields;

[VolunteerRoleClerical]
[VolunteerRoleCreativePlay]
[VolunteerRoleClothingRoom]
[VolunteerRoleDriver]
[VolunteerRoleFamilySupport]
[VolunteerRoleTutor]
[VolunteerRoleOlderBrother]
[VolunteerRoleOlderSister]
[VolunteerRoleGraphicDesigner]
[VolunteerRoleProgramAnimation]
[VolunteerRoleOther]

For each field/role the volunteer ranks his/her preference for that role by
entering a number (1 to 11) in that Numberfield. For example, a volunteer
might enter a "1" in the [VolunteerRoleDriver] numberfield meaning that their
first choice for a role would be a Driver. Then the same person might enter
a "2" in the [VolunteerRoleOlderSister] field indicating her second choice
would be an OlderSister role...etc etc. This also allows for duplicate
rankings say, if she wanted to be a Tutor just as much as an OlderSister and
ProgramAnimator.. then shed rank them all as "1". Or if she just wanted to
be a Driver, then shed put a "1" in the driver field and leave all the other
fields null.


So, id like to be able to call up all the Volunteers who ranked OlderSister
as a role theyd like.. within the "Applying" AND "WaitingToBeAssigned"
queries...yet, id still like the option of looking thru the "Retired"
query of volunteers for an OlderSister if nothing else works out.. we get
over 100 NEW volunteers every year which is why we created those 8
catagories/queries (at the top of this post) and why my UNION query is so
compounded.

Cheers,
WebDude!




Jeff Boyce said:
One way to simplify the query is to simplify your data. From your SQL
statement, I'm guessing you have 8 different "conditions" re: Volunteers.
In a spreadsheet, you might use 8 different sheets.

In Access (you posted in an Access.queries newsgroup), you can use 1 table
by simply adding a "condition" field to document what your (apparent)
multi-table structure now tries to capture.
Click to expand...
Click to expand...
 
Hi Duane! I myself am working from a non-normalized table and am trying to
use a Union Query to use as a row source for a combo box. (I could tell you
why, but you might not want to know...) Anyhow, I can't specifically select
the results, I'm guessing, because the results are derived from a single
record on a table. Will a subquery method allow me to get around that? I've
tried your syntax for the subquery and it gives me the same results that I
got before. Any clues?
--
Why are you asking me? I dont know what Im doing!

Jaybird


Duane Hookom said:
You wouldn't have this issue if your tables were normalized ;-)

--
Duane Hookom
MS Access MVP


WebDude said:
david@epsomdotcomdotau said:
1) Just create another query:
select * from union_query where [] is not null.

2) Write it as a subquery:
select * from (select * from... ) where [] is not null
Click to expand...

Hi Dave,

I understood your second suggestion and wrote it this way;


SELECT * FROM
(SELECT * FROM Volunteers_Inquiry
UNION ALL SELECT * FROM Volunteers_Applying
UNION ALL SELECT * FROM Volunteers_WaitingToBeAssigned
UNION ALL SELECT * FROM Volunteers_Assigned
UNION ALL SELECT * FROM Volunteers_onVacation
UNION ALL SELECT * FROM Volunteers_Retired
UNION ALL SELECT * FROM Volunteers_MIA
UNION ALL SELECT * FROM Volunteers_DoNotPlace)
WHERE [VolunteerRoleClerical] Is Not Null

..it worked perfectly!
you just shortened my code by half!
thanks a million! :-)

Cheers!
WebDude
Click to expand...
Click to expand...
 
Back
Top