Query

[Guest]

I have a table with the following fields:
Date
MemberID
Grouping
Income
Expenditure

I am trying to create a query that that will show a running balance. i.e. a
calculate balance after each (income/expenditure) entry. plase expalin in
plain english.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


It's easier to explain in SQL:

SELECT [Date], MemberID, [Grouping], Income,
(SELECT Sum(Income) FROM table_name WHERE [Date] <= T.[Date] AND
MemberID = T.MemberID AND [Grouping] = T.[Grouping]) As IncomeRunSum,
Expenditure,
(SELECT Sum(Expenditure) FROM table_name WHERE [Date] <= T.[Date] AND
MemberID = T.MemberID AND [Grouping] = T.[Grouping]) As
ExpenditureRunSum
FROM table_name As T
WHERE < criteria >

The subqueries in the SELECT statement get the running sums of Income &
Expenditure for each Date, MemberID and Grouping.

Substitute the name of the table for "table_name."

BTW, Date is a bad name for a column because it is a VBA keyword and it
doesn't identify the type of date: birth date, termination date, sales
date, receipt date, etc; which is it?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9wDVoechKqOuFEgEQJZdwCgvVTszG76HRx45oxeNBDUkqcL9VYAn0oh
2/p7hkw2NyFOwpK4F7VuhpUj
=Izvm
-----END PGP SIGNATURE-----