Totals Query
in [Queries]
|
Prev: Make table query combining records of two tables into a third tabl
Next: Removing text from a field, leaving a number
From: Erika on 27 Jun 2008 11:14 I have a table where I enter the following info Permit# Issue Date Cost State 1234 05/08/08 40.00 WI 1235 05/09/08 40.00 WI How do I set up a query that I can enter a start date and and end date and it will give me the total count of the Permit# and the total costs for WI within the date range. I am getting totals but it is grouping them by state and then listing them for each day and I just want the following if searched between 05/01/08 and 05/10/08 Permit count Cost State 2 80.00 WI
From: Michel Walsh on 27 Jun 2008 11:26 SELECT COUNT(*) AS premitCount, SUM(Costs) AS totalCost, state FROM yourTableName WHERE [Issued Date] Between [frist date to consider] AND [last date to consider] GROUP BY state Vanderghast, Access MVP "Erika" <Erika(a)discussions.microsoft.com> wrote in message news:07139253-BC96-49E1-A324-99CFA97D2292(a)microsoft.com... >I have a table where I enter the following info > > Permit# Issue Date Cost State > 1234 05/08/08 40.00 WI > 1235 05/09/08 40.00 WI > > How do I set up a query that I can enter a start date and and end date and > it will give me the total count of the Permit# and the total costs for WI > within the date range. I am getting totals but it is grouping them by > state > and then listing them for each day and I just want the following if > searched > between 05/01/08 and 05/10/08 > > Permit count Cost State > 2 80.00 WI
From: John Spencer on 27 Jun 2008 13:25
In design view (the query grid0, change GROUP BY to WHERE under the Issue Date field. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Michel Walsh wrote: > SELECT COUNT(*) AS premitCount, SUM(Costs) AS totalCost, state > FROM yourTableName > WHERE [Issued Date] Between [frist date to consider] AND [last date to > consider] > GROUP BY state > > > > Vanderghast, Access MVP > > > > "Erika" <Erika(a)discussions.microsoft.com> wrote in message > news:07139253-BC96-49E1-A324-99CFA97D2292(a)microsoft.com... >> I have a table where I enter the following info >> >> Permit# Issue Date Cost State >> 1234 05/08/08 40.00 WI >> 1235 05/09/08 40.00 WI >> >> How do I set up a query that I can enter a start date and and end date and >> it will give me the total count of the Permit# and the total costs for WI >> within the date range. I am getting totals but it is grouping them by >> state >> and then listing them for each day and I just want the following if >> searched >> between 05/01/08 and 05/10/08 >> >> Permit count Cost State >> 2 80.00 WI > > |