Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #11104 > unrolled thread

help for newbie with totals in report

Back to article view | Back to comp.databases.ms-access

Contents

  help for newbie with totals in report mikep <mike.jeanette.p.mp@gmail.com> - 2015-09-18 22:37 +0930
    Re: help for newbie with totals in report Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-09-18 08:39 -0700
      Re: help for newbie with totals in report mikep <mike.jeanette.p.mp@gmail.com> - 2015-09-20 00:22 +0930
    Re: help for newbie with totals in report CDMAPoster@fortunejames.com - 2015-09-18 14:04 -0700
      Re: help for newbie with totals in report mikep <mike.jeanette.p.mp@gmail.com> - 2015-09-20 00:30 +0930
    Re: help for newbie with totals in report "Joan Wild" <joanwild@xplornet.com> - 2015-09-19 16:24 +0000
      Re: help for newbie with totals in report Larry Linson <larry.linson@verizon.net> - 2015-09-19 15:46 -0500
        Re: help for newbie with totals in report mikep <mike.jeanette.p.mp@gmail.com> - 2015-09-20 22:46 +0930
          Re: help for newbie with totals in report mikep <mike.jeanette.p.mp@gmail.com> - 2015-09-21 01:10 +0930
            Re: help for newbie with totals in report mikep <mike.jeanette.p.mp@gmail.com> - 2015-09-22 00:16 +0930
              Re: help for newbie with totals in report "Joan Wild" <joanwild@xplornet.com> - 2015-09-21 21:11 +0000
            Re: help for newbie with totals in report "Joan Wild" <joanwild@xplornet.com> - 2015-09-21 21:51 +0000
              Re: help for newbie with totals in report mikep <mike.jeanette.p.mp@gmail.com> - 2015-10-02 20:34 +0930
    Re: help for newbie with totals in report "Ron Paii" <None@none.com> - 2015-09-21 15:12 -0500
      Re: help for newbie with totals in report mikep <mike.jeanette.p.mp@gmail.com> - 2015-10-02 20:14 +0930
        Re: help for newbie with totals in report Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-10-02 06:27 -0700
          Re: help for newbie with totals in report M Phillips <mike.jeanette.p@gmail.com> - 2015-10-02 14:39 -0700
        Re: help for newbie with totals in report "Ron Paii" <None@none.com> - 2015-10-02 14:45 -0500

#11104 — help for newbie with totals in report

I have an access2003 database i use in access2007 for recording and 
reporting income and expenditure transactions in 3 accounts and a 
multitude of projects within these accounts for a volunteer organisation 
i'm a member of. It has been adequate for many years now, but i want to 
improve one report, or add another.  Currently i can for these accounts 
for a start and end date range obtained from a date input form, report 
the 'current' (as defined by the input date range) balance and all 
transactions from year start to 'current'. I can also report all 
transactions for the input date range, summarised for each project. 
What i would like to do is in a single report show, for the input date 
range, the date range starting balance, the transactions for the date 
range and the date range end date balance.
my tables are; [accounttypes] with fields accounttypeid, accountypename, 
initialbalance and startdate;
[projects] with fields accounttypeid, projectid, projectname;
[transactions] with fields transid, transref, transdate, 
transdescription, projectid, withdrawalamount, depositamount, 
checkorcashpresented

i can use a query to filter the transactions for the daterange to get 
the transactions between the selected dates. i can use a query to get 
the transactions up to the the daterange start date to get the daterange 
start balance, or a query to get the transactions up to the daterange 
end date to get the end balance. BUT, HOW can i get all these into ONE 
report?

As is certainly obvious i am an Access 'newbie'. I slogged through about 
500 pages of Sams Teach Yourself Access 2000 in 21 Days book over 12 
years ago and it took me rather more than 21 days :) and I never became 
proficient and have now forgotten more than i care to remember.  Any 
help with this, or a pointer to a sample or example would be greatly 
appreciated. Sorry about the length of the post

[toc] | [next] | [standalone]

#11105

On Friday, September 18, 2015 at 9:07:06 AM UTC-4, mikep wrote:
> I have an access2003 database i use in access2007 for recording and 
> reporting income and expenditure transactions in 3 accounts and a 
> multitude of projects within these accounts for a volunteer organisation 
> i'm a member of. It has been adequate for many years now, but i want to 
> improve one report, or add another.  Currently i can for these accounts 
> for a start and end date range obtained from a date input form, report 
> the 'current' (as defined by the input date range) balance and all 
> transactions from year start to 'current'. I can also report all 
> transactions for the input date range, summarised for each project. 
> What i would like to do is in a single report show, for the input date 
> range, the date range starting balance, the transactions for the date 
> range and the date range end date balance.
> my tables are; [accounttypes] with fields accounttypeid, accountypename, 
> initialbalance and startdate;
> [projects] with fields accounttypeid, projectid, projectname;
> [transactions] with fields transid, transref, transdate, 
> transdescription, projectid, withdrawalamount, depositamount, 
> checkorcashpresented
> 
> i can use a query to filter the transactions for the daterange to get 
> the transactions between the selected dates. i can use a query to get 
> the transactions up to the the daterange start date to get the daterange 
> start balance, or a query to get the transactions up to the daterange 
> end date to get the end balance. BUT, HOW can i get all these into ONE 
> report?
> 
> As is certainly obvious i am an Access 'newbie'. I slogged through about 
> 500 pages of Sams Teach Yourself Access 2000 in 21 Days book over 12 
> years ago and it took me rather more than 21 days :) and I never became 
> proficient and have now forgotten more than i care to remember.  Any 
> help with this, or a pointer to a sample or example would be greatly 
> appreciated. Sorry about the length of the post

I don't know if I am following you or not.  I will thus go by the subject line

I typically create a band to break on.  For example, let's say we have a list of customer records, sorted on customer name, and I want the totals for each customer.  I will then create a CustomerName band.  Then have a footer.  If I had a field called SalesAmt totaled, I'd copy the field SalesAmt into the band and make the controlsource "=Sum([SalesAmt])" but without the quotes.

[toc] | [prev] | [next] | [standalone]

#11110

On 19/09/2015 1:09 AM, Patrick Finucane wrote:
> On Friday, September 18, 2015 at 9:07:06 AM UTC-4, mikep wrote:
>> I have an access2003 database i use in access2007 for recording and
>> reporting income and expenditure transactions in 3 accounts and a
>> multitude of projects within these accounts for a volunteer organisation
>> i'm a member of. It has been adequate for many years now, but i want to
>> improve one report, or add another.  Currently i can for these accounts
>> for a start and end date range obtained from a date input form, report
>> the 'current' (as defined by the input date range) balance and all
>> transactions from year start to 'current'. I can also report all
>> transactions for the input date range, summarised for each project.
>> What i would like to do is in a single report show, for the input date
>> range, the date range starting balance, the transactions for the date
>> range and the date range end date balance.
>> my tables are; [accounttypes] with fields accounttypeid, accountypename,
>> initialbalance and startdate;
>> [projects] with fields accounttypeid, projectid, projectname;
>> [transactions] with fields transid, transref, transdate,
>> transdescription, projectid, withdrawalamount, depositamount,
>> checkorcashpresented
>>
>> i can use a query to filter the transactions for the daterange to get
>> the transactions between the selected dates. i can use a query to get
>> the transactions up to the the daterange start date to get the daterange
>> start balance, or a query to get the transactions up to the daterange
>> end date to get the end balance. BUT, HOW can i get all these into ONE
>> report?
>>
>> As is certainly obvious i am an Access 'newbie'. I slogged through about
>> 500 pages of Sams Teach Yourself Access 2000 in 21 Days book over 12
>> years ago and it took me rather more than 21 days :) and I never became
>> proficient and have now forgotten more than i care to remember.  Any
>> help with this, or a pointer to a sample or example would be greatly
>> appreciated. Sorry about the length of the post
>
> I don't know if I am following you or not.  I will thus go by the subject line
>
> I typically create a band to break on.  For example, let's say we have a list of customer records, sorted on customer name, and I want the totals for each customer.  I will then create a CustomerName band.  Then have a footer.  If I had a field called SalesAmt totaled, I'd copy the field SalesAmt into the band and make the controlsource "=Sum([SalesAmt])" but without the quotes.
>
Thanks, I may have been less clear than i hoped.

What I am trying to do is provide a period profit and loss report with 
period opening and closing balances. Presently what i have is a 
year-to-date profit and loss report with yearstartbalance  and closing 
balance.

At present the user enters into a form an <enddate>, which is used in a 
query to produce a report which shows me a $ balance for the <enddate>, 
plus a summarised list of the transactions from <yearstartdate> to the 
inputted <enddate>. It does this by summing the <yearstartbalance> to 
all transactions up to the inputted <enddate>. This gets me my present 
year-to-date profit and loss report. However, getting a period 
<openingbalance> into this report is what is frustratingly eluding me.

I am capable of modifying the date entry form to get user to input both 
period <start> and <enddate>.  However if I filter the transactions to 
get those only between the start and end dates the earlier transactions, 
prior to the <start> date, are gone and so i can not get the sum of 
those earlier transactions to add to the yearstartbalance to give me the 
wanted period <openingbalance>. It seems i do not well understand queries.

Could I perhaps use a subreport to show the period openingbalance and 
would it then be possible to use the openingbalance in that subreport 
and add it to the transactions summary for the period to calculate the 
period closing balance? I don't know if that would work, and is there a 
better way?

I hope this makes clearer what i'm trying to do. Thanks for your 
patience and assistance
mike

[toc] | [prev] | [next] | [standalone]

#11109

On Friday, September 18, 2015 at 9:07:06 AM UTC-4, mikep wrote:
> I have an access2003 database i use in access2007 for recording and 
> reporting income and expenditure transactions in 3 accounts and a 
> multitude of projects within these accounts for a volunteer organisation 
> i'm a member of. It has been adequate for many years now, but i want to 
> improve one report, or add another.  Currently i can for these accounts 
> for a start and end date range obtained from a date input form, report 
> the 'current' (as defined by the input date range) balance and all 
> transactions from year start to 'current'. I can also report all 
> transactions for the input date range, summarised for each project. 
> What i would like to do is in a single report show, for the input date 
> range, the date range starting balance, the transactions for the date 
> range and the date range end date balance.
> my tables are; [accounttypes] with fields accounttypeid, accountypename, 
> initialbalance and startdate;
> [projects] with fields accounttypeid, projectid, projectname;
> [transactions] with fields transid, transref, transdate, 
> transdescription, projectid, withdrawalamount, depositamount, 
> checkorcashpresented
> 
> i can use a query to filter the transactions for the daterange to get 
> the transactions between the selected dates. i can use a query to get 
> the transactions up to the the daterange start date to get the daterange 
> start balance, or a query to get the transactions up to the daterange 
> end date to get the end balance. BUT, HOW can i get all these into ONE 
> report?
> 
> As is certainly obvious i am an Access 'newbie'. I slogged through about 
> 500 pages of Sams Teach Yourself Access 2000 in 21 Days book over 12 
> years ago and it took me rather more than 21 days :) and I never became 
> proficient and have now forgotten more than i care to remember.  Any 
> help with this, or a pointer to a sample or example would be greatly 
> appreciated. Sorry about the length of the post

Here is a function that I use within report groups to obtain results that don't necessarily coincide with the Report's RecordSource: 

Public Function ReturnSQLResult(strSQL As String) As Variant
Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If MyRS.RecordCount <> 0 Then
   MyRS.MoveFirst
   ReturnSQLResult = MyRS(0)
Else
   ReturnSQLResult = Null
End If
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Function

If the RecordSource for the Report is:

SELECT * FROM tblSalesOrders WHERE Status = 'A'

Then you might want a shipped total within your Customer Group by using a ControlSource that uses a different Status than the one in the Report's RecordSource:

=ReturnSQLResult("SELECT Sum(SalesOrderTotal) FROM tblSalesOrders WHERE Customer = " & Chr(34) & txtCustomer.Value & Chr(34) & " AND Status = 'S' & ";")

This technique is also good if you need a different date range than the one in the Report's RecordSource.

The NZ() function can be used along with the function:

=Nz(ReturnSQLResult(strSQL), 0)


Two other handy functions:

Public Function DNoRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset

DNoRecords = True
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
  DNoRecords = False
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function

Public Function DHasRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset

DHasRecords = False
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
  DHasRecords = True
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function

I hope that helps,

James A. Fortune
CDMAPoster@FortuneJames.com

[toc] | [prev] | [next] | [standalone]

#11111

On 19/09/2015 6:34 AM, CDMAPoster@fortunejames.com wrote:
> On Friday, September 18, 2015 at 9:07:06 AM UTC-4, mikep wrote:
>> I have an access2003 database i use in access2007 for recording and
>> reporting income and expenditure transactions in 3 accounts and a
>> multitude of projects within these accounts for a volunteer organisation
>> i'm a member of. It has been adequate for many years now, but i want to
>> improve one report, or add another.  Currently i can for these accounts
>> for a start and end date range obtained from a date input form, report
>> the 'current' (as defined by the input date range) balance and all
>> transactions from year start to 'current'. I can also report all
>> transactions for the input date range, summarised for each project.
>> What i would like to do is in a single report show, for the input date
>> range, the date range starting balance, the transactions for the date
>> range and the date range end date balance.
>> my tables are; [accounttypes] with fields accounttypeid, accountypename,
>> initialbalance and startdate;
>> [projects] with fields accounttypeid, projectid, projectname;
>> [transactions] with fields transid, transref, transdate,
>> transdescription, projectid, withdrawalamount, depositamount,
>> checkorcashpresented
>>
>> i can use a query to filter the transactions for the daterange to get
>> the transactions between the selected dates. i can use a query to get
>> the transactions up to the the daterange start date to get the daterange
>> start balance, or a query to get the transactions up to the daterange
>> end date to get the end balance. BUT, HOW can i get all these into ONE
>> report?
>>
>> As is certainly obvious i am an Access 'newbie'. I slogged through about
>> 500 pages of Sams Teach Yourself Access 2000 in 21 Days book over 12
>> years ago and it took me rather more than 21 days :) and I never became
>> proficient and have now forgotten more than i care to remember.  Any
>> help with this, or a pointer to a sample or example would be greatly
>> appreciated. Sorry about the length of the post
>
> Here is a function that I use within report groups to obtain results that don't necessarily coincide with the Report's RecordSource:
>
> Public Function ReturnSQLResult(strSQL As String) As Variant
> Dim MyDB As Database
> Dim MyRS As Recordset
>
> Set MyDB = CurrentDb
> Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
> If MyRS.RecordCount <> 0 Then
>     MyRS.MoveFirst
>     ReturnSQLResult = MyRS(0)
> Else
>     ReturnSQLResult = Null
> End If
> MyRS.Close
> Set MyRS = Nothing
> Set MyDB = Nothing
> End Function
>
> If the RecordSource for the Report is:
>
> SELECT * FROM tblSalesOrders WHERE Status = 'A'
>
> Then you might want a shipped total within your Customer Group by using a ControlSource that uses a different Status than the one in the Report's RecordSource:
>
> =ReturnSQLResult("SELECT Sum(SalesOrderTotal) FROM tblSalesOrders WHERE Customer = " & Chr(34) & txtCustomer.Value & Chr(34) & " AND Status = 'S' & ";")
>
> This technique is also good if you need a different date range than the one in the Report's RecordSource.
>
> The NZ() function can be used along with the function:
>
> =Nz(ReturnSQLResult(strSQL), 0)
>
>
> Two other handy functions:
>
> Public Function DNoRecords(strSQL As String) As Boolean
> Dim MyDB As Database
> Dim CountRS As Recordset
>
> DNoRecords = True
> Set MyDB = CurrentDb
> Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
> If CountRS.RecordCount > 0 Then
>    DNoRecords = False
> End If
> CountRS.Close
> Set CountRS = Nothing
> Set MyDB = Nothing
> End Function
>
> Public Function DHasRecords(strSQL As String) As Boolean
> Dim MyDB As Database
> Dim CountRS As Recordset
>
> DHasRecords = False
> Set MyDB = CurrentDb
> Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
> If CountRS.RecordCount > 0 Then
>    DHasRecords = True
> End If
> CountRS.Close
> Set CountRS = Nothing
> Set MyDB = Nothing
> End Function
>
> I hope that helps,
>
> James A. Fortune
> CDMAPoster@FortuneJames.com
>
I'm sorry, i do appreciate the effort you went to to try to help me, but 
what you've written is WAY over my head, except maybe for 
"=ReturnSQLResult("SELECT Sum(SalesOrderTotal) FROM tblSalesOrders WHERE 
Customer = " & Chr(34) & txtCustomer.Value & Chr(34) & " AND Status = 
'S' & ";") "
  which i have a vague feeling :% might be a pointer for me in trying to 
write or create an expression in my report to get the sums i want.

[toc] | [prev] | [next] | [standalone]

#11112

mikep wrote:

> i can use a query to filter the transactions for the daterange to get
> the transactions between the selected dates. i can use a query to get
> the transactions up to the the daterange start date to get the
> daterange start balance, or a query to get the transactions up to the
> daterange end date to get the end balance. BUT, HOW can i get all
> these into ONE report?

You could just create three reports (one for each of your queries).
Then put these three reports (each as a sub-report) on a single report.

Or, as it sounds like you are using an aggregate query to get a single
value for the start balance, and also for the end balance.  In that
case you can use Dlookup function to get these values and put them in a
textboxes on the report.

'Query to get the transactions up to the daterange start date to get
the start balance'  - does this query return a single value i.e. are
you using a 'Totals' query?  Post the SQL of your query.

[toc] | [prev] | [next] | [standalone]

#11113

On Sat, 19 Sep 2015 16:24:01 +0000 (UTC), "Joan Wild"
<joanwild@xplornet.com> wrote:

I see Joan has already suggested what immediately came to my mind...
it appears you have three reports that give what you want, so just
embed them as subreports on a report and, lo, you have what you need,
in the easiest possible way.

 Larry

>mikep wrote:
>
>> i can use a query to filter the transactions for the daterange to get
>> the transactions between the selected dates. i can use a query to get
>> the transactions up to the the daterange start date to get the
>> daterange start balance, or a query to get the transactions up to the
>> daterange end date to get the end balance. BUT, HOW can i get all
>> these into ONE report?
>
>You could just create three reports (one for each of your queries).
>Then put these three reports (each as a sub-report) on a single report.
>
>Or, as it sounds like you are using an aggregate query to get a single
>value for the start balance, and also for the end balance.  In that
>case you can use Dlookup function to get these values and put them in a
>textboxes on the report.
>
>'Query to get the transactions up to the daterange start date to get
>the start balance'  - does this query return a single value i.e. are
>you using a 'Totals' query?  Post the SQL of your query.

[toc] | [prev] | [next] | [standalone]

#11114

Thanks for the interest and suggestions.  As  you asked, the SQL for the 
query to get period opening balance is;

SELECT [Account Types].AccountType, [Account Types].InitialBalance AS 
IB, Sum(Transactions.DepositAmount) AS DA, 
Sum(Transactions.WithdrawalAmount) AS WA, [IB]+[DA]-[WA] AS openbal
FROM [Account Types] RIGHT JOIN (Accounts RIGHT JOIN Transactions ON 
Accounts.AccountID = Transactions.AccountID) ON [Account 
Types].AccountTypeID = Accounts.AccountTypeID
WHERE (((Transactions.TransactionDate)<=[Forms]![Report Date 
Range]![Beginning Trans Date] And 
(Transactions.TransactionDate)>=[Account Types]![openingdate]))
GROUP BY [Account Types].AccountType, [Account Types].InitialBalance;

The same query with "[Forms]![Report Date Range]![Beginning Trans Date] 
replaced by "[Forms]![Report Date Range]![Ending Trans Date]"  gets me 
the period closing balance. I can copy and For clarity modify the query 
to give me a closebal instead of the openbal.


The report i've BEEN trying to modify comes from the following query;

SELECT [Account Types].AccountType, [Account Types].openingdate, 
Transactions.TransactionDate, Transactions.TransactionDescription, 
Transactions.WithdrawalAmount, Transactions.DepositAmount, 
CCur([Transactions]![DepositAmount]-[Transactions]![WithdrawalAmount]) 
AS runtot, [Account Types].InitialBalance, [Account Types].openingdate, 
Transactions.TransactionID, Transactions.TransactionNumber
FROM ([Account Types] INNER JOIN Accounts ON [Account 
Types].AccountTypeID = Accounts.AccountTypeID) INNER JOIN Transactions 
ON Accounts.AccountID = Transactions.AccountID
GROUP BY [Account Types].AccountType, [Account Types].openingdate, 
Transactions.TransactionDate, Transactions.TransactionDescription, 
Transactions.WithdrawalAmount, Transactions.DepositAmount, 
CCur([Transactions]![DepositAmount]-[Transactions]![WithdrawalAmount]), 
[Account Types].InitialBalance, [Account Types].openingdate, 
Transactions.TransactionID, Transactions.TransactionNumber
HAVING (((Transactions.TransactionDate)>=[Forms]![Report Date 
Range]![Beginning Trans Date] And 
(Transactions.TransactionDate)<=[Forms]![Report Date Range]![Ending 
Trans Date]))
ORDER BY [Account Types].AccountType, Transactions.TransactionDate;

The date input defaults to year start, so this report can sum all 
transactions and the InitialBalance (which is year start) to the period 
end date giving me a period end balance and showing the transactions 
between the year start date and period end date with running total. 
Changing the period start date away from the default will get me a 
report which will show transactions for the period of interest and an 
endingbalance, but the period opening balance remains the year start 
balance [initialbalance].
This report is so close to what i wanted that you can see why i've been 
trying to find a way to get an actual period beginning figure into it.

Perhaps, as you suggest, I could add a subreport for the actual period 
opening balance as per the top sql listing  would be the simplest way 
for me to go? I'll give it a try and see if i have any problems.

thanks for the help.
mike





On 20/09/2015 6:16 AM, Larry Linson wrote:
> On Sat, 19 Sep 2015 16:24:01 +0000 (UTC), "Joan Wild"
> <joanwild@xplornet.com> wrote:
>
> I see Joan has already suggested what immediately came to my mind...
> it appears you have three reports that give what you want, so just
> embed them as subreports on a report and, lo, you have what you need,
> in the easiest possible way.
>
>   Larry
>
>> mikep wrote:
>>
>>> i can use a query to filter the transactions for the daterange to get
>>> the transactions between the selected dates. i can use a query to get
>>> the transactions up to the the daterange start date to get the
>>> daterange start balance, or a query to get the transactions up to the
>>> daterange end date to get the end balance. BUT, HOW can i get all
>>> these into ONE report?
>>
>> You could just create three reports (one for each of your queries).
>> Then put these three reports (each as a sub-report) on a single report.
>>
>> Or, as it sounds like you are using an aggregate query to get a single
>> value for the start balance, and also for the end balance.  In that
>> case you can use Dlookup function to get these values and put them in a
>> textboxes on the report.
>>
>> 'Query to get the transactions up to the daterange start date to get
>> the start balance'  - does this query return a single value i.e. are
>> you using a 'Totals' query?  Post the SQL of your query.

[toc] | [prev] | [next] | [standalone]

#11115

Well, this is not going as i'd hoped. With a subreport for 
openingbalances in the [accounttype] header area i get all three 
accounttype opening balances at the beginning of each of the 3 
accounttypes sections.  I wanted the opening balance for each 
accounttype showing only for its own section.

here is the code for the query i'm using;
SELECT [Account Types].AccountType, [Account Types].InitialBalance AS 
IB, Sum(Transactions.DepositAmount) AS DA, 
Sum(Transactions.WithdrawalAmount) AS WA, [IB]+[DA]-[WA] AS openbal, 
[Account Types].openingdate
FROM [Account Types] RIGHT JOIN (Accounts RIGHT JOIN Transactions ON 
Accounts.AccountID = Transactions.AccountID) ON [Account 
Types].AccountTypeID = Accounts.AccountTypeID
WHERE (((Transactions.TransactionDate)<=[Forms]![Report Date 
Range]![Beginning Trans Date] And 
(Transactions.TransactionDate)>=[Account Types]![openingdate]))
GROUP BY [Account Types].AccountType, [Account Types].InitialBalance, 
[Account Types].openingdate;
Any ideas?? My brain has gone to mush - too late!
Thanks,
mike




On 20/09/2015 10:46 PM, mikep wrote:
> Thanks for the interest and suggestions.  As  you asked, the SQL for the
> query to get period opening balance is;
>
> SELECT [Account Types].AccountType, [Account Types].InitialBalance AS
> IB, Sum(Transactions.DepositAmount) AS DA,
> Sum(Transactions.WithdrawalAmount) AS WA, [IB]+[DA]-[WA] AS openbal
> FROM [Account Types] RIGHT JOIN (Accounts RIGHT JOIN Transactions ON
> Accounts.AccountID = Transactions.AccountID) ON [Account
> Types].AccountTypeID = Accounts.AccountTypeID
> WHERE (((Transactions.TransactionDate)<=[Forms]![Report Date
> Range]![Beginning Trans Date] And
> (Transactions.TransactionDate)>=[Account Types]![openingdate]))
> GROUP BY [Account Types].AccountType, [Account Types].InitialBalance;
>
> The same query with "[Forms]![Report Date Range]![Beginning Trans Date]
> replaced by "[Forms]![Report Date Range]![Ending Trans Date]"  gets me
> the period closing balance. I can copy and For clarity modify the query
> to give me a closebal instead of the openbal.
>
>
> The report i've BEEN trying to modify comes from the following query;
>
> SELECT [Account Types].AccountType, [Account Types].openingdate,
> Transactions.TransactionDate, Transactions.TransactionDescription,
> Transactions.WithdrawalAmount, Transactions.DepositAmount,
> CCur([Transactions]![DepositAmount]-[Transactions]![WithdrawalAmount])
> AS runtot, [Account Types].InitialBalance, [Account Types].openingdate,
> Transactions.TransactionID, Transactions.TransactionNumber
> FROM ([Account Types] INNER JOIN Accounts ON [Account
> Types].AccountTypeID = Accounts.AccountTypeID) INNER JOIN Transactions
> ON Accounts.AccountID = Transactions.AccountID
> GROUP BY [Account Types].AccountType, [Account Types].openingdate,
> Transactions.TransactionDate, Transactions.TransactionDescription,
> Transactions.WithdrawalAmount, Transactions.DepositAmount,
> CCur([Transactions]![DepositAmount]-[Transactions]![WithdrawalAmount]),
> [Account Types].InitialBalance, [Account Types].openingdate,
> Transactions.TransactionID, Transactions.TransactionNumber
> HAVING (((Transactions.TransactionDate)>=[Forms]![Report Date
> Range]![Beginning Trans Date] And
> (Transactions.TransactionDate)<=[Forms]![Report Date Range]![Ending
> Trans Date]))
> ORDER BY [Account Types].AccountType, Transactions.TransactionDate;
>
> The date input defaults to year start, so this report can sum all
> transactions and the InitialBalance (which is year start) to the period
> end date giving me a period end balance and showing the transactions
> between the year start date and period end date with running total.
> Changing the period start date away from the default will get me a
> report which will show transactions for the period of interest and an
> endingbalance, but the period opening balance remains the year start
> balance [initialbalance].
> This report is so close to what i wanted that you can see why i've been
> trying to find a way to get an actual period beginning figure into it.
>
> Perhaps, as you suggest, I could add a subreport for the actual period
> opening balance as per the top sql listing  would be the simplest way
> for me to go? I'll give it a try and see if i have any problems.
>
> thanks for the help.
> mike
>
>
>
>
>
> On 20/09/2015 6:16 AM, Larry Linson wrote:
>> On Sat, 19 Sep 2015 16:24:01 +0000 (UTC), "Joan Wild"
>> <joanwild@xplornet.com> wrote:
>>
>> I see Joan has already suggested what immediately came to my mind...
>> it appears you have three reports that give what you want, so just
>> embed them as subreports on a report and, lo, you have what you need,
>> in the easiest possible way.
>>
>>   Larry
>>
>>> mikep wrote:
>>>
>>>> i can use a query to filter the transactions for the daterange to get
>>>> the transactions between the selected dates. i can use a query to get
>>>> the transactions up to the the daterange start date to get the
>>>> daterange start balance, or a query to get the transactions up to the
>>>> daterange end date to get the end balance. BUT, HOW can i get all
>>>> these into ONE report?
>>>
>>> You could just create three reports (one for each of your queries).
>>> Then put these three reports (each as a sub-report) on a single report.
>>>
>>> Or, as it sounds like you are using an aggregate query to get a single
>>> value for the start balance, and also for the end balance.  In that
>>> case you can use Dlookup function to get these values and put them in a
>>> textboxes on the report.
>>>
>>> 'Query to get the transactions up to the daterange start date to get
>>> the start balance'  - does this query return a single value i.e. are
>>> you using a 'Totals' query?  Post the SQL of your query.
>

[toc] | [prev] | [next] | [standalone]

#11116

Hi again. Having failed with subreports i have tried Joan's suggestion 
to use Dlookup. I've never used this before. However, I placed
<=DLookUp("[openbal]","[qryOpeningBbalance]")>
in the sectionheader area and this got me a report with the correct 
opening balance; my detail section gets me the transactions for the 
period i specify, and the sectionfooter area has an expression that sums 
the openingbalance and transactions which gets me the period closing 
balance. Great! But the second section (for the next accounttype) shows 
the openingbalance for the first sectionheader. Ditto the third 
sectionheader Not surprising since i have no criteria defined to specify 
otherwise. Is it possible to add criteria to get the relevant 
openingbalance  for the specific section?
thanks,
mike


On 21/09/2015 1:10 AM, mikep wrote:
> Well, this is not going as i'd hoped. With a subreport for
> openingbalances in the [accounttype] header area i get all three
> accounttype opening balances at the beginning of each of the 3
> accounttypes sections.  I wanted the opening balance for each
> accounttype showing only for its own section.
>
> here is the code for the query i'm using;
> SELECT [Account Types].AccountType, [Account Types].InitialBalance AS
> IB, Sum(Transactions.DepositAmount) AS DA,
> Sum(Transactions.WithdrawalAmount) AS WA, [IB]+[DA]-[WA] AS openbal,
> [Account Types].openingdate
> FROM [Account Types] RIGHT JOIN (Accounts RIGHT JOIN Transactions ON
> Accounts.AccountID = Transactions.AccountID) ON [Account
> Types].AccountTypeID = Accounts.AccountTypeID
> WHERE (((Transactions.TransactionDate)<=[Forms]![Report Date
> Range]![Beginning Trans Date] And
> (Transactions.TransactionDate)>=[Account Types]![openingdate]))
> GROUP BY [Account Types].AccountType, [Account Types].InitialBalance,
> [Account Types].openingdate;
> Any ideas?? My brain has gone to mush - too late!
> Thanks,
> mike
>
>
>
>
> On 20/09/2015 10:46 PM, mikep wrote:
>> Thanks for the interest and suggestions.  As  you asked, the SQL for the
>> query to get period opening balance is;
>>
>> SELECT [Account Types].AccountType, [Account Types].InitialBalance AS
>> IB, Sum(Transactions.DepositAmount) AS DA,
>> Sum(Transactions.WithdrawalAmount) AS WA, [IB]+[DA]-[WA] AS openbal
>> FROM [Account Types] RIGHT JOIN (Accounts RIGHT JOIN Transactions ON
>> Accounts.AccountID = Transactions.AccountID) ON [Account
>> Types].AccountTypeID = Accounts.AccountTypeID
>> WHERE (((Transactions.TransactionDate)<=[Forms]![Report Date
>> Range]![Beginning Trans Date] And
>> (Transactions.TransactionDate)>=[Account Types]![openingdate]))
>> GROUP BY [Account Types].AccountType, [Account Types].InitialBalance;
>>
>> The same query with "[Forms]![Report Date Range]![Beginning Trans Date]
>> replaced by "[Forms]![Report Date Range]![Ending Trans Date]"  gets me
>> the period closing balance. I can copy and For clarity modify the query
>> to give me a closebal instead of the openbal.
>>
>>
>> The report i've BEEN trying to modify comes from the following query;
>>
>> SELECT [Account Types].AccountType, [Account Types].openingdate,
>> Transactions.TransactionDate, Transactions.TransactionDescription,
>> Transactions.WithdrawalAmount, Transactions.DepositAmount,
>> CCur([Transactions]![DepositAmount]-[Transactions]![WithdrawalAmount])
>> AS runtot, [Account Types].InitialBalance, [Account Types].openingdate,
>> Transactions.TransactionID, Transactions.TransactionNumber
>> FROM ([Account Types] INNER JOIN Accounts ON [Account
>> Types].AccountTypeID = Accounts.AccountTypeID) INNER JOIN Transactions
>> ON Accounts.AccountID = Transactions.AccountID
>> GROUP BY [Account Types].AccountType, [Account Types].openingdate,
>> Transactions.TransactionDate, Transactions.TransactionDescription,
>> Transactions.WithdrawalAmount, Transactions.DepositAmount,
>> CCur([Transactions]![DepositAmount]-[Transactions]![WithdrawalAmount]),
>> [Account Types].InitialBalance, [Account Types].openingdate,
>> Transactions.TransactionID, Transactions.TransactionNumber
>> HAVING (((Transactions.TransactionDate)>=[Forms]![Report Date
>> Range]![Beginning Trans Date] And
>> (Transactions.TransactionDate)<=[Forms]![Report Date Range]![Ending
>> Trans Date]))
>> ORDER BY [Account Types].AccountType, Transactions.TransactionDate;
>>
>> The date input defaults to year start, so this report can sum all
>> transactions and the InitialBalance (which is year start) to the period
>> end date giving me a period end balance and showing the transactions
>> between the year start date and period end date with running total.
>> Changing the period start date away from the default will get me a
>> report which will show transactions for the period of interest and an
>> endingbalance, but the period opening balance remains the year start
>> balance [initialbalance].
>> This report is so close to what i wanted that you can see why i've been
>> trying to find a way to get an actual period beginning figure into it.
>>
>> Perhaps, as you suggest, I could add a subreport for the actual period
>> opening balance as per the top sql listing  would be the simplest way
>> for me to go? I'll give it a try and see if i have any problems.
>>
>> thanks for the help.
>> mike
>>
>>
>>
>>
>>
>> On 20/09/2015 6:16 AM, Larry Linson wrote:
>>> On Sat, 19 Sep 2015 16:24:01 +0000 (UTC), "Joan Wild"
>>> <joanwild@xplornet.com> wrote:
>>>
>>> I see Joan has already suggested what immediately came to my mind...
>>> it appears you have three reports that give what you want, so just
>>> embed them as subreports on a report and, lo, you have what you need,
>>> in the easiest possible way.
>>>
>>>   Larry
>>>
>>>> mikep wrote:
>>>>
>>>>> i can use a query to filter the transactions for the daterange to get
>>>>> the transactions between the selected dates. i can use a query to get
>>>>> the transactions up to the the daterange start date to get the
>>>>> daterange start balance, or a query to get the transactions up to the
>>>>> daterange end date to get the end balance. BUT, HOW can i get all
>>>>> these into ONE report?
>>>>
>>>> You could just create three reports (one for each of your queries).
>>>> Then put these three reports (each as a sub-report) on a single report.
>>>>
>>>> Or, as it sounds like you are using an aggregate query to get a single
>>>> value for the start balance, and also for the end balance.  In that
>>>> case you can use Dlookup function to get these values and put them in a
>>>> textboxes on the report.
>>>>
>>>> 'Query to get the transactions up to the daterange start date to get
>>>> the start balance'  - does this query return a single value i.e. are
>>>> you using a 'Totals' query?  Post the SQL of your query.
>>
>

[toc] | [prev] | [next] | [standalone]

#11119

=DLookup("openbal","qryOpeningBbalance", "[Account Types] = 1"

If account types are text then
=DLookup("openbal","qryOpeningBbalance", "[Account Types] = 'something'"

I just copied your example; is the query really named
qryOpeningBbalance?



mikep wrote:

> Hi again. Having failed with subreports i have tried Joan's
> suggestion to use Dlookup. I've never used this before. However, I
> placed <=DLookUp("[openbal]","[qryOpeningBbalance]")> in the
> sectionheader area and this got me a report with the correct opening
> balance; my detail section gets me the transactions for the period i
> specify, and the sectionfooter area has an expression that sums the
> openingbalance and transactions which gets me the period closing
> balance. Great! But the second section (for the next accounttype)
> shows the openingbalance for the first sectionheader. Ditto the third
> sectionheader Not surprising since i have no criteria defined to
> specify otherwise. Is it possible to add criteria to get the relevant
> openingbalance  for the specific section?  thanks, mike
> 

[toc] | [prev] | [next] | [standalone]

#11121

You could make use the the sorting and grouping capabilities in reports:
Main report record source is your qryOpeningBalances - group on
AccountID and put the opening balance in the group header.

Use a subreport for the transaction list (master/child link on
AccountID)
and put the closing balance in the group footer.

mikep wrote:

> Well, this is not going as i'd hoped. With a subreport for
> openingbalances in the [accounttype] header area i get all three
> accounttype opening balances at the beginning of each of the 3
> accounttypes sections.  I wanted the opening balance for each
> accounttype showing only for its own section.
> 
> here is the code for the query i'm using;
> SELECT [Account Types].AccountType, [Account Types].InitialBalance AS
> IB, Sum(Transactions.DepositAmount) AS DA,
> Sum(Transactions.WithdrawalAmount) AS WA, [IB]+[DA]-[WA] AS openbal,
> [Account Types].openingdate FROM [Account Types] RIGHT JOIN (Accounts
> RIGHT JOIN Transactions ON Accounts.AccountID =
> Transactions.AccountID) ON [Account Types].AccountTypeID =
> Accounts.AccountTypeID WHERE
> (((Transactions.TransactionDate)<=[Forms]![Report Date
> Range]![Beginning Trans Date] And
> (Transactions.TransactionDate)>=[Account Types]![openingdate])) GROUP
> BY [Account Types].AccountType, [Account Types].InitialBalance,
> [Account Types].openingdate; Any ideas?? My brain has gone to mush -
> too late!  Thanks, mike

[toc] | [prev] | [next] | [standalone]

#11153

Thank you Joan, that is what i think i did in the end. However, to get 
the opening balance in the header i needed(?) to use dlookup to get the 
opening balance from another query. In order for the opening balance 
query to update for each accounttype i needed to use the following;
<=DLookUp("[openbal]","[qryOpeningBbalance]","  [AccountTypeID]  =" & 
[AccountTypeID])>
This works, so i suppose it must be generally correct. What do you think 
of it?
As i replied to a later post the next (last) problem i have with my 
report is that if there are no transactions for a particular accounttype 
for the selected period then i get no report for that accounttype. It 
would be good if the report always showed the opening and closing 
balances even if they're the same. I have looked at the code for the 
report and am unable to see anything which which would be stopping those 
balances showing. My knowledge of sql is near zero, i can not write it - 
but i can  sort of read it albeit with some guesswork. I was once a 
little familiar with fortran, basic and qbasic as well as a little heard 
of language called mimic, in the days when computers were electron 
tube!! So, is there a 'switch' i can set to make it always report?
Thanks again for the suggestions and help.

On 22/09/2015 7:21 AM, Joan Wild wrote:
> You could make use the the sorting and grouping capabilities in reports:
> Main report record source is your qryOpeningBalances - group on
> AccountID and put the opening balance in the group header.
>
> Use a subreport for the transaction list (master/child link on
> AccountID)
> and put the closing balance in the group footer.
>
> mikep wrote:
>
>> Well, this is not going as i'd hoped. With a subreport for
>> openingbalances in the [accounttype] header area i get all three
>> accounttype opening balances at the beginning of each of the 3
>> accounttypes sections.  I wanted the opening balance for each
>> accounttype showing only for its own section.
>>
>> here is the code for the query i'm using;
>> SELECT [Account Types].AccountType, [Account Types].InitialBalance AS
>> IB, Sum(Transactions.DepositAmount) AS DA,
>> Sum(Transactions.WithdrawalAmount) AS WA, [IB]+[DA]-[WA] AS openbal,
>> [Account Types].openingdate FROM [Account Types] RIGHT JOIN (Accounts
>> RIGHT JOIN Transactions ON Accounts.AccountID =
>> Transactions.AccountID) ON [Account Types].AccountTypeID =
>> Accounts.AccountTypeID WHERE
>> (((Transactions.TransactionDate)<=[Forms]![Report Date
>> Range]![Beginning Trans Date] And
>> (Transactions.TransactionDate)>=[Account Types]![openingdate])) GROUP
>> BY [Account Types].AccountType, [Account Types].InitialBalance,
>> [Account Types].openingdate; Any ideas?? My brain has gone to mush -
>> too late!  Thanks, mike

[toc] | [prev] | [next] | [standalone]

#11118

"mikep" <mike.jeanette.p.mp@gmail.com> wrote in message 
news:55fc0c76$0$1580$c3e8da3$5496439d@news.astraweb.com...
> I have an access2003 database i use in access2007 for recording and 
> reporting income and expenditure transactions in 3 accounts and a 
> multitude of projects within these accounts for a volunteer organisation 
> i'm a member of. It has been adequate for many years now, but i want to 
> improve one report, or add another.  Currently i can for these accounts 
> for a start and end date range obtained from a date input form, report the 
> 'current' (as defined by the input date range) balance and all 
> transactions from year start to 'current'. I can also report all 
> transactions for the input date range, summarised for each project. What i 
> would like to do is in a single report show, for the input date range, the 
> date range starting balance, the transactions for the date range and the 
> date range end date balance.
> my tables are; [accounttypes] with fields accounttypeid, accountypename, 
> initialbalance and startdate;
> [projects] with fields accounttypeid, projectid, projectname;
> [transactions] with fields transid, transref, transdate, transdescription, 
> projectid, withdrawalamount, depositamount, checkorcashpresented
>
> i can use a query to filter the transactions for the daterange to get the 
> transactions between the selected dates. i can use a query to get the 
> transactions up to the the daterange start date to get the daterange start 
> balance, or a query to get the transactions up to the daterange end date 
> to get the end balance. BUT, HOW can i get all these into ONE report?
>
> As is certainly obvious i am an Access 'newbie'. I slogged through about 
> 500 pages of Sams Teach Yourself Access 2000 in 21 Days book over 12 years 
> ago and it took me rather more than 21 days :) and I never became 
> proficient and have now forgotten more than i care to remember.  Any help 
> with this, or a pointer to a sample or example would be greatly 
> appreciated. Sorry about the length of the post
>

The following union query will supply you with the sum of all transactions 
before the start date with a transaction date set to the day before the 
start date to be used for the beginning balance. The end part is all 
transactions between start and end date. Sum the 2 to get the ending 
balance.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT transactions.projectid, Sum(transactions.withdrawalamount) AS 
SumOfwithdrawalamount, Sum(transactions.depositamount) AS 
SumOfdepositamount, DateAdd("d",-1,[Start Date]) AS TransDate
FROM transactions
WHERE transactions.transdate<[Start Date]
GROUP BY transactions.projectid, DateAdd("d",-1,[Start Date]);

UNION ALL SELECT transactions.projectid, Sum(transactions.withdrawalamount) 
AS SumOfwithdrawalamount, Sum(transactions.depositamount) AS 
SumOfdepositamount, transactions.transdate
FROM transactions
GROUP BY transactions.projectid, transactions.transdate
HAVING transactions.transdate Between [Start Date] And [End Date]; 

[toc] | [prev] | [next] | [standalone]

#11152

On 22/09/2015 5:42 AM, Ron Paii wrote:
>
>
> "mikep" <mike.jeanette.p.mp@gmail.com> wrote in message
> news:55fc0c76$0$1580$c3e8da3$5496439d@news.astraweb.com...
>> I have an access2003 database i use in access2007 for recording and
>> reporting income and expenditure transactions in 3 accounts and a
>> multitude of projects within these accounts for a volunteer
>> organisation i'm a member of. It has been adequate for many years now,
>> but i want to improve one report, or add another.  Currently i can for
>> these accounts for a start and end date range obtained from a date
>> input form, report the 'current' (as defined by the input date range)
>> balance and all transactions from year start to 'current'. I can also
>> report all transactions for the input date range, summarised for each
>> project. What i would like to do is in a single report show, for the
>> input date range, the date range starting balance, the transactions
>> for the date range and the date range end date balance.
>> my tables are; [accounttypes] with fields accounttypeid,
>> accountypename, initialbalance and startdate;
>> [projects] with fields accounttypeid, projectid, projectname;
>> [transactions] with fields transid, transref, transdate,
>> transdescription, projectid, withdrawalamount, depositamount,
>> checkorcashpresented
>>
>> i can use a query to filter the transactions for the daterange to get
>> the transactions between the selected dates. i can use a query to get
>> the transactions up to the the daterange start date to get the
>> daterange start balance, or a query to get the transactions up to the
>> daterange end date to get the end balance. BUT, HOW can i get all
>> these into ONE report?
>>
>> As is certainly obvious i am an Access 'newbie'. I slogged through
>> about 500 pages of Sams Teach Yourself Access 2000 in 21 Days book
>> over 12 years ago and it took me rather more than 21 days :) and I
>> never became proficient and have now forgotten more than i care to
>> remember.  Any help with this, or a pointer to a sample or example
>> would be greatly appreciated. Sorry about the length of the post
>>
>
> The following union query will supply you with the sum of all
> transactions before the start date with a transaction date set to the
> day before the start date to be used for the beginning balance. The end
> part is all transactions between start and end date. Sum the 2 to get
> the ending balance.
>
> PARAMETERS [Start Date] DateTime, [End Date] DateTime;
> SELECT transactions.projectid, Sum(transactions.withdrawalamount) AS
> SumOfwithdrawalamount, Sum(transactions.depositamount) AS
> SumOfdepositamount, DateAdd("d",-1,[Start Date]) AS TransDate
> FROM transactions
> WHERE transactions.transdate<[Start Date]
> GROUP BY transactions.projectid, DateAdd("d",-1,[Start Date]);
>
> UNION ALL SELECT transactions.projectid,
> Sum(transactions.withdrawalamount) AS SumOfwithdrawalamount,
> Sum(transactions.depositamount) AS SumOfdepositamount,
> transactions.transdate
> FROM transactions
> GROUP BY transactions.projectid, transactions.transdate
> HAVING transactions.transdate Between [Start Date] And [End Date];
Thank you Ron, I'll perhaps give that a go. Sorry about the slow 
response, the news server i use went down and it took them days to get 
it back by which time i hade left home for a week and was unable to 
access it anyway. I did persevere and from Joan's suggestion re dlookup, 
and some reading and a certain amount of trial and error, i managed to 
get almost the report i want  with dlookup, if not perhaps as elegantly 
as someone with more knowledge would have done. "Almost" you say? Well, 
i have the opening balance, the transaction list for the designated 
period, and the closing balance, all just as i wanted - except... if 
there are no transactions in the period for one of the accounts then 
there's no report for that account. Oh well, keeps my brain going i suppose.

[toc] | [prev] | [next] | [standalone]

#11154

On Friday, October 2, 2015 at 6:45:00 AM UTC-4, mikep wrote:
> On 22/09/2015 5:42 AM, Ron Paii wrote:
> >
> >
> > "mikep" <mike.jeanette.p.mp@gmail.com> wrote in message
> > news:55fc0c76$0$1580$c3e8da3$5496439d@news.astraweb.com...
> >> I have an access2003 database i use in access2007 for recording and
> >> reporting income and expenditure transactions in 3 accounts and a
> >> multitude of projects within these accounts for a volunteer
> >> organisation i'm a member of. It has been adequate for many years now,
> >> but i want to improve one report, or add another.  Currently i can for
> >> these accounts for a start and end date range obtained from a date
> >> input form, report the 'current' (as defined by the input date range)
> >> balance and all transactions from year start to 'current'. I can also
> >> report all transactions for the input date range, summarised for each
> >> project. What i would like to do is in a single report show, for the
> >> input date range, the date range starting balance, the transactions
> >> for the date range and the date range end date balance.
> >> my tables are; [accounttypes] with fields accounttypeid,
> >> accountypename, initialbalance and startdate;
> >> [projects] with fields accounttypeid, projectid, projectname;
> >> [transactions] with fields transid, transref, transdate,
> >> transdescription, projectid, withdrawalamount, depositamount,
> >> checkorcashpresented
> >>
> >> i can use a query to filter the transactions for the daterange to get
> >> the transactions between the selected dates. i can use a query to get
> >> the transactions up to the the daterange start date to get the
> >> daterange start balance, or a query to get the transactions up to the
> >> daterange end date to get the end balance. BUT, HOW can i get all
> >> these into ONE report?
> >>
> >> As is certainly obvious i am an Access 'newbie'. I slogged through
> >> about 500 pages of Sams Teach Yourself Access 2000 in 21 Days book
> >> over 12 years ago and it took me rather more than 21 days :) and I
> >> never became proficient and have now forgotten more than i care to
> >> remember.  Any help with this, or a pointer to a sample or example
> >> would be greatly appreciated. Sorry about the length of the post
> >>
> >
> > The following union query will supply you with the sum of all
> > transactions before the start date with a transaction date set to the
> > day before the start date to be used for the beginning balance. The end
> > part is all transactions between start and end date. Sum the 2 to get
> > the ending balance.
> >
> > PARAMETERS [Start Date] DateTime, [End Date] DateTime;
> > SELECT transactions.projectid, Sum(transactions.withdrawalamount) AS
> > SumOfwithdrawalamount, Sum(transactions.depositamount) AS
> > SumOfdepositamount, DateAdd("d",-1,[Start Date]) AS TransDate
> > FROM transactions
> > WHERE transactions.transdate<[Start Date]
> > GROUP BY transactions.projectid, DateAdd("d",-1,[Start Date]);
> >
> > UNION ALL SELECT transactions.projectid,
> > Sum(transactions.withdrawalamount) AS SumOfwithdrawalamount,
> > Sum(transactions.depositamount) AS SumOfdepositamount,
> > transactions.transdate
> > FROM transactions
> > GROUP BY transactions.projectid, transactions.transdate
> > HAVING transactions.transdate Between [Start Date] And [End Date];
> Thank you Ron, I'll perhaps give that a go. Sorry about the slow 
> response, the news server i use went down and it took them days to get 
> it back by which time i hade left home for a week and was unable to 
> access it anyway. I did persevere and from Joan's suggestion re dlookup, 
> and some reading and a certain amount of trial and error, i managed to 
> get almost the report i want  with dlookup, if not perhaps as elegantly 
> as someone with more knowledge would have done. "Almost" you say? Well, 
> i have the opening balance, the transaction list for the designated 
> period, and the closing balance, all just as i wanted - except... if 
> there are no transactions in the period for one of the accounts then 
> there's no report for that account. Oh well, keeps my brain going i suppose.

If you have Google, you can use google news groups.  This newsgroup is a Google newsgroup.

[toc] | [prev] | [next] | [standalone]

#11156

On Friday, 2 October 2015 23:27:16 UTC+10, Patrick Finucane  wrote:
> On Friday, October 2, 2015 at 6:45:00 AM UTC-4, mikep wrote:
> > On 22/09/2015 5:42 AM, Ron Paii wrote:
> > >
> > >
> > > "mikep" <mike.jeanette.p.mp@gmail.com> wrote in message
> > > news:55fc0c76$0$1580$c3e8da3$5496439d@news.astraweb.com...
> > >> I have an access2003 database i use in access2007 for recording and
> > >> reporting income and expenditure transactions in 3 accounts and a
> > >> multitude of projects within these accounts for a volunteer
> > >> organisation i'm a member of. It has been adequate for many years now,
> > >> but i want to improve one report, or add another.  Currently i can for
> > >> these accounts for a start and end date range obtained from a date
> > >> input form, report the 'current' (as defined by the input date range)
> > >> balance and all transactions from year start to 'current'. I can also
> > >> report all transactions for the input date range, summarised for each
> > >> project. What i would like to do is in a single report show, for the
> > >> input date range, the date range starting balance, the transactions
> > >> for the date range and the date range end date balance.
> > >> my tables are; [accounttypes] with fields accounttypeid,
> > >> accountypename, initialbalance and startdate;
> > >> [projects] with fields accounttypeid, projectid, projectname;
> > >> [transactions] with fields transid, transref, transdate,
> > >> transdescription, projectid, withdrawalamount, depositamount,
> > >> checkorcashpresented
> > >>
> > >> i can use a query to filter the transactions for the daterange to get
> > >> the transactions between the selected dates. i can use a query to get
> > >> the transactions up to the the daterange start date to get the
> > >> daterange start balance, or a query to get the transactions up to the
> > >> daterange end date to get the end balance. BUT, HOW can i get all
> > >> these into ONE report?
> > >>
> > >> As is certainly obvious i am an Access 'newbie'. I slogged through
> > >> about 500 pages of Sams Teach Yourself Access 2000 in 21 Days book
> > >> over 12 years ago and it took me rather more than 21 days :) and I
> > >> never became proficient and have now forgotten more than i care to
> > >> remember.  Any help with this, or a pointer to a sample or example
> > >> would be greatly appreciated. Sorry about the length of the post
> > >>
> > >
> > > The following union query will supply you with the sum of all
> > > transactions before the start date with a transaction date set to the
> > > day before the start date to be used for the beginning balance. The end
> > > part is all transactions between start and end date. Sum the 2 to get
> > > the ending balance.
> > >
> > > PARAMETERS [Start Date] DateTime, [End Date] DateTime;
> > > SELECT transactions.projectid, Sum(transactions.withdrawalamount) AS
> > > SumOfwithdrawalamount, Sum(transactions.depositamount) AS
> > > SumOfdepositamount, DateAdd("d",-1,[Start Date]) AS TransDate
> > > FROM transactions
> > > WHERE transactions.transdate<[Start Date]
> > > GROUP BY transactions.projectid, DateAdd("d",-1,[Start Date]);
> > >
> > > UNION ALL SELECT transactions.projectid,
> > > Sum(transactions.withdrawalamount) AS SumOfwithdrawalamount,
> > > Sum(transactions.depositamount) AS SumOfdepositamount,
> > > transactions.transdate
> > > FROM transactions
> > > GROUP BY transactions.projectid, transactions.transdate
> > > HAVING transactions.transdate Between [Start Date] And [End Date];
> > Thank you Ron, I'll perhaps give that a go. Sorry about the slow 
> > response, the news server i use went down and it took them days to get 
> > it back by which time i hade left home for a week and was unable to 
> > access it anyway. I did persevere and from Joan's suggestion re dlookup, 
> > and some reading and a certain amount of trial and error, i managed to 
> > get almost the report i want  with dlookup, if not perhaps as elegantly 
> > as someone with more knowledge would have done. "Almost" you say? Well, 
> > i have the opening balance, the transaction list for the designated 
> > period, and the closing balance, all just as i wanted - except... if 
> > there are no transactions in the period for one of the accounts then 
> > there's no report for that account. Oh well, keeps my brain going i suppose.
> 
> If you have Google, you can use google news groups.  This newsgroup is a Google newsgroup.

Thank you for the tip, i was unaware of that. 

[toc] | [prev] | [next] | [standalone]

#11155

"mikep" <mike.jeanette.p.mp@gmail.com> wrote in message 
news:560e6023$0$11096$c3e8da3@news.astraweb.com...
> On 22/09/2015 5:42 AM, Ron Paii wrote:
>>
>>
>> "mikep" <mike.jeanette.p.mp@gmail.com> wrote in message
>> news:55fc0c76$0$1580$c3e8da3$5496439d@news.astraweb.com...
>>> I have an access2003 database i use in access2007 for recording and
>>> reporting income and expenditure transactions in 3 accounts and a
>>> multitude of projects within these accounts for a volunteer
>>> organisation i'm a member of. It has been adequate for many years now,
>>> but i want to improve one report, or add another.  Currently i can for
>>> these accounts for a start and end date range obtained from a date
>>> input form, report the 'current' (as defined by the input date range)
>>> balance and all transactions from year start to 'current'. I can also
>>> report all transactions for the input date range, summarised for each
>>> project. What i would like to do is in a single report show, for the
>>> input date range, the date range starting balance, the transactions
>>> for the date range and the date range end date balance.
>>> my tables are; [accounttypes] with fields accounttypeid,
>>> accountypename, initialbalance and startdate;
>>> [projects] with fields accounttypeid, projectid, projectname;
>>> [transactions] with fields transid, transref, transdate,
>>> transdescription, projectid, withdrawalamount, depositamount,
>>> checkorcashpresented
>>>


Add a 3rd union to the query to return all projectids and 0 amount

UNION ALL SELECT DISTINCT transactions.projectid,
0 AS SumOfwithdrawalamount,
0 AS SumOfdepositamount,
[Start Date] as TransDate
FROM transactions;
>>> i can use a query to filter the transactions for the daterange to get
>>> the transactions between the selected dates. i can use a query to get
>>> the transactions up to the the daterange start date to get the
>>> daterange start balance, or a query to get the transactions up to the
>>> daterange end date to get the end balance. BUT, HOW can i get all
>>> these into ONE report?
>>>
>>> As is certainly obvious i am an Access 'newbie'. I slogged through
>>> about 500 pages of Sams Teach Yourself Access 2000 in 21 Days book
>>> over 12 years ago and it took me rather more than 21 days :) and I
>>> never became proficient and have now forgotten more than i care to
>>> remember.  Any help with this, or a pointer to a sample or example
>>> would be greatly appreciated. Sorry about the length of the post
>>>
>>
>> The following union query will supply you with the sum of all
>> transactions before the start date with a transaction date set to the
>> day before the start date to be used for the beginning balance. The end
>> part is all transactions between start and end date. Sum the 2 to get
>> the ending balance.
>>
>> PARAMETERS [Start Date] DateTime, [End Date] DateTime;
>> SELECT transactions.projectid, Sum(transactions.withdrawalamount) AS
>> SumOfwithdrawalamount, Sum(transactions.depositamount) AS
>> SumOfdepositamount, DateAdd("d",-1,[Start Date]) AS TransDate
>> FROM transactions
>> WHERE transactions.transdate<[Start Date]
>> GROUP BY transactions.projectid, DateAdd("d",-1,[Start Date]);
>>
>> UNION ALL SELECT transactions.projectid,
>> Sum(transactions.withdrawalamount) AS SumOfwithdrawalamount,
>> Sum(transactions.depositamount) AS SumOfdepositamount,
>> transactions.transdate
>> FROM transactions
>> GROUP BY transactions.projectid, transactions.transdate
>> HAVING transactions.transdate Between [Start Date] And [End Date];
> Thank you Ron, I'll perhaps give that a go. Sorry about the slow response, 
> the news server i use went down and it took them days to get it back by 
> which time i hade left home for a week and was unable to access it anyway. 
> I did persevere and from Joan's suggestion re dlookup, and some reading 
> and a certain amount of trial and error, i managed to get almost the 
> report i want  with dlookup, if not perhaps as elegantly as someone with 
> more knowledge would have done. "Almost" you say? Well, i have the opening 
> balance, the transaction list for the designated period, and the closing 
> balance, all just as i wanted - except... if there are no transactions in 
> the period for one of the accounts then there's no report for that 
> account. Oh well, keeps my brain going i suppose. 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web