ACCESS FEED: RE: [MS_AccessPros] Access 2010 Report

Rabu, 10 April 2013

RE: [MS_AccessPros] Access 2010 Report

Hello Bijan

This is the tricky part:
> If in the same year, the account appear more than once, it should be count
as 1.

Someone else may be able to suggest an easier way, but I think you will need
a nested subquery to count the number of records in a DISTINCT query for the
given year.

This should work:

Select
Q.Year,
Sum(Q.InvAmt) as InvAmt,
Sum(Q.Balance) as Balance,
(Select Count(*) from
(Select Distinct C.Year, C.AcctNo from [Your Query Name] as C where
C.Balance<>0)
as D where D.Year=Q.Year) as Count
From [Your Query Name] as Q
Group By Q.Year;

Best wishes,
Graham [Access MVP 1996-2013]

> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bijan
> Sent: Thursday, 11 April 2013 03:46
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Access 2010 Report
>
>
> Hi,
>
> I have a query with data something like below.
>
> AcctNo Year InvAmt Balance
> ------ ---- ------ -------
> A0001 2003 200.00 0.00
> A0001 2003 300.00 150.00
> A0001 2004 250.00 0.00
> A0001 2004 250.00 50.00
> A0002 2003 100.00 100.00
> A0002 2003 100.00 100.00
> A0002 2004 100.00 0.00
> ----- ---- ------ ------
>
> In my report I want to make something like below, which the report only
counts the number of account with balance which is not 0. If in the same
year, the account appear more than once, it should be count as 1.
>
> Year InvAmt Balance Count
> ---- ------ ------- -----
> 2003 700.00 350.00 2 <--Two accounts with balances
> 2004 600.00 50.00 1 <--One account with balance
> ---- ------- ------- -----
> TOTAL 1300.00 400.00 3
> ======= ======= =====
>
> Thank you for any tips and trick and advice for the problem.