distinct + sum

[Kevin Carter]

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

[Stewart Allen]

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