Some help deciphering

[blinddestiny]

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.

 

[blinddestiny]

After some experimentation and disection, it'll work successfully up to
" AND ((q.[arrears])>0))"
it will crash whilst applying this command.

If I remove that line and use the last line
"ORDER BY unit.unitref"
this will crash it as well.

So basically my refined script of:

SELECT q.paymentid AS PaymentId, q.tenancyid AS TenancyId, unit.unitref AS Unit, q.arrears AS Arrears
FROM unit, tenancy, qryArrearsSourceQ AS q
WHERE ((q.tenancyid)=[tenancy].[tenancyid]) AND ([tenancy].[unitid]=[unit].[unitid]);

works perfectly up until those above two lines.

qryArrearsSourceQ is the chunk out of the middle cut into a new query to make my life simpler, as below:

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

 

[DCrake]

Doesn't help that yopu have field names such as Date. This is an Access reserved word and it may be having trouble deciphering this as being a field name not today's date.

David

 

[datAdrenaline]

Are you using linked tables that point to a SQL Server? If so, you can capitalize upon Passthrough queries...

....

Despite this, why do you have a UNION query? In addition, there is really no need to ORDER anything until the last query. Also, all of your queries come from the same source, so why so many queries?

For Example, I beleive the following single query combines QryPaymentStatement & qryPaymentStatementArrears:

I will call this query qryPaymentStatementArrears ---

SELECT paymentid
       , tenancyid
       , [Date]
       , amount as [Due]
       , IIf([Type]="Due",0,[Amount]) as [Received]
       , Note, ChequeName, IIf(IsRent=0,"No","Yes") AS Rent
       , Amount-IIf([Type]="Due",0,[Amount]) AS Arrears
FROM payment
WHERE [type] In ("Due", "Received)

Then proceding, you have the query qryPaymentStatementCumArrears which is really working against you becuase you have PaymentID as part of your GROUP BY clause, and PaymentID, I presume, is Unique in the Payments table, so the grouping doing nothing but overworking your CPU. The real work is in the Sub-Query where you are summing up the arrears by tenancyid and a date range, but you alias that column to a name that already exists in the source listed in the FROM clause, which is generally not a good practice.

....

Gotta run for now ... but I will have more later ...

 

[blinddestiny]

Thanks datAdrenaline,

The script you gave didn't quite get the result I wanted but I have managed to get qryPaymentStatement and qryPaymentStatementArrears down to one query using it as a guide.

Below is the edited script.

Going to see what I can do to get qryPaymentStatementCumArrears and QryArrears working now.

Any more advice on the scripts you can offer would be appreciated

SELECT paymentid
, tenancyid
, [Date]
, IIf([Type]="Received",0,[Amount]) as [Due]
, IIf([Type]="Due",0,[Amount]) as [Received]
, Due-Received AS Arrears
, Note
, ChequeName
, IIf(IsRent=0,"No","Yes") AS Rent
FROM payment
WHERE [type] In ("Due", "Received");