RE: [MS_AccessPros] Best way to do queries

 

Connie-

Try to avoid stacking Union upon Union. The second approach is better.

In your qUnion2 query, I see you're overriding the AER from qUnion1 field with a
0 even though the first four queries in qUnion1 return -1. Why is that?

John Viescas, author

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Microsoft Access Applications

Microsoft Office Access 2003 Inside Out

SQL Queries for Mere Mortals

<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Tuesday, May 15, 2012 8:28 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Best way to do queries

John-- There is no join of table and qUnion1. qUnion1 is all the data that is
derived from info in the db and the Contact_Status is the table where data is
entered that cannot be derived from the db (statusses of contacts before 2011).
The SQLs for the what I currently have and UNION queries are below.

Thanks for sticking with me!
Connie

qUnion1 is the one I'm hoping to implode:
SELECT qStatusBuyersDerived.ContactID, qStatusBuyersDerived.StatusID, -1 AS AER,
qStatusBuyersDerived.ListID
FROM qStatusBuyersDerived
UNION ALL SELECT qStatusSellersContactsDerived.ContactID,
qStatusSellersContactsDerived.StatusID, -1 AS AER,
qStatusSellersContactsDerived.ListID
FROM qStatusSellersContactsDerived
UNION ALL SELECT qStatusClosedBuyersDerived.ContactID,
qStatusClosedBuyersDerived.StatusID, -1 AS AER,
qStatusClosedBuyersDerived.ListID
FROM qStatusClosedBuyersDerived
UNION ALL SELECT qStatusClosedSellersContactsDerived.ContactID,
qStatusClosedSellersContactsDerived.StatusID, -1 AS AER,
qStatusClosedSellersContactsDerived.ListID
FROM qStatusClosedSellersContactsDerived
UNION ALL SELECT
qStatusClosedSellersNonAERDerived.ContactID,qStatusClosedSellersNonAERDerived.St
atusID, 0 AS AER, qStatusClosedSellersNonAERDerived.ListID
FROM qStatusClosedSellersNonAERDerived
UNION ALL SELECT qStatusRenters.ContactID, qStatusRenters.StatusID, 0 AS AER,
qStatusRenters.ListID
FROM qStatusRenters;

qUnion2 as it stands:
UNION ALL SELECT qUnion1.ContactID, qUnion1.StatusID, 0 AS AER, qUnion1.ListID
FROM qUnion1
UNION ALL SELECT Contact_Status.ContactID, Contact_Status.StatusID, -1 AS AER, 0
As ListID
FROM Contact_Status;

qUnion2 As I was thinking of changing it so that it stands on its own without
referring to another Union query. Only the last SELECT is different from
qUnion1:
SELECT qStatusBuyersDerived.ContactID, qStatusBuyersDerived.StatusID, -1 AS AER,
qStatusBuyersDerived.ListID
FROM qStatusBuyersDerived
UNION ALL SELECT qStatusSellersContactsDerived.ContactID,
qStatusSellersContactsDerived.StatusID, -1 AS AER,
qStatusSellersContactsDerived.ListID
FROM qStatusSellersContactsDerived
UNION ALL SELECT qStatusClosedBuyersDerived.ContactID,
qStatusClosedBuyersDerived.StatusID, -1 AS AER,
qStatusClosedBuyersDerived.ListID
FROM qStatusClosedBuyersDerived
UNION ALL SELECT qStatusClosedSellersContactsDerived.ContactID,
qStatusClosedSellersContactsDerived.StatusID, -1 AS AER,
qStatusClosedSellersContactsDerived.ListID
FROM qStatusClosedSellersContactsDerived
UNION ALL SELECT
qStatusClosedSellersNonAERDerived.ContactID,qStatusClosedSellersNonAERDerived.St
atusID, 0 AS AER, qStatusClosedSellersNonAERDerived.ListID
FROM qStatusClosedSellersNonAERDerived
UNION ALL SELECT qStatusRenters.ContactID, qStatusRenters.StatusID, 0 AS AER,
qStatusRenters.ListID
FROM qStatusRenters
UNION ALL SELECT Contact_Status.ContactID, Contact_Status.StatusID, -1 AS AER, 0
As ListID
FROM Contact_Status;

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@...>
wrote:
>
> Connie-
>
>
>
> What does the join between qUnion1 and the first table look like? You say it's
> another UNION query, but I'm having a hard time picturing that. Is it possible
> to do the Join inside qUnion1 by joining the table with each of the SELECT
> queries? Simplify, simplify.
>
>
>
> John Viescas, author
>
> Microsoft Office Access 2010 Inside Out
>
> Microsoft Office Access 2007 Inside Out
>
> Building Microsoft Access Applications
>
> Microsoft Office Access 2003 Inside Out
>
> SQL Queries for Mere Mortals
>
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> Sent: Tuesday, May 15, 2012 7:03 PM
> To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> Subject: Re: [MS_AccessPros] Best way to do queries
>
>
>
>
>
> John--I do believe it will probably work to implode them. Will check it
out--was
> just doing the "next thing" and not looking at the bigger picture. Would be
> awesome!
>
> Okay, I'll try harder to explain! Currently I'm making one union query
> (qUnion1). Then making another union query joining qUnion1 with a table to
make
> qUnion2. Then making another union query joining qUnion2 to a table to make
> qUnion3. I have to doublecheck but I think each of these queries are used in
> different spots.
>
> Would it be better for qUnion2 to be based on the SQL for qUnion1 (rather than
> just Unioning it to qUnion1)? So I would paste the SQL from qUnion and add
> another UNION ALL SELECT at the end for the table it's joining to. Based upon
> your answer I'll do the same thing with qUnion3.
>
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> wrote:
> >
> > Connie-
> >
> > Is it possible to "implode" Buyers AER and Buyers Non-AER; Sellers AER and
> > Sellers Non-AER; Closed Buyers AER and Closed Buyers Non-AER; and Closed
> Sellers
> > AER and Closed Sellers Non-AER using the IIf technique we just discussed?
That
> > would cut the number of queries in your UNION below in half.
> >
> > I'm not sure I understand your question about joining qDerivedStatus with a
> > table.
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> > --------------------------------
> >
> > From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > Sent: Tuesday, May 15, 2012 5:51 PM
> > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: [MS_AccessPros] Best way to do queries
> >
> >
> > Good morning/afternoon all,
> >
> > I am going through queries trying to simplify and have come up with some
> > question re the best way to do things. I had many SELECTs rather than SELECT
> ALL
> > in union queries and I'm hoping that changing that will help with the
"cannot
> > open any more databases" error. Do you think that might?
> >
> > I have a Union query (we'll call it qDerivedStatus)which joins the derived
> > StatusIDs, along with other info for 7 queries. There is a second union
query
> > (we'll call it qAllStatus) that joins qDerivedStatus with a table to create
a
> > query for all statuses (derived and entered). Then there is a third query
> which
> > joins qAllStatus with a table to create qAllStatusServices.
> >
> > For qAllStatus is there a difference between joining qDerivedStatus with the
> > table vs the following? (I will apply your input to the qAllStatusServices
> too.
> > )
> >
> > SELECT qStatusBuyersDerived.ContactID, qStatusBuyersDerived.StatusID, -1 AS
> AER,
> > qStatusBuyersDerived.ListID
> > FROM qStatusBuyersDerived
> > UNION ALL SELECT qStatusSellersContactsDerived.ContactID,
> > qStatusSellersContactsDerived.StatusID, -1 AS AER,
> > qStatusSellersContactsDerived.ListID
> > FROM qStatusSellersContactsDerived
> > UNION ALL SELECT qStatusClosedBuyersDerived.ContactID,
> > qStatusClosedBuyersDerived.StatusID, -1 AS AER,
> > qStatusClosedBuyersDerived.ListID
> > FROM qStatusClosedBuyersDerived
> > UNION ALL SELECT qStatusClosedSellersContactsDerived.ContactID,
> > qStatusClosedSellersContactsDerived.StatusID, -1 AS AER,
> > qStatusClosedSellersContactsDerived.ListID
> > FROM qStatusClosedSellersContactsDerived
> > UNION ALL SELECT qStatusClosedBuyersNonAERDerived.ContactID,
> > qStatusClosedBuyersNonAERDerived.StatusID, 0 AS AER,
> > qStatusClosedBuyersNonAERDerived.ListID
> > FROM qStatusClosedBuyersNonAERDerived
> > UNION ALL SELECT
> >
>
qStatusClosedSellersNonAERDerived.ContactID,qStatusClosedSellersNonAERDerived.St
> > atusID, 0 AS AER, qStatusClosedSellersNonAERDerived.ListID
> > FROM qStatusClosedSellersNonAERDerived
> > UNION ALL SELECT qStatusRenters.ContactID, qStatusRenters.StatusID, 0 AS
AER,
> > qStatusRenters.ListID
> > FROM qStatusRenters
> > UNION ALL SELECT qStatusEntered.ContactID, qStatusEntered.StatusID, -1 AS
AER,
> 0
> > As ListID
> > FROM qStatusEntered;
> >
> > Soooo glad you're here!
> > Connie
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (6)

Recent Activity:

• New Members 5
• New Files 2

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___