Bijan-
SELECT AcctNo, Year, Max(InvAmt) As Inventory
FROM MyTable
WHERE Balance = 0
GROUP BY AcctNo, Year;
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bijan
Sent: Wednesday, April 10, 2013 5:46 PM
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.
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar