Selasa, 15 Mei 2012

RE: [MS_AccessPros] Best way to do queries

 

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

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar