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?

Connie-

Ah, yes. The single query would be better, but you need to fix your IIf.

SELECT SalesBuyers.ContactID, IIf(([BuyerAgencyID]=1) 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;

I'm curious why you decided to use a RIGHT join in this one.

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:22 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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)
>
> ------------------------
>