ACCESS FEED: [MS_AccessPros] Re: Best way to do queries

John- thanks for catching that. In the process of making changes I've needed to change that portion. I have had several Union queries stacked on each other with not using UNION ALL. Eek!

Thanks again!
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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]
>