am attempting to build a 'trial balance' page using DW8 PHP MySql as follows:

Account, begin balance, total debits, total credits, ending balance.
assets_______1000________300______ 200_________1100

begin balance = sum of all debits/credits year to date thru prior month
total debits/total credits = sum of all debits/credits for the current month
ending balance = sum of all debits/credits year to date thru current month.

The transaction table contains the account, transaction date, debit amount, and credit amount.

I have 2 recordsets:Recordset1 calculates the year-to-date values (WHERE transaction date between 'year_start_date' and 'month_end_date')
Recordset2 calculates the month values (WHERE transaction date between 'month_start_date' and 'month_end_date')
Both recordsets use the same URL value (period_id)

I created a table and applied a repeat region behavior. The data from Recordset1 displays correctly, However, the Recordset2 data just repeats the first result continously.

What techniques can I use to resolve this issue? Is there a way to 'link' the 2 recordsets and display in the same repeat region?

RECORDSET1:
SELECT a_glaccount.account, a_glaccount.acct_description,sum(a_gljrnl.debit_amt) as debs, sum(a_gljrnl.credit_amt) as creds
FROM a_glaccount INNER JOIN a_gljrnl on a_glaccount.account=a_gljrnl.account ,a_glperiod
WHERE a_gljrnl.account=a_glaccount.account anD a_glperiod.per_id=var1 and a_gljrnl.tran_date between a_glperiod.start_date and a_glperiod.end_date
GROUP BY a_glaccount.account

RECORDSET2
SELECT a_glaccount.account, a_glaccount.acct_description,sum(a_gljrnl.debit_amt) as ydebs, sum(a_gljrnl.credit_amt) as ycreds
FROM a_glaccount INNER JOIN a_gljrnl on a_glaccount.account=a_gljrnl.account ,a_glperiod,a_control
WHERE a_gljrnl.account=a_glaccount.account anD a_glperiod.per_id=var1 and a_control.ctrl_id=1 and a_gljrnl.tran_date between a_control.year_start and a_glperiod.end_date
GROUP BY a_glaccount.account

Thank you
Dennis Brownlie