By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,692 Members | 1,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,692 IT Pros & Developers. It's quick & easy.

distinct + sum

P: n/a
hi
i have a table with several fields

Field1(area), Field2(Quantity), Field3(cost), field4(date)

area Quantity cost date
office 10 50 6/4/04
gym 50 90 9/4/04
office 100 2000 9/4//04
what i need to do is return the total for an area over a date range

ie
area Quantity cost
office 110 2050
gym 50 90

the user will select the date range from a combobox

i have the following code

strSQL = "SELECT DISTINCT ([DataTable].[Area]),
Sum([DataTable].Quantity) AS [SumOfTotal Quantity],
Sum([DataTable].TotalPrice) AS [SumOfTotal Price] " & _
"FROM datatable " & _
"WHERE DataTable.Dates Between" &
SQLDate(Me.cboStartDate.Value) & "And" & SQLDate(cboEndDate.Value)

this code works fine until i include the date range

How do i adapt this code to return the distinct area along with the
total quantity and total cost for the area
thanks in advance
kevin
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi Kevin

Same as my reply to your last post, use GROUP BY DataTable.Area only

Stewart

"Kevin Carter" <kc******@ford.com> wrote in message
news:2a**************************@posting.google.c om...
hi
i have a table with several fields

Field1(area), Field2(Quantity), Field3(cost), field4(date)

area Quantity cost date
office 10 50 6/4/04
gym 50 90 9/4/04
office 100 2000 9/4//04
what i need to do is return the total for an area over a date range

ie
area Quantity cost
office 110 2050
gym 50 90

the user will select the date range from a combobox

i have the following code

strSQL = "SELECT DISTINCT ([DataTable].[Area]),
Sum([DataTable].Quantity) AS [SumOfTotal Quantity],
Sum([DataTable].TotalPrice) AS [SumOfTotal Price] " & _
"FROM datatable " & _
"WHERE DataTable.Dates Between" &
SQLDate(Me.cboStartDate.Value) & "And" & SQLDate(cboEndDate.Value)

this code works fine until i include the date range

How do i adapt this code to return the distinct area along with the
total quantity and total cost for the area
thanks in advance
kevin

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.