Finding Totals from two diff date ranges

[Dave K.]

I have a form that includes two sub forms that are link together. subformA
and SubformB. SubformA is linked to the Parent form FormA and SubformB is
linked to SubformA. When I select from the list of items in subformA the the
proper list shows in subformB ok. the problem is that the detail list in
subformB has a date fiield that reflects dates from 2009 and 2008 and a
Dollar figure for each date called the amount field. I want to show a total
in the parent form formA to reflect the totals for 2008 and 2009 in two
different fields on formA. Example

SubformA
MeterNumber SupplierName
4446900 Pitney Bowes

SubFormB
Date Amount
07/01/2009 3000
04/22/2009 3000
01/22/2009 3000
11/07/2008 3000
08/22/2008 3000
06/11/2008 42
05/27/2008 3000
03/18/2008 3000

I to show in the main form
Total 2009: $9,000.00
Total 2008: $12,042.00

Right now it works but it shows the sum for both
Total 2009: 21,042.00

I don't have a field set for 2008 yet.

 

[John W. Vinson]

I have a form that includes two sub forms that are link together. subformA
and SubformB. SubformA is linked to the Parent form FormA and SubformB is
linked to SubformA. When I select from the list of items in subformA the the
proper list shows in subformB ok. the problem is that the detail list in
subformB has a date fiield that reflects dates from 2009 and 2008 and a
Dollar figure for each date called the amount field. I want to show a total
in the parent form formA to reflect the totals for 2008 and 2009 in two
different fields on formA. Example

SubformA
MeterNumber SupplierName
4446900 Pitney Bowes

SubFormB
Date Amount
07/01/2009 3000
04/22/2009 3000
01/22/2009 3000
11/07/2008 3000
08/22/2008 3000
06/11/2008 42
05/27/2008 3000
03/18/2008 3000

I to show in the main form
Total 2009: $9,000.00
Total 2008: $12,042.00

Right now it works but it shows the sum for both
Total 2009: 21,042.00

I don't have a field set for 2008 yet.

When it gets to be 2013, will you want fields for 2008, 2009, 2010, 2011, 2012
and 2013? I.e. will you keep changing the design of the form? Ouch!

I'd suggest not getting the data from the Subform; the data isn't IN the
subform, it's in a table. Instead, you could use the DSum() function as the
control source of a textbox on the main form. If you want to get the sum for
the current year in one textbox and the previous year in a second textbox, you
could use a Control Source like

=DSum("[Amount]", "[tablename]", "[Date] >= DateSerial(Year(Date()), 1, 1) AND
[Date] < DateSerial(Year(Date()) + 1, 1, 1)")

for the current year, and

=DSum("[Amount]", "[tablename]", "[Date] >= DateSerial(Year(Date())-1, 1, 1)
AND [Date] < DateSerial(Year(Date()), 1, 1)")

for the previous.

 

[Dave K.]

John, That works grea, but it gives me the total for all the items in the
table (meter) I need the same thing but for each meter I select in the
sub-form.

This is what I have =DSum("[amount]","[TblPostage]","[date]>= DateSerial
(2009, 1, 1) and [date]<Dateserial(2009+1,1,1)")

Dave


--
Dave K.

I have a form that includes two sub forms that are link together. subformA
and SubformB. SubformA is linked to the Parent form FormA and SubformB is
linked to SubformA. When I select from the list of items in subformA the the
proper list shows in subformB ok. the problem is that the detail list in
subformB has a date fiield that reflects dates from 2009 and 2008 and a
Dollar figure for each date called the amount field. I want to show a total
in the parent form formA to reflect the totals for 2008 and 2009 in two
different fields on formA. Example

SubformA
MeterNumber SupplierName
4446900 Pitney Bowes

SubFormB
Date Amount
07/01/2009 3000
04/22/2009 3000
01/22/2009 3000
11/07/2008 3000
08/22/2008 3000
06/11/2008 42
05/27/2008 3000
03/18/2008 3000

I to show in the main form
Total 2009: $9,000.00
Total 2008: $12,042.00

Right now it works but it shows the sum for both
Total 2009: 21,042.00

I don't have a field set for 2008 yet.

When it gets to be 2013, will you want fields for 2008, 2009, 2010, 2011, 2012
and 2013? I.e. will you keep changing the design of the form? Ouch!

I'd suggest not getting the data from the Subform; the data isn't IN the
subform, it's in a table. Instead, you could use the DSum() function as the
control source of a textbox on the main form. If you want to get the sum for
the current year in one textbox and the previous year in a second textbox, you
could use a Control Source like

=DSum("[Amount]", "[tablename]", "[Date] >= DateSerial(Year(Date()), 1, 1) AND
[Date] < DateSerial(Year(Date()) + 1, 1, 1)")

for the current year, and

=DSum("[Amount]", "[tablename]", "[Date] >= DateSerial(Year(Date())-1, 1, 1)
AND [Date] < DateSerial(Year(Date()), 1, 1)")

for the previous.

 

[John W. Vinson]

John, That works grea, but it gives me the total for all the items in the
table (meter) I need the same thing but for each meter I select in the
sub-form.

This is what I have =DSum("[amount]","[TblPostage]","[date]>= DateSerial
(2009, 1, 1) and [date]<Dateserial(2009+1,1,1)")

Dave

Add a criterion for the meter to the third argument to DSum. Since I have no
idea where or how the meter information is stored I can't be specific, but the
third argument is a valid SQL WHERE clause without the WHERE; try creating a
query that retrieves the items you want, view it in SQL view, and use that as
the basis for your DSum.

Do note that your query with 2009 literally in it will give 2009 data next
year, and year after, and forever... unlike using Year(Date()), which is what
I suggested. If that's the result you want, fine.

 

[Dave K.]

John I thing I have it right now, but the text box with this control :

=DSum("[amount]","[TblPostage]","[meter Number]=
[MasterSubform].Form![TblEquipment Subform].Form![MeterNumber] And [date]>=
DateSerial (2009, 1, 1) and [date]<Dateserial(2009+1,1,1) ")

will not update when I select a new record in the subform.
The Meter information is stored in one table, the office where each meter is
located is in another table and the postage for each meter is recorded in
another table. I have a main form that shows the office location and when
each office location changes the subform reflects the meters that go to that
office and in a second subform it shows the detail for each meter when it is
selected in the meter sub form.

hope this helps.
--
Dave K.

John, That works grea, but it gives me the total for all the items in the
table (meter) I need the same thing but for each meter I select in the
sub-form.

This is what I have =DSum("[amount]","[TblPostage]","[date]>= DateSerial
(2009, 1, 1) and [date]<Dateserial(2009+1,1,1)")

Dave

Add a criterion for the meter to the third argument to DSum. Since I have no
idea where or how the meter information is stored I can't be specific, but the
third argument is a valid SQL WHERE clause without the WHERE; try creating a
query that retrieves the items you want, view it in SQL view, and use that as
the basis for your DSum.

Do note that your query with 2009 literally in it will give 2009 data next
year, and year after, and forever... unlike using Year(Date()), which is what
I suggested. If that's the result you want, fine.

 

[John W. Vinson]

=DSum("[amount]","[TblPostage]","[meter Number]=
[MasterSubform].Form![TblEquipment Subform].Form![MeterNumber] And [date]>=
DateSerial (2009, 1, 1) and [date]<Dateserial(2009+1,1,1) ")

will not update when I select a new record in the subform.

Requery the textbox in the Current event of the subform.