ACCESS FEED: Re: [MS_AccessPros] Can these queries be put into one query?

Selasa, 15 Mei 2012

Re: [MS_AccessPros] Can these queries be put into one query?

John--
Let me retry.

I have two queries that look at the same data; one determines AERBuyers and the other NonAerBuyers. The query determining AERBuyers creates a StatusID of 8 and the other query creates a status of 10. If these two queries would be put in a union query the result would be a line for each SalesBuyers record with the appropriate status. These queries are then combined in a union query with other queries which derive other statuses.

Since creating these I've realized that it might be better to have one query with one criteria for AERBuyers and another for NonAERBuyers. To make this happen it seems that I'd end up with an Iif statement in the StatusID column.

IIf([BuyerAgencyID] Or [ListingAgency]=1,8,10)

Does that create a situation where databases are opened up? (My "cannot open any more databases") situation. Would one query with the above Iif statement be faster/better than the two separate queries?

I've created the query with that Iif statement and put it first below. The others are below it.

Thanks once again!
Connie

Combined:
SELECT SalesBuyers.ContactID, IIf([BuyerAgencyID] Or [ListingAgency]=1,8,10) AS StatusID, Sales.ListID
FROM (Listings RIGHT JOIN Sales ON Listings.ListID = Sales.ListID) RIGHT JOIN SalesBuyers ON Sales.SalesID = SalesBuyers.SaleID;

AERBuyers:
SELECT SalesBuyers.ContactID, 8 AS StatusID, Sales.ListID, Sales.BuyerAgencyID, Listings.ListingAgency
FROM (Listings INNER JOIN Sales ON Listings.ListID = Sales.ListID) INNER JOIN SalesBuyers ON Sales.SalesID = SalesBuyers.SaleID
WHERE (((Sales.BuyerAgencyID)=1)) OR (((Listings.ListingAgency)=1));

NonAERBuyers:
SELECT SalesBuyers.ContactID, 10 AS StatusID, Sales.ListID, Sales.BuyerAgencyID, Listings.ListingAgency
FROM (Listings INNER JOIN Sales ON Listings.ListID = Sales.ListID) INNER JOIN SalesBuyers ON Sales.SalesID = SalesBuyers.SaleID
WHERE (((Sales.BuyerAgencyID)<>1 Or (Sales.BuyerAgencyID) Is Null) AND ((Listings.ListingAgency)<>1));

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> Please explain what you mean by "combine them." I see no IIf statements in your
> SQL.
>
> 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)
>
> ------------------------
>