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
|
|||
|
|||
Year to date values
I need help on calculating the amount of money the agencies for the company
has collected for the month specified, year to date, and the same month compared to last year. I have two tables Agency and Data. Data Cash collected would be called Amount. Each day of the year cash is collected so it is called Date (daily, meaning the values are usually 1/1/06,1/2/06, etc). Agency AgencyNumber represents the number assigned to the agency. AgencyName is the name of the agency. So basically I need the query to be able to calculate the month's total cash collected for that agency, the amount of cash collected for the year from the beginning to the specified month for that particular agency, and cash collected for the month a year ago for that agency. I want to be able to turn it into a report looking like this: # Agency Mar 2005 YTD March 2004 X XXXXX 12345 5765985 13445 The dialog box for it won't be a problem for me its just the coding that's giving me the trouble. Any help would be greatly appreciated. Thanks! |
#2
|
|||
|
|||
Year to date values
There isn't a field in Data that ties to an Agency so this is impossible. To
create some columns in a query with the Data table, you can use expressions like: YTD: Abs(Year([Date]) = Year(Date()) * [Amount] March2004: Abs(Format([Date],"yyyymm") = Format(#3/1/2005#,"yyyymm")) * [Amount] -- Duane Hookom MS Access MVP "van_slanzar" wrote in message ... I need help on calculating the amount of money the agencies for the company has collected for the month specified, year to date, and the same month compared to last year. I have two tables Agency and Data. Data Cash collected would be called Amount. Each day of the year cash is collected so it is called Date (daily, meaning the values are usually 1/1/06,1/2/06, etc). Agency AgencyNumber represents the number assigned to the agency. AgencyName is the name of the agency. So basically I need the query to be able to calculate the month's total cash collected for that agency, the amount of cash collected for the year from the beginning to the specified month for that particular agency, and cash collected for the month a year ago for that agency. I want to be able to turn it into a report looking like this: # Agency Mar 2005 YTD March 2004 X XXXXX 12345 5765985 13445 The dialog box for it won't be a problem for me its just the coding that's giving me the trouble. Any help would be greatly appreciated. Thanks! |
#3
|
|||
|
|||
Year to date values
You can join in the Agency table with the AgencyNumber field. To allow the
user to specify a month, replace the hard coded #3/1/2005# with a reference to a control on a form like: AnyMonth: Abs(Format([Date],"yyyymm") = Format(Forms!frmA!txtDate,"yyyymm")) * [Amount] -- Duane Hookom MS Access MVP From Van_slanzar My apologies for not specifying. The Data table contains AgencyNumber to link the two tables together. And also what I want the user to be able to specify which month he/she wants to look at. Thanks in advance again! "Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message ... There isn't a field in Data that ties to an Agency so this is impossible. To create some columns in a query with the Data table, you can use expressions like: YTD: Abs(Year([Date]) = Year(Date()) * [Amount] March2004: Abs(Format([Date],"yyyymm") = Format(#3/1/2005#,"yyyymm")) * [Amount] -- Duane Hookom MS Access MVP "van_slanzar" wrote in message ... I need help on calculating the amount of money the agencies for the company has collected for the month specified, year to date, and the same month compared to last year. I have two tables Agency and Data. Data Cash collected would be called Amount. Each day of the year cash is collected so it is called Date (daily, meaning the values are usually 1/1/06,1/2/06, etc). Agency AgencyNumber represents the number assigned to the agency. AgencyName is the name of the agency. So basically I need the query to be able to calculate the month's total cash collected for that agency, the amount of cash collected for the year from the beginning to the specified month for that particular agency, and cash collected for the month a year ago for that agency. I want to be able to turn it into a report looking like this: # Agency Mar 2005 YTD March 2004 X XXXXX 12345 5765985 13445 The dialog box for it won't be a problem for me its just the coding that's giving me the trouble. Any help would be greatly appreciated. Thanks! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
year date only, number or date format? | PAULinLAOS | Database Design | 6 | April 8th, 2006 03:19 AM |
Adding info to query | mjj4golf | Running & Setting Up Queries | 20 | January 9th, 2006 02:29 AM |
Data Entry Form with Previous Year's Information | Tandy | Using Forms | 16 | July 7th, 2005 09:31 PM |
Date Range and Average F/X Rate | David | General Discussion | 0 | June 23rd, 2005 02:26 PM |
Does date fall between two ranges? | MR | Worksheet Functions | 4 | January 14th, 2004 04:08 PM |