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, 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@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Tuesday, May 15, 2012 5:51 PM
> To: MS_Access_Professionals@yahoogroups.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
>
Tidak ada komentar:
Posting Komentar