Me again,
For example, there are four fields in the Sales table
that look as though they contain significant data that
are not used in your query to limit the search.
PendingDate, BuyerAgencyID, AgentID, and SoldDate,
none of which are used to limit the number of records.
PendingDate 161 records
BuyerAgencyID 139 records
AgentID 86 records
SoldDate 416 records
out of 422 records.
Regards, Clive.
--- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@...> wrote:
>
> Hi Connie,
>
> Can you tell us what you are trying to obtain
> from this query, in English or American rather
> that in SQL?
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> >
> > Happy New Year!
> >
> > I'm going to be spending this holiday on Access. I've been at a stalemate so decided to ask for help. As Poirot would say "the little grey cells, they are having difficulty" :-)
> >
> > I have a query that pulls all the people that have bought using our agency or have bought one of our properties. If a couple has bought a house, there is one line for that transaction. If a single has bought there is one line for that transaction. If one half of a couple has bought there is one line for that transaction.
> >
> > The part I'm having problems is where one person from a couple has bought. If the main person has had only one transaction and the spouse has not been entered in any transactions, there's no problem. But if there are several transactions and the spouse/partner was involved in one of them but not in all of them, the query does not have the transaction in which he/she was not involved.
> >
> > I believe the problem originates from the joining of the qCustomerList_2ContactNameSpID.ContactID1.SpID to the SalesBuyers_1 table. Since the spouse/partner is not null in one instance (SalesBuyers_1.ContactID) and the criteria says that it is null, it does not show up. But... what do I do .. ? Can't get my head around this.
> >
> > This query is one of 4 similar queries that feed into a Union query (Buyers, Sellers, ClosedBuyers, ClosedSellers), so whatever is done here will be duplicated in three other queries. I'd love it if there's a way to minimize the opening of tables since I've encountered the "cannot open databases" table here in the past and have repeated tried to simplify these queries and remove the subqueries.
> >
> > The query is below.
> >
> > Thanks much!
> > Connie
> >
> > SELECT Sales.SalesID, Contacts.LastName, Contacts.FirstName, Sales.ListID, Listings.ListingAgency, Sales.BuyerAgencyID, qCustomerList_2ContactNameSpID.SpID, SalesBuyers_1.ContactID, SalesBuyers_1.SaleID
> > FROM (Listings INNER JOIN Sales ON Listings.ListID = Sales.ListID) LEFT JOIN (Contacts RIGHT JOIN ((SalesBuyers LEFT JOIN qCustomerList_2ContactNameSpID ON SalesBuyers.ContactID = qCustomerList_2ContactNameSpID.ContactID1) LEFT JOIN SalesBuyers AS SalesBuyers_1 ON qCustomerList_2ContactNameSpID.SpID = SalesBuyers_1.ContactID) ON Contacts.ContactID = SalesBuyers.ContactID) ON Sales.SalesID = SalesBuyers.SaleID
> > WHERE (((Listings.ListingAgency)=1) AND ((SalesBuyers_1.ContactID) Is Null)) OR (((Sales.BuyerAgencyID)=1) AND ((SalesBuyers_1.ContactID) Is Null));
> >
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar