SQL for Access Users

Side by Side Demonstrations

Selecting

SELECT day, month, year, species FROM surveys;

SELECT * FROM surveys;

SELECT DISTINCT year FROM surveys;

Filtering

SELECT * FROM surveys WHERE species="DM";

SELECT * FROM surveys WHERE (year >= 2000) AND (species = "DM");

Sorting

SELECT * FROM species ORDER BY Genus ASC;
SELECT * FROM species ORDER BY Genus DESC;
SELECT * FROM species ORDER BY genus ASC, species ASC;

Aggregation

Get the combined mass of all individuals in each year.

SELECT year, SUM(wgt)
FROM surveys
GROUP BY year;

Joins

SELECT surveys.year, surveys.month, surveys.day, species.genus, species.species
FROM surveys
JOIN species ON surveys.species = species.species_id

Combining it all

SELECT surveys.year, species.genus, species.species, SUM(wgt / 1000.0)
FROM surveys
JOIN species ON surveys.species = species.species_id
WHERE species.taxa="Rodent"
GROUP BY surveys.year, species.genus, species.species