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

Groups > comp.databases.ms-access > #11118

Re: help for newbie with totals in report

Show all headers | View raw

"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]; 

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web