Making a crosstab in Access...without crosstab, because multiple values

July 14, 2020 6:36 PM

I've got a table, tblVegePlantings. It has a field VegeType, which contains items like Carrots, Peas, and Broccoli. I've got three other fields - DatePurchased, DatePlanted, and DateHarvested. Each record also has a unique PlantingID.

A few example rows (warning - non-US dates / dodgy table formatting):

PlantingID VegeType DatePurchased DatePlanted DateHarvested
A12345 Corn 1/7/2019 2/7/2019 1/8/2019
B23456 Peas 4/5/2019 15/8/2019 26/10/2019
C45678 Corn 4/8/2019 25/9/2019 15/12/2019

(These are imaginary vegetables with ludicrous growing times.)

So each planting has a vege type and three dates. Note that the same vege type can be used in more than one planting. Also, a date might be empty - for example, because I've purchased the seeds and planted them, but not harvested.

I'd like a query (it's probably going to be joined queries, plural) that spits out this:

VegeType DatePurchased DatePlanted DateHarvested
Corn 2 3 0
Peas 5 2 1
Broccoli 3 3 3

The numbers are the count of the dates that fall between two dates. For example, I might want to know how many veges I purchased between 1 July and 31 December 2019, and so I put those into the query as a criteria, and the result "Corn 2" tells me that I purchased corn twice between those two dates.

This is trivial with one value. It's probably also simple to do with multiple values in Excel as a pivot table. But because stupid bet, I'd really like to get it going in Access. I suspect the answer involves a goat, inner joins, and a pentagram.

posted by some little punk in a rocket to Computers & Internet (6 answers total)

Here’s what I’d try first. The left join sort of implicitly assumes that anything you have planted, you will have also purchased, and anything you have harvested, you will have also planted. An inner join would assume that there’s no vegetable you haven’t done all three things with at least once in that date range. (Or that if there are, you don’t want to count them.)

SELECT PurchaseCount.VegeType, NumPurchased, NumPlanted, NumHarvested
From
((SELECT VegeType, count(*) AS NumPurchased FROM tblVegePlantings WHERE DatePurchased > "07/1/2019" AND DatePurchased <> LEFT JOIN
(SELECT VegeType, count(*) AS NumPlanted FROM tblVegePlantings WHERE DatePlanted > "07/1/2019" AND DatePlanted <> ON PurchaseCount.VegeType = PlantCount.VegeType)
LEFT JOIN
(SELECT VegeType, count(*) AS NumHarvested FROM tblVegePlantings WHERE DateHarvested > "07/1/2019" AND DateHarvested <> ON PurchaseCount.VegeType = HarvestCount.VegeType;
posted by eirias at 7:04 PM on July 14, 2020

Response by poster: Thanks! Your implicit assumptions are spot on.

I'm getting a syntax error in the FROM clause. I've tried adding closing bracket(s) to to various places; this just shifts the syntax error to the JOIN operation. FWIW, I'm just copying and pasting the above - I'm actually using those table / field names.
posted by some little punk in a rocket at 7:58 PM on July 14, 2020

Response by poster: They syntax error highlights that closing semicolon. Adding three closing brackets (one for the start of the final select, two for the first SELECT after the FROM that don't seem to be closed anywhere) gets me:

Syntax error (missing operator) in query expression 'DatePurchased > "07/1/2019" AND DatePurchased <> LEFT JOIN
(SELECT VegeType, count(*) AS NumPlanted FROM tblVegePlantings WHERE DatePlanted > "07/1/2019" AND DatePlanted <> ON PurchaseCount.VegeType = PlantCount.VegeType)
LEFT JOIN
(SELECT VegeType, count(*) AS NumHarvested FROM tblVegePlantings WHERE DateHarvested > "07/1/2019" AND DateHarvested <> ON PurchaseCount.VegeType = HarvestCount.VegeType)'.

I'll stop floundering about now.
posted by some little punk in a rocket at 8:28 PM on July 14, 2020

Yeah somehow part of what I typed did not render properly, and I did not notice during the edit window! Where you currently have two angle braces together you need to have the other half of your date range instead, looking like “less than Dec 31.” (Also note I used the American date format, so you will want to switch the format to match your own.)
posted by eirias at 10:50 PM on July 14, 2020

fyi and iirc, you should use #, not double quotes, to delimit dates in Access. If you use double quotes, it usually works, silently converting the Date to a String, but this is dependent on the local date format settings.
posted by thelonius at 3:21 AM on July 15, 2020 [1 favorite]

My usual approach to this kind of thing is to consider what I wished the data looked like. In this case, wouldn't it be great if instead of a date, each of those columns had a 1 in it to indicate it happened within my date range of interest, or a 0 if it did not. Then I could sum all the rows, with a group by VegeType, and I'd have what I need.

I would proceed by trying to write a query that does exactly that (spits out the table but with a 1 or a 0 depending on each date), and then just add the sum and group by stuff once that works. I don't actually have Access on any of my computers any more, so haven't worked this out in code, but am hoping a suggestion on an overall approach might be helpful.
posted by FishBike at 9:44 AM on July 15, 2020