Access query summing multiple columns with multiple criteria

[KarinS]

I’m using 3 tables. SpeciesData (fields: Genus, Species, CommonName) , PlantData (fields: Action, NumPlants) , EventData (fields: Location, EventDate). Of course there are various ID fields to relate the tables.I have no problem creating a simple query showing the number of seedlings (by species) planted in total for a given date range but what I really need is to report on how many seedlings were planted (Action=plant) in Location 1 in Q1 (1July-30Sept), Location 1 Q2 (1 Oct-31Dec) Location 2 Q1, Location 2 Q2, etc....All in one table for comparison. I have tried various things in the query like using DSum to create a new field and specifying the criteria, but nothing works. I’m not even sure if it is possible to use DSum if the fields for the necessary criteria are in different tables.
I would be very grateful for any help!

[June7]

Need to create a field in query that calculates the quarter. DatePart will calculate quarters based on calendar year.
DatePart("q",EventDate)

If the data is multi-year then also need the year part.
Year(EventDate)

Group data on those two fields.

If you want to adjust the quarters for July-June year:

Choose(DatePart("q",EventDate),"Q3","Q4","Q1","Q2" ))

[KarinS]

Ok, I tried your suggestion and it results in a table that shows the number of plants by species by quarters but how do I tie the different locations in? I need results for location1 in q1, location1 in q2, location2 in q1, loc2 in q2, etc. I would like to do it in the same query so the results are in just one table for easy comparison.
thank you for your help.

[June7]

Need a relationship between tables so can be joined in query and include Location field in the GROUP BY criteria. I can't tell how tables are related.

[KarinS]

Need a relationship between tables so can be joined in query and include Location field in the GROUP BY criteria. I can't tell how tables are related.

sorry I should have just included the ID fields in my earlier post. The SpeciesData table and the PlantData table are related by a field called TaxonID; the PlantData table and the EventData table are related by a field called OutPlantID.

Right now I set this up as a cross tab query with the Genus, Species and CommonName fields set up as row headings, the sum of NumPlants is set as value, and the quarters are column headings. But what happens is that when I run the query I have multiple rows for any species that was planted in more than one quarter. Is there any way to set it up so that each species only shows once?

thanks again for your continued help

[June7]

Crosstab should result in one row for each species. Don't know why it isn't.

You don't mention using Location in the crosstab criteria. I thought you wanted the data by species for each location for each quarter

I need sample data and the attempted SQL. Post or attach db. Follow instructions at bottom of my post.

Is the data multi-year?

[KarinS]

I have attached the database with the 3 tables and 1 query, so you can see what I've done so far. Yes, the data are multi year and ideally I would want to summarize data by quarters from different calendar years. For example, July-Sept 2011, Oct-Dec 2011, Jan-March 2012, Apr-June 2012 would represent quarters 1,2,3,4 for fiscal year 2012. Ideally also I would also have separate columns for the individual locations for each quarter, I that is possible.
Thanks so much for your continued help and Happy New Year!

[June7]

Why would you need Locations in columns? How many locations are possible? Right now there are 16. Four quarters for each location would be 64 columns. How would you arrange 64 columns on a report?

Try this adjustment to the CROSSTAB:

TRANSFORM Sum(PlantData.NumPlants) AS SumOfNumPlants
SELECT EventData.Location, [Genus] & " " & [Species] AS GenSpe, SpeciesData.CommonName, Year([EventDate]) AS YearRec
FROM EventData RIGHT JOIN (PlantData LEFT JOIN SpeciesData ON PlantData.TaxonID = SpeciesData.TaxonID) ON EventData.OutPlantID = PlantData.OutPlantID
WHERE (((PlantData.Action)="plant") AND ((Year([EventDate]))=2012))
GROUP BY EventData.Location, [Genus] & " " & [Species], SpeciesData.CommonName, Year([EventDate])
PIVOT Choose(DatePart("q",[EventDate]),"Q3","Q4","Q1","Q2");