calculated field

i have a query with the following fields:

year, month, customer, debit euro, credit euro and balance (debit-credit).
the query returns multible entries for each customer. e.g. customer X in
November 2009 has a balance of 500 euro and December 2009 has a balance of
-100 euro.

i want to build a field that returns 500 euro for November and 400 euro for
December. can i achieve that in a query?

i also want to create another field that returns a date. in the above
mentioned example: if year is 2009 and month is 11 i want to add thrre
months, that is the filed should return the date 28/02/2010.

On Wed, 30 Dec 2009 09:34:01 -0800, angie wrote:

i have a query with the following fields:

year, month, customer, debit euro, credit euro and balance (debit-credit).
the query returns multible entries for each customer. e.g. customer X in
November 2009 has a balance of 500 euro and December 2009 has a balance of
-100 euro.

i want to build a field that returns 500 euro for November and 400 euro for
December. can i achieve that in a query?

i also want to create another field that returns a date. in the above
mentioned example: if year is 2009 and month is 11 i want to add thrre
months, that is the filed should return the date 28/02/2010.


The easiest way would be to to use the field 'customer' and 'the field 'balance' in a query,
then use 'totals' (3rd menu), and replace 'group by' in the query by the word 'sum'

If you could mail me ( bbq0014 @ gmail.com ) an example of a table with say 20 records and a query you made I can show you what possibilities there are
Somethime you first make another table and later add this table to your query again.


This adds 3 months to to todays date
Expr1: DateAdd('m',3,(Date()))

This adds 3 months to a date fielld in Table 1, field 'date'
Expr1b: DateAdd('m',3,[Table1]![date])

This adds 3 months to todays date, if the present month is 12
Expr2: IIf(Month(Date())=12,DateAdd('m',3,(Date())),'')

The field (date()) can also be replaced by a date field, I am not sure to what date you want to add three months.

I'm assuming the query returns one row per customer per month per year. To
compute the running balance you sum the credits minus the debits per customer
up to and including the current month. You can base a query on your existing
e.g.

SELECT Q1.customer, Q1.year, Q1.month, Q1.balance,
(SELECT SUM(balance)
FROM YourQuery AS Q2
WHERE Q2.customer = Q1.Customer
AND Q2.year = Q1.year
AND Q2.month = Q1.month)
AS running_balance
FROM YourQuery As Q1
ORDER BY Q1.customer, Q1.year, DESC Q1.month DESC;

or on the original base table(s) e.g. for a table Transactions which includes
a transaction_date column of date/time data type:

SELECT customer,
YEAR(transaction_date) AS transaction_year,
MONTH(transaction_date) AS transaction_month,
SUM(credit) AS total_credit,
SUM(debit) AS total_debit,
SUM(credit_debit) AS monthly_balance,
(SELECT SUM(credit_debit)
FROM Transactions As T2
WHERE T2.customer = T1.Customer
AND YEAR(T2.transaction_date) = YEAR(T1.transaction_date)
AND MONTH(T2.transaction_date) = MONTH(T1.transaction_date)
AS running_balance
FROM Transactions As T1
GROUP BY customer,
YEAR(transaction_date),
MONTH(transaction_date);

To return a date which is the last date of the month n months ahead of a
year/month add the following function to a standard module in the database:

Public Function AddMonths(intYear As Integer, _
intmonth As Integer, _
intMonthsAhead As Integer) As Date

Dim dtmStart As Date

'get start of current month
dtmStart = DateSerial(intYear, intmonth, 1)
' add months ahead plus 1 and subtract 1 day
AddMonths = DateAdd("d", -1, DateAdd("m", intMonthsAhead + 1, dtmStart))

End Function

You can then call it in a query or a computed control in a form or report, e.
g. in the first of the above queries:

AddMonths(Q1.Year,Q1.Month,3) AS three_months_ahead

Ken Sheridan
Stafford, England

angie wrote:
i have a query with the following fields:

year, month, customer, debit euro, credit euro and balance (debit-credit).
the query returns multible entries for each customer. e.g. customer X in
November 2009 has a balance of 500 euro and December 2009 has a balance of
-100 euro.

i want to build a field that returns 500 euro for November and 400 euro for
December. can i achieve that in a query?

i also want to create another field that returns a date. in the above
mentioned example: if year is 2009 and month is 11 i want to add thrre
months, that is the filed should return the date 28/02/2010.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200912/1

you can use the dateadd function to add 3 month to date:
ThreeMonthLater: DateAdd("m",3,[yourdatefield])

The other part I don't understand, do you want a running balance field?

"angie" wrote:

i have a query with the following fields:

year, month, customer, debit euro, credit euro and balance (debit-credit).
the query returns multible entries for each customer. e.g. customer X in
November 2009 has a balance of 500 euro and December 2009 has a balance of
-100 euro.

i want to build a field that returns 500 euro for November and 400 euro for
December. can i achieve that in a query?

i also want to create another field that returns a date. in the above
mentioned example: if year is 2009 and month is 11 i want to add thrre
months, that is the filed should return the date 28/02/2010.