selecting db results

  • Thread starter Thread starter Matt Shudy
  • Start date Start date
M

Matt Shudy

Hi,

I have a ms db that i need to get data from. I am able to
populate my whole spreadsheet so far besides the last
column, which is a totals column. Am i able to write a
SQL statement that will allow me to get the records from
one query based on the Plant Name and Year in another
query? The only way i have been able to get this to work
(Only in access) is to use a [RequestLocation] for the
criteria in the query that contains all the data that
needs to be totaled, but this won't work with my current
SQL statement. I am using fp 2000 ms access 2000 and am
writing my own asp results page. So just to clarify i
have two querys, one has all the information that needs to
be totaled including the plant name and year, the other
query will be used to calculate the totals. Is there a
way to write a SQL statement that will calculate the
totals based on the Plant Name and Year? If i haven't
made myself clear, please ask some qestions.

Thanks,

Matt
 
-----Original Message-----
Hi,

I have a ms db that i need to get data from. I am able
to populate my whole spreadsheet so far besides the last
column, which is a totals column. Am i able to write a
SQL statement that will allow me to get the records from
one query based on the Plant Name and Year in another
query? The only way i have been able to get this to work
(Only in access) is to use a [RequestLocation] for the
criteria in the query that contains all the data that
needs to be totaled, but this won't work with my current
SQL statement. I am using fp 2000 ms access 2000 and am
writing my own asp results page. So just to clarify i
have two querys, one has all the information that needs
to be totaled including the plant name and year, the
other query will be used to calculate the totals. Is
there a way to write a SQL statement that will calculate
the totals based on the Plant Name and Year? If i
haven't made myself clear, please ask some qestions.

Matt
Click to expand...

See if this helps. The following SQL statatement computes
and returns the total of the IncidentCount fields for each
unique combination of PlantName and Year. For example, if
there are three records for year 2001 at the Walla Walla
plant, and these records contain incident counts of 4, 5,
and 8, the result set would contain one Walla Walla 2001
record with a TotalIncidents value of 17.

SELECT PlantName,
Year,
Sum(IncidentCount) AS TotalIncidents
FROM PlantInfo
GROUP BY PlantName, Year
ORDER BY PlantName, Year;

This statement returns one record for each combination of
PlantName and Year because those are the fields that the
GROUP BY clause specifies.

It adds all the IncidentCount values within each group
because of the Sum() function in the SELECT clause.

Jim Buyens
Microsoft FrontPage MVP
(e-mail address removed)
http://www.interlacken.com
Author of:
*------------------------------------------------------*
|\----------------------------------------------------/|
|| Microsoft Office FrontPage 2003 Inside Out ||
|| Microsoft FrontPage Version 2002 Inside Out ||
|| Web Database Development Step by Step .NET Edition ||
|| Troubleshooting Microsoft FrontPage 2002 ||
|| Faster Smarter Beginning Programming ||
|| (All from Microsoft Press) ||
|/----------------------------------------------------\|
*------------------------------------------------------*
 
Back
Top