Year to date values

[Guest]

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!

 

[Duane Hookom]

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]

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!

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

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!