If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
calculated field
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. |
#3
|
|||
|
|||
calculated field
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 |
#4
|
|||
|
|||
calculated field
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. |
Thread Tools | |
Display Modes | |
|
|