Conditional Sum in a Form

I

igorin

Hello,

Hopefully you can help me.

I have the following database:

(Note: date format is dd/mm/yy)

Date Bulldozer Crane Excavator
01/01/09 5.3 7.8 9.4
02/01/09 2.3 12.2 8.1
03/01/09 1.9 23 2.7
04/01/09 3.8 19.4 13.5
05/01/09 9 12.1 6.7
06/01/09 10.1 7.4 15.2

In a Form there would be text boxes for each equipment field (Bulldozer,
Crane, Excavator). And there should also be a text box for a start date and
another for an end date. Then, once the input dates (start & end date) are
given, the text boxes for each equipment field should show the sum of the
values between the search dates.

For example, if I input in the form these dates:

Start date: 02/01/09
End date: 04/01/09

Then in the text boxes for the equipment fields should appear the values for
the corresponding sum:

Bulldozer: 8 (this is the sum 2.3 + 1.9 + 3.8)
Crane: 54.6 (this is the sum 12.2 + 23 + 19.4)
Excavator: 24.3 (this is the sum 8.1 + 2.7 + 13.5)

I have two questions:

1) How should the query be done?
2) Once the query is done I would just need to make the form as usual, is
this correct?

Thanks for the help!
 
K

Ken Snell MVP

The query would look like this:

PARAMETERS
Forms!YourFormName!StartDateControl Date,
Forms!YourFormName!EndDateControl Date;
SELECT Sum([Bulldozer]) As SumBulldozer,
Sum([Crane]) As SumCrane,
Sum([Excavator]) As SumExcavator
FROM YourTableName
WHERE [Date] Between Forms!YourFormName!StartDateControl
And Forms!YourFormName!EndDateControl;


Build a form that has the two textboxes for start date and end date,
textboxes to be bound to the three fields in the above query, and a command
button.

Use the command button to requery the form after you type values into the
two textboxes for start date and end date:

Private Sub NameOfCommandButton_Click()
Me.Requery
End Sub
 
D

Damon Heron

Simplest way is to use the dsum for each equipment item:
Private Sub cmdCalculate_Click()

Me.txtBulldozer = nz(DSum("Bulldozer", "Yourtable", "[Datefield] >= #" &
txtStart & "# And [datefield] <= #" & txtEnd & "#"),0)
' repeat for each item

End Sub
I used a command button on the form, but you could use the afterupdate
event.

HTH
Damon
 
D

Damon Heron

This also works:
Me.txtBulldozer = DSum("Bulldozer", "Yourtable", "[Date1] between [txtStart]
And [txtEnd]")

Damon

Damon Heron said:
Simplest way is to use the dsum for each equipment item:
Private Sub cmdCalculate_Click()

Me.txtBulldozer = nz(DSum("Bulldozer", "Yourtable", "[Datefield] >= #" &
txtStart & "# And [datefield] <= #" & txtEnd & "#"),0)
' repeat for each item

End Sub
I used a command button on the form, but you could use the afterupdate
event.

HTH
Damon


igorin said:
Hello,

Hopefully you can help me.

I have the following database:

(Note: date format is dd/mm/yy)

Date Bulldozer Crane Excavator
01/01/09 5.3 7.8 9.4
02/01/09 2.3 12.2 8.1
03/01/09 1.9 23 2.7
04/01/09 3.8 19.4 13.5
05/01/09 9 12.1 6.7
06/01/09 10.1 7.4 15.2

In a Form there would be text boxes for each equipment field (Bulldozer,
Crane, Excavator). And there should also be a text box for a start date
and
another for an end date. Then, once the input dates (start & end date)
are
given, the text boxes for each equipment field should show the sum of the
values between the search dates.

For example, if I input in the form these dates:

Start date: 02/01/09
End date: 04/01/09

Then in the text boxes for the equipment fields should appear the values
for
the corresponding sum:

Bulldozer: 8 (this is the sum 2.3 + 1.9 + 3.8)
Crane: 54.6 (this is the sum 12.2 + 23 + 19.4)
Excavator: 24.3 (this is the sum 8.1 + 2.7 + 13.5)

I have two questions:

1) How should the query be done?
2) Once the query is done I would just need to make the form as usual, is
this correct?

Thanks for the help!
Click to expand...
Click to expand...
 
I

igorin

Thank you, Ken! I will try it over the weekend.

If I have any questions I'll let you know.

:)

Ken Snell MVP said:
The query would look like this:

PARAMETERS
Forms!YourFormName!StartDateControl Date,
Forms!YourFormName!EndDateControl Date;
SELECT Sum([Bulldozer]) As SumBulldozer,
Sum([Crane]) As SumCrane,
Sum([Excavator]) As SumExcavator
FROM YourTableName
WHERE [Date] Between Forms!YourFormName!StartDateControl
And Forms!YourFormName!EndDateControl;


Build a form that has the two textboxes for start date and end date,
textboxes to be bound to the three fields in the above query, and a command
button.

Use the command button to requery the form after you type values into the
two textboxes for start date and end date:

Private Sub NameOfCommandButton_Click()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



igorin said:
Hello,

Hopefully you can help me.

I have the following database:

(Note: date format is dd/mm/yy)

Date Bulldozer Crane Excavator
01/01/09 5.3 7.8 9.4
02/01/09 2.3 12.2 8.1
03/01/09 1.9 23 2.7
04/01/09 3.8 19.4 13.5
05/01/09 9 12.1 6.7
06/01/09 10.1 7.4 15.2

In a Form there would be text boxes for each equipment field (Bulldozer,
Crane, Excavator). And there should also be a text box for a start date
and
another for an end date. Then, once the input dates (start & end date) are
given, the text boxes for each equipment field should show the sum of the
values between the search dates.

For example, if I input in the form these dates:

Start date: 02/01/09
End date: 04/01/09

Then in the text boxes for the equipment fields should appear the values
for
the corresponding sum:

Bulldozer: 8 (this is the sum 2.3 + 1.9 + 3.8)
Crane: 54.6 (this is the sum 12.2 + 23 + 19.4)
Excavator: 24.3 (this is the sum 8.1 + 2.7 + 13.5)

I have two questions:

1) How should the query be done?
2) Once the query is done I would just need to make the form as usual, is
this correct?

Thanks for the help!
Click to expand...
Click to expand...
 
I

igorin

Thank you very much, Damon!

I will try this over the weekend and if I have any questions I'll let you
know.

Thanks again for the help!


Damon Heron said:
This also works:
Me.txtBulldozer = DSum("Bulldozer", "Yourtable", "[Date1] between [txtStart]
And [txtEnd]")

Damon

Damon Heron said:
Simplest way is to use the dsum for each equipment item:
Private Sub cmdCalculate_Click()

Me.txtBulldozer = nz(DSum("Bulldozer", "Yourtable", "[Datefield] >= #" &
txtStart & "# And [datefield] <= #" & txtEnd & "#"),0)
' repeat for each item

End Sub
I used a command button on the form, but you could use the afterupdate
event.

HTH
Damon


igorin said:
Hello,

Hopefully you can help me.

I have the following database:

(Note: date format is dd/mm/yy)

Date Bulldozer Crane Excavator
01/01/09 5.3 7.8 9.4
02/01/09 2.3 12.2 8.1
03/01/09 1.9 23 2.7
04/01/09 3.8 19.4 13.5
05/01/09 9 12.1 6.7
06/01/09 10.1 7.4 15.2

In a Form there would be text boxes for each equipment field (Bulldozer,
Crane, Excavator). And there should also be a text box for a start date
and
another for an end date. Then, once the input dates (start & end date)
are
given, the text boxes for each equipment field should show the sum of the
values between the search dates.

For example, if I input in the form these dates:

Start date: 02/01/09
End date: 04/01/09

Then in the text boxes for the equipment fields should appear the values
for
the corresponding sum:

Bulldozer: 8 (this is the sum 2.3 + 1.9 + 3.8)
Crane: 54.6 (this is the sum 12.2 + 23 + 19.4)
Excavator: 24.3 (this is the sum 8.1 + 2.7 + 13.5)

I have two questions:

1) How should the query be done?
2) Once the query is done I would just need to make the form as usual, is
this correct?

Thanks for the help!
Click to expand...
Click to expand...
Click to expand...
 
I

igorin

Ken,

Thank you very much, this worked.

I have been trying to get your post listed as the answer but for some reason
it only allows me to say that it was helpful.

This forum has been acting funny for the past weeks.

--
igor


Ken Snell MVP said:
The query would look like this:

PARAMETERS
Forms!YourFormName!StartDateControl Date,
Forms!YourFormName!EndDateControl Date;
SELECT Sum([Bulldozer]) As SumBulldozer,
Sum([Crane]) As SumCrane,
Sum([Excavator]) As SumExcavator
FROM YourTableName
WHERE [Date] Between Forms!YourFormName!StartDateControl
And Forms!YourFormName!EndDateControl;


Build a form that has the two textboxes for start date and end date,
textboxes to be bound to the three fields in the above query, and a command
button.

Use the command button to requery the form after you type values into the
two textboxes for start date and end date:

Private Sub NameOfCommandButton_Click()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



igorin said:
Hello,

Hopefully you can help me.

I have the following database:

(Note: date format is dd/mm/yy)

Date Bulldozer Crane Excavator
01/01/09 5.3 7.8 9.4
02/01/09 2.3 12.2 8.1
03/01/09 1.9 23 2.7
04/01/09 3.8 19.4 13.5
05/01/09 9 12.1 6.7
06/01/09 10.1 7.4 15.2

In a Form there would be text boxes for each equipment field (Bulldozer,
Crane, Excavator). And there should also be a text box for a start date
and
another for an end date. Then, once the input dates (start & end date) are
given, the text boxes for each equipment field should show the sum of the
values between the search dates.

For example, if I input in the form these dates:

Start date: 02/01/09
End date: 04/01/09

Then in the text boxes for the equipment fields should appear the values
for
the corresponding sum:

Bulldozer: 8 (this is the sum 2.3 + 1.9 + 3.8)
Crane: 54.6 (this is the sum 12.2 + 23 + 19.4)
Excavator: 24.3 (this is the sum 8.1 + 2.7 + 13.5)

I have two questions:

1) How should the query be done?
2) Once the query is done I would just need to make the form as usual, is
this correct?

Thanks for the help!
Click to expand...
Click to expand...
 
I

igorin

Damon,

Thank you very much. This also worked fine.

For some reason I can't mark your reply as the answer. I don't know what is
happening with the forum.

Thanks again.

--
igor


Damon Heron said:
This also works:
Me.txtBulldozer = DSum("Bulldozer", "Yourtable", "[Date1] between [txtStart]
And [txtEnd]")

Damon

Damon Heron said:
Simplest way is to use the dsum for each equipment item:
Private Sub cmdCalculate_Click()

Me.txtBulldozer = nz(DSum("Bulldozer", "Yourtable", "[Datefield] >= #" &
txtStart & "# And [datefield] <= #" & txtEnd & "#"),0)
' repeat for each item

End Sub
I used a command button on the form, but you could use the afterupdate
event.

HTH
Damon


igorin said:
Hello,

Hopefully you can help me.

I have the following database:

(Note: date format is dd/mm/yy)

Date Bulldozer Crane Excavator
01/01/09 5.3 7.8 9.4
02/01/09 2.3 12.2 8.1
03/01/09 1.9 23 2.7
04/01/09 3.8 19.4 13.5
05/01/09 9 12.1 6.7
06/01/09 10.1 7.4 15.2

In a Form there would be text boxes for each equipment field (Bulldozer,
Crane, Excavator). And there should also be a text box for a start date
and
another for an end date. Then, once the input dates (start & end date)
are
given, the text boxes for each equipment field should show the sum of the
values between the search dates.

For example, if I input in the form these dates:

Start date: 02/01/09
End date: 04/01/09

Then in the text boxes for the equipment fields should appear the values
for
the corresponding sum:

Bulldozer: 8 (this is the sum 2.3 + 1.9 + 3.8)
Crane: 54.6 (this is the sum 12.2 + 23 + 19.4)
Excavator: 24.3 (this is the sum 8.1 + 2.7 + 13.5)

I have two questions:

1) How should the query be done?
2) Once the query is done I would just need to make the form as usual, is
this correct?

Thanks for the help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

In a Form: Show sums for all unique values 4
Populate a table 2
Summing groups in a report 1
conditional running sum 7
Conditional sum equals 0 4
Summing Groups in Reports 2
Calculations with times 6
Display "week 1" based on date in another field 1

Top