Some help deciphering

[blindeddestiny]

Hi Guys,

Wondering if anyone can help me with some deciphering of code.

In short I am working with Access and trying to run a report that ends up using 100% of the cpu and eventually having to force quit the application. I have found the source of the problem and code below, but having trouble figuring out exactly why it is doing what it's doing.

I have posted the code and all the queries it needs to run. Al other queries run and complete quickly it's the last one that causes problems.

Thanks in advance for any help.



QryArrears
-----------
SELECT q.paymentid, q.tenancyid, u.unitref, q.arrears
FROM tenancy AS t, unit AS u, (select paymentid, a.tenancyid, arrears
from qrypaymentstatementcumarrears a,
(
select tenancyid, max(date) as maxdate
from qrypaymentstatementcumarrears
where Date <=Forms![frmArrearsReport]![txtDate]
group by tenancyid
) b
where a.tenancyid = b.tenancyid and a.date = b.maxdate) AS q
WHERE q.tenancyid = t.tenancyid
and t.unitid = u.unitid
and arrears > 0
ORDER BY unitref;


'This is referncing a query that references equations in several 'other queries. However these all run successfully. Below is the 'SQL for the other queries:

qryPaymentStatementCumArrears
---------------------------------
SELECT OutPay.paymentid, OutPay.tenancyid, OutPay.date, OutPay.due, OutPay.received, (select sum(Arrears)
from qryPaymentStatementArrears as inPay
where inPay.date <= outPay.date and inPay.tenancyid=outpay.tenancyid) AS Arrears, OutPay.note, OutPay.chequename, OutPay.Rent
FROM qryPaymentStatementArrears AS OutPay
GROUP BY OutPay.paymentid, OutPay.tenancyid, OutPay.date, OutPay.due, OutPay.received, OutPay.note, OutPay.chequename, OutPay.Rent, OutPay.Arrears
ORDER BY OutPay.tenancyid, OutPay.date;

qryPaymentStatementArrears
----------------------------
SELECT qryPaymentStatement.paymentid, qryPaymentStatement.tenancyid, qryPaymentStatement.Date, qryPaymentStatement.Due, qryPaymentStatement.Received, due-received AS Arrears, qryPaymentStatement.Note, qryPaymentStatement.ChequeName, IIf(IsRent=0,"No","Yes") AS Rent
FROM qryPaymentStatement;

QryPaymentStatement
----------------------
select paymentid, tenancyid, Date,amount as [Due], 0 as [Received], Note, ChequeName, IsRent
from payment
where type = "Due"
UNION select paymentid, tenancyid, date,0,amount, note, ChequeName, IsRent
from payment
where type = "Received"
ORDER BY date;


The final query above simply references a table.

 

[r937]

please repost this in one of the Access forums, e.g. forum701

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[blindeddestiny]

Thanks, Will do.
Wasn't too sure what best place to put it would be being partially SQL and partially Access scripts.

 

[r937]

Access uses (a version of) SQL as its language

SQL Server uses (a version of) SQL as its language

SQL != SQL Server

there is nothing related to SQL Server in your Access problem



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[gmmastros]

Eventhough it's SQL, it's still Access's version of SQL which is different from SQL Server's version of SQL.

Anyway... you say it's the last query that is causing your problem, right. Looking at it, I have a question for you.

Do you understand the difference between union and union all? Union will return distinct rows amongst the queries and union all will return duplicates. Since it takes time to determine what is a duplicate, union performs slower than union all. If your data is such that there cannot be duplicates anyway, you are better off using union all.

Also... I notice that both queries are similar. The where clause is different and a couple of the columns have hard coded values, but I think it can be combined in to one query which may improve performance.

select paymentid, 
       tenancyid, 
       Date,
       iif(type="Due", amount, 0) as [Due], 
       iif(type="Due", 0, amount) as [Received], 
       Note, 
       ChequeName, 
       IsRent
from   payment
where type = "Due" Or Type = "Received"
ORDER BY date;

[!]note:[/!] My Access skills are a bit rusty so the syntax may not be 100% accurate.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[blindeddestiny]

Ah Sorry I didn't realise that they were different forms of SQL. Apologies.

And gmmastros, thank you for your help, but it was actually the top QryArrears SQL formula that is causing the problems. Sorry I meant the last formula in the sequence.

Apologies for wording it incorrectly on my part.
Your advice on union all is useful though and appreciated, I did not know that.

Only been learning SQL for the last month or so, trying my best to learn as quickly as possible