DEBITING BUDGET SPEND IN TABLE1 FROM BUDGET ALLOCATED IN TABLE 2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables. The first one contains details of a budget holders money
allocation for a given period, and the other tracks their spend on products
over that period. How can I generate a query to calculate the total running
spend for each user from the budget spend table that will be written into the
users record in the budget allocation table.

My aim is to show details of budget allocation, total spend to date and
remaining budget for each user in an Order form / report.

Can anyone please advise me on how to do this or suggest another way of
doing it? Any help would be greatly appreciated.

Thanks in advance.

Regards Peter
 
Hi,



SELECT a.UserID, a.DateTimeStamp, SUM(b.Amount)
FROM myTable As a INNER JOIN myTable As b
ON a.UserID=b.UserID AND a.DateTImeStamp >= b.DateTimeStamp
GROUP BY a.UserID, a.DateTimeStamp




Enter Amount credit as negative and debit as positive (or the reverse) and
use a dateTimeStamp (with the time in addition to the date, if required),
for each transaction.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top