Query help! one is dreadfully slow, the other doesn't work

O

obfruscated

i've had the unpleasant honor of having to add functionality to a
database that already is a kludge, what would normally be easy on a db
that was designed properly is turning into my month long nightmare. I
have experience with oracle/sql but access is a new beast for me. this
first query i wonder if theres a way to speed it up, it currently takes
about 5 minutes but it works just fine.

its rather lengthy

SELECT DISTINCT Applicants.CompanyName, Applicants.Address1,
Applicants.Address2, Applicants.City, Applicants.State, Applicants.Zip,
[Applicants-Contacts].Ins_Name, [Applicants-Contacts].Ins_address1,
[Applicants-Contacts].Ins_address2, [Applicants-Contacts].Ins_city,
[Applicants-Contacts].Ins_state, [Applicants-Contacts].Ins_zip,
Coverages.LimitN, Coverages.LimitW,
GetItemList("Products","Contracts",Contracts.ContractID) AS Items

FROM ((((Applicants INNER JOIN [Applicants-Contacts] ON
Applicants.AppID=[Applicants-Contacts].AppID) INNER JOIN
ApplicationFees ON Applicants.AppID=ApplicationFees.AppID) INNER JOIN
Contracts ON (Applicants.AppID=Contracts.AppID) AND
(ApplicationFees.ContractID=Contracts.ContractID)) INNER JOIN Coverages
ON Applicants.AppID=Coverages.AppID) INNER JOIN Products ON
Contracts.ContractID=Products.ContractID

WHERE (((Contracts.ContractStatus)="Fully Executed") And
((ApplicationFees.Fee)=300) And
(contracts.term=forms!Renewal!comboStartDate));



what this does is returns all the information regarding applicants and
how much they've paid for their term. if i take DISTINCT out it runs
much faster BUT it returns multiples of the same applicants who have
more than 1 item. so if applicant 1 has 5 items, all of applicant 1's
info is returned 5 times, it doesn't make for a pretty mailmerge
document :)


the second query that's giving me absolute fits is a variation on the
above query, the applicants have the choice of paying 1 time for 300 or
3 times for 125 each. what i'm trying to do is use a sub query but i'm
finding out that access doesn't really like it the way i want to do
it.

SELECT DISTINCT Applicants.CompanyName AS Company, Applicants.Address1,
Applicants.Address2, Applicants.City, Applicants.State, Applicants.Zip,
[Applicants-Contacts].Ins_Name, [Applicants-Contacts].Ins_address1,
[Applicants-Contacts].Ins_address2, [Applicants-Contacts].Ins_city,
[Applicants-Contacts].Ins_state, [Applicants-Contacts].Ins_zip,
Coverages.LimitN, Coverages.LimitW,
GetItemList("Products","Contracts",Contracts.ContractID) AS Items

FROM ((((Applicants INNER JOIN [Applicants-Contacts] ON
Applicants.AppID=[Applicants-Contacts].AppID) INNER JOIN
ApplicationFees ON Applicants.AppID=ApplicationFees.AppID) INNER JOIN
Contracts ON (ApplicationFees.ContractID=Contracts.ContractID) AND
(Applicants.AppID=Contracts.AppID)) INNER JOIN Coverages ON
Applicants.AppID=Coverages.AppID) INNER JOIN Products ON
Contracts.ContractID=Products.ContractID

WHERE EXISTS (select applicationfees.contractid,
sum(applicationfees.fee) as SumOfFee
from applicationfees
where (applicationfees.contractid is not null)
and (lcase(contracts.contractstatus = "fully executed"))
group by applicationfees.contractid
having sum(applicationfees.fee) =375) and
(contracts.expirationdate = [forms]![renewal]![comboEndDate]
);

what i need is for the inner query to select all who paid 375 and then
the outter query to grab all the other nastiness where
contracts.contracid in (subquery)


any ideas or do i need to be more specific in what i'm asking for?
 
J

John Viescas

Dear Obfruscated-

Why do you include Products in the first query? It appears you have a
custom function that returns the list of products (GetItemList). What
information is duplicated when you exclude DISTINCT? Product info? If
that's the case, remove the Products table from the query. It'll also help
if there's an index on Contracts.ContractStatus, ApplicationFees.Fee, and
Contracts.Term.

Your second query is failing because you're referencing a table in the
subquery WHERE clause that isn't in the FROM clause. You also must return
only one column in a subquery. I suspect you really want to do:

SELECT Applicants.CompanyName AS Company, Applicants.Address1,
Applicants.Address2, Applicants.City, Applicants.State, Applicants.Zip,
[Applicants-Contacts].Ins_Name, [Applicants-Contacts].Ins_address1,
[Applicants-Contacts].Ins_address2, [Applicants-Contacts].Ins_city,
[Applicants-Contacts].Ins_state, [Applicants-Contacts].Ins_zip,
Coverages.LimitN, Coverages.LimitW,
GetItemList("Products","Contracts",Contracts.ContractID) AS Items

FROM ((((Applicants INNER JOIN [Applicants-Contacts] ON
Applicants.AppID=[Applicants-Contacts].AppID) INNER JOIN
ApplicationFees ON Applicants.AppID=ApplicationFees.AppID) INNER JOIN
Contracts ON (ApplicationFees.ContractID=Contracts.ContractID) AND
(Applicants.AppID=Contracts.AppID)) INNER JOIN Coverages ON
Applicants.AppID=Coverages.AppID)

WHERE Contracts.ContractStatus = "fully executed")
AND Contracts.ExpirationDate = [Forms]![Renewal]![comboEndDate]
AND ContractID IN
(SELECT applicationfees.contractid
from applicationfees
group by applicationfees.contractid
having sum(applicationfees.fee) =375);

--
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
obfruscated said:
i've had the unpleasant honor of having to add functionality to a
database that already is a kludge, what would normally be easy on a db
that was designed properly is turning into my month long nightmare. I
have experience with oracle/sql but access is a new beast for me. this
first query i wonder if theres a way to speed it up, it currently takes
about 5 minutes but it works just fine.

its rather lengthy

SELECT DISTINCT Applicants.CompanyName, Applicants.Address1,
Applicants.Address2, Applicants.City, Applicants.State, Applicants.Zip,
[Applicants-Contacts].Ins_Name, [Applicants-Contacts].Ins_address1,
[Applicants-Contacts].Ins_address2, [Applicants-Contacts].Ins_city,
[Applicants-Contacts].Ins_state, [Applicants-Contacts].Ins_zip,
Coverages.LimitN, Coverages.LimitW,
GetItemList("Products","Contracts",Contracts.ContractID) AS Items

FROM ((((Applicants INNER JOIN [Applicants-Contacts] ON
Applicants.AppID=[Applicants-Contacts].AppID) INNER JOIN
ApplicationFees ON Applicants.AppID=ApplicationFees.AppID) INNER JOIN
Contracts ON (Applicants.AppID=Contracts.AppID) AND
(ApplicationFees.ContractID=Contracts.ContractID)) INNER JOIN Coverages
ON Applicants.AppID=Coverages.AppID) INNER JOIN Products ON
Contracts.ContractID=Products.ContractID

WHERE (((Contracts.ContractStatus)="Fully Executed") And
((ApplicationFees.Fee)=300) And
(contracts.term=forms!Renewal!comboStartDate));



what this does is returns all the information regarding applicants and
how much they've paid for their term. if i take DISTINCT out it runs
much faster BUT it returns multiples of the same applicants who have
more than 1 item. so if applicant 1 has 5 items, all of applicant 1's
info is returned 5 times, it doesn't make for a pretty mailmerge
document :)


the second query that's giving me absolute fits is a variation on the
above query, the applicants have the choice of paying 1 time for 300 or
3 times for 125 each. what i'm trying to do is use a sub query but i'm
finding out that access doesn't really like it the way i want to do
it.

SELECT DISTINCT Applicants.CompanyName AS Company, Applicants.Address1,
Applicants.Address2, Applicants.City, Applicants.State, Applicants.Zip,
[Applicants-Contacts].Ins_Name, [Applicants-Contacts].Ins_address1,
[Applicants-Contacts].Ins_address2, [Applicants-Contacts].Ins_city,
[Applicants-Contacts].Ins_state, [Applicants-Contacts].Ins_zip,
Coverages.LimitN, Coverages.LimitW,
GetItemList("Products","Contracts",Contracts.ContractID) AS Items

FROM ((((Applicants INNER JOIN [Applicants-Contacts] ON
Applicants.AppID=[Applicants-Contacts].AppID) INNER JOIN
ApplicationFees ON Applicants.AppID=ApplicationFees.AppID) INNER JOIN
Contracts ON (ApplicationFees.ContractID=Contracts.ContractID) AND
(Applicants.AppID=Contracts.AppID)) INNER JOIN Coverages ON
Applicants.AppID=Coverages.AppID) INNER JOIN Products ON
Contracts.ContractID=Products.ContractID

WHERE EXISTS (select applicationfees.contractid,
sum(applicationfees.fee) as SumOfFee
from applicationfees
where (applicationfees.contractid is not null)
and (lcase(contracts.contractstatus = "fully executed"))
group by applicationfees.contractid
having sum(applicationfees.fee) =375) and
(contracts.expirationdate = [forms]![renewal]![comboEndDate]
);

what i need is for the inner query to select all who paid 375 and then
the outter query to grab all the other nastiness where
contracts.contracid in (subquery)


any ideas or do i need to be more specific in what i'm asking for?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

dcount in query 8
HELP WITH Access QUERY PLEASE :) 8
How do you make a Query that shows records even if a field is blan 5
Help Please 5
Query Format Help 0
cross tab query help 3
Very slow query 2
query too slow 1

Top