ACCESS FEED: 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.StatusID, 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.StatusID, 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, 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@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Tuesday, May 15, 2012 7:03 PM
> To: MS_Access_Professionals@yahoogroups.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> , 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@yahoogroups.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>
> > 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]
>