No need for the right join. Using an inner one now.
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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)
> >
> > ------------------------
> >
>
Tidak ada komentar:
Posting Komentar