Hi Connie,
Congratulations!
Just as well I didn't see your previous post then <g>.
Those are the best problems - the ones you fix yourself.
Regards, Clive.
--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> SOLVED!!! Just had to give those little grey cells a little rest for them to get over their dislike of subqueries. After all, sub queries do work magic! And in the process I found 3 other records that had a similar problem to the one I'd found.
>
> Thank you Clive for taking the time to try to noodle through this with me. I hope you didn't spend too much more time after my email this morning.
>
> Woo Hoo! I'm almost done with this project!!!
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> >
> > Clive,
> >
> > I'll try to give a better description. Guess I'm used to John Viescas the maestro of SQLs!
> >
> > The purpose of this query is to be used in a report called "CustomerList". It's a list of our customers and their status with other misc info. A status is determined (ClosedBuyers in this instance) and each Sale our company (ListingsAgency = 1 Or BuyerAgencyID = 1) is involved in should result in a line for each buying couple or a line for each buying single involved. Thus, if there are 6 people in the SalesBuyers table for a certain SaleID and there are two sets of couples and two singles, there would be 4 lines (1 for each couple and 1 for each single). So in the table I have two criteria lines for couples where both are entered--one for BuyerAgencyID = 1 and one for LinstingsAgency = 1) and two criteria lines for singles or couples where only one is entered -- one for BuyerAgencyID = 1 and one for ListingsAgency = 1.
> >
> > The part that is not working is when one person of a couple is in SalesBuyers table (ex: bought for business) AND the missing person from the couple has been entered in a previous sale. In this instance the line for this sale is completely missing. My best guess is that the query is determining that the missing person has been entered in a sale (even though a different SaleID).
> >
> > The reason there are fields that are not used to limit the results is that they are there for the form for Filtering, info, etc. The BuyerAgencyID is used to limit this query. Since I've been dealing with the "cannot open any more databases" I've tried when possible to open a form only when needed and pass any other info through rather than open the form again later in a parent query.
> >
> > Hope this is clearer!
> > Connie
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Clive" <zctek@> wrote:
> > >
> > > 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 (10) |
Tidak ada komentar:
Posting Komentar