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

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

Re: help for newbie with totals in report

Show all headers | View raw

"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. 

Back to comp.databases.ms-access | Previous | Next — Previous 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