Count of items for multiple years

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

Guest

I want to subtotal the number of loans funded in 2003, 2004 and 2005 and
then have a grand total. I figured out the grand total however I have yet
figured out the subs. I read a lot about a running sum but i do not know how
to do a running sum for a date range here is what i tried: =IIf([funded]
Between 1/1/2003 And 12/31/2003,1,0)

it doesnt work. Can someone tell me what i am doing wrong?
 
1. Create a query, with this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. In the Field row, enter:
Year([funded])
Accept Group By in the Total row.

4. Drag the primary key into the grid.
In the Total row under this field, choose Count.

5. Drag the Funded field into the grid.
In the Total row, choose Where
In the Criteria row, enter:
Between #1/1/2003# And #12/31/2005#

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Running sum of multiple years"
 
Thank you so much for your help. But what I really want to do is show on the
actual report the totals for the year 2003, 2004 and 2005 seperated. Is
there a way to do this?

Allen Browne said:
1. Create a query, with this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. In the Field row, enter:
Year([funded])
Accept Group By in the Total row.

4. Drag the primary key into the grid.
In the Total row under this field, choose Count.

5. Drag the Funded field into the grid.
In the Total row, choose Where
In the Criteria row, enter:
Between #1/1/2003# And #12/31/2005#

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Running sum of multiple years"
I want to subtotal the number of loans funded in 2003, 2004 and 2005 and
then have a grand total. I figured out the grand total however I have yet
figured out the subs. I read a lot about a running sum but i do not know
how
to do a running sum for a date range here is what i tried: =IIf([funded]
Between 1/1/2003 And 12/31/2003,1,0)

it doesnt work. Can someone tell me what i am doing wrong?
Click to expand...
Click to expand...
 
Could you use 3 text boxes, each with a DSum() or a DCount() expression?

Example:
=DCount("*", "Table1", "[LoanDate] Between #1/1/2003# And #12/31/2003#")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Running sum of multiple years"
Thank you so much for your help. But what I really want to do is show on
the
actual report the totals for the year 2003, 2004 and 2005 seperated. Is
there a way to do this?

Allen Browne said:
1. Create a query, with this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. In the Field row, enter:
Year([funded])
Accept Group By in the Total row.

4. Drag the primary key into the grid.
In the Total row under this field, choose Count.

5. Drag the Funded field into the grid.
In the Total row, choose Where
In the Criteria row, enter:
Between #1/1/2003# And #12/31/2005#


"Running sum of multiple years"
message
I want to subtotal the number of loans funded in 2003, 2004 and 2005
and
then have a grand total. I figured out the grand total however I have
yet
figured out the subs. I read a lot about a running sum but i do not
know
how
to do a running sum for a date range here is what i tried:
=IIf([funded]
Between 1/1/2003 And 12/31/2003,1,0)

it doesnt work. Can someone tell me what i am doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top