Sum of Values

[Guest]

Hello,
I have a query for some vegetation sampling data.

The 4 tables are set up like this arrows indicated linked fields (note: I
didn't design the database)

Env Table Cover Table Species Table Type
Table
Plot ID ------> Plot ID Species Name
Plot name Species # ----> Species #
Percent cover Species Group
lkup Type ------>------------------> Type ID

Type Name

All relationships are one to many with the cover table being the "many"
in all cases. The lkup type is a combo box that looks up the type name in
the type table.


So each sample event has a plot id and name, within each plot there are
percent cover calculations for each tree species (i.e. jack pine, lodgepole
pine, white spruce, black spurce etc) found in that plot. Each species in the
plot is also identified by group (i.e. spruces, pines, poplars, etc) and type
(natural or introduced). I want to calculate the sum percent cover of each
plot number by type (i.e. natural) and by group (spruces) . So basically I
want to know the total percent cover of all spruce species that are natural
for each plot number.

I tried simply sum percent cover by using natural and spruce as criteria in
the query and just not showing those two fields in the results hoping it
would sum by plot number but this did not work. Any ideas on how to do this
would be great.

I think the problem lies in that species group and species name are in the
same table and therefore when i query using species group (i.e spruces) as
the criteria it still gives me all results for all individual spruce species
records instead of grouping them all together by group and calculating a sum
for the group.

I realize this may be confusing but any comments or advice would be grealty
appreciated.

Thanks in advance,

 

[Guest]

Karl,

How about something like:

SELECT [Cover Table].[Plot ID],
[Species Table].[Species Group],
SUM([Cover Table].[Percent cover]) as GrpPct
FROM [Cover Table] LEFT JOIN [Species Table]
ON [Cover Table].[Species #] = [Species Table].[Species #]
GROUP BY [Cover Table].[Plot ID], [Species Table].[Species Group]

HTH
Dale