| re: distict and sum
"Kevin Carter" <kcarte10@ford.com> wrote in message
news:2a17a2d5.0407112207.71e092e3@posting.google.c om...[color=blue]
> "Stewart Allen" <sagasu@NOT.wave.THIS.co.nz> wrote in message[/color]
news:<ccr3su$1c4$1@news.wave.co.nz>...[color=blue][color=green]
> > Hi Kevin,
> >
> > There's no need for distinctrow for the type of query you're using but[/color][/color]
to[color=blue][color=green]
> > get the result you want only GROUP BY the fields that you're not using[/color][/color]
the[color=blue][color=green]
> > Sum function on.
> > GROUP BY DataTable.Area; <== This is all that's needed. Remove the[/color][/color]
other 3[color=blue][color=green]
> > fields from this clause
> >
> > Stewart
> >
> >
> > "Kevin Carter" <kcarte10@ford.com> wrote in message
> > news:2a17a2d5.0407110120.75bac214@posting.google.c om...[color=darkred]
> > > Hi
> > > i have a table that contains several fields
> > >
> > > field 1(area), field2(quantity),Field3(price)
> > > What i want to do is
> > > return total quatity and total price against a distict area
> > >
> > > i have the following code
> > >
> > > strSQL = "SELECT distinctrow datatable.area,
> > > Sum([DataTable].TotalPrice) AS [SumOfTotal Price],
> > > Sum([DataTable].Quantity) AS [SumOfTotal Quantity]" & _
> > > "FROM datatable " & _
> > > "WHERE DataTable.Dates Between" &
> > > SQLDate(Me.cboStartDate.Value) & "And" & SQLDate(cboEndDate.Value) & _
> > > "GROUP BY DataTable.Area, DataTable.TotalPrice,
> > > DataTable.Dates, DataTable.Quantity "
> > > this code returns each record in the table
> > > ie
> > > area Quantity Total Price
> > > office 10 400
> > > gym 20 100
> > > office 50 2000
> > >
> > > What i want is
> > > area Quantity Total Price
> > > office 60 2400
> > > gym 20 100
> > >
> > >
> > > i can not figure out how to return the distinct area with the totals
> > >
> > > any help greatly welcomed
> > >
> > > thanks
> > >
> > > kevin[/color][/color]
>
> thanks for reply
>
> When i run the query i get a message box asking for the date. this i
> want to avoid as the user has already selected the date range. if i
> include the dates in my select statement it becomes part of the
> distinct criteria, What i need is a distinct area with all the dates
> in the range selected by the user
>
> Hope this makes sense
> kevin[/color]
There's also a problem with your WHERE clause, use the # as the delimiter
for dates.
e.g.
"WHERE DataTable.Dates Between #" & SQLDate(Me.cboStartDate.Value) _
& "# And #" & SQLDate(cboEndDate.Value) _
& "GROUP BY DataTable.Area;"
There's no need for distinctrow if you are using the aggregate functions
(Sum, Count, Min etc.). Using your dates as the filter in your WHERE clause
will collect the required records for you.
e.g. DataTable
Area Quantity TotalPrice TheDate
office 10 400 16/06/04
gym 20 100 17/06/04
office 50 2000 20/06/04
SELECT Area, Sum(Quantity) AS Q, Sum(TotalPrice) AS P
FROM DataTable
WHERE TheDate Between #06/15/04# And #06/17/04#
GROUP BY Area;
Returns
Area Q P
office 10 400
gym 20 100
But
SELECT Area, Sum(Quantity) AS Q, Sum(TotalPrice) AS P
FROM DataTable
WHERE TheDate Between #06/15/04# And #06/20/04#
GROUP BY Area;
Returns
Area Q P
office 60 2400
gym 20 100
| 
