Identifying Records to Delete and/or Using Delete Query - Is There Anyway to Get This To Work?

[mcl]

PARAMETERS [Enter minimum number of monthly obs] IEEESingle;
SELECT *
FROM datsav3
WHERE exists
(SELECT Datsav3.blkstn, Datsav3.year, Datsav3.mo, Count(Datsav3.day) AS
CountOfday
FROM Datsav3
GROUP BY Datsav3.blkstn, Datsav3.year, Datsav3.mo
HAVING (((Count(Datsav3.day))<[Enter minimum number of monthly obs])));


If I just use:
SELECT Datsav3.blkstn, Datsav3.year, Datsav3.mo, Count(Datsav3.day) AS
CountOfday
FROM Datsav3
GROUP BY Datsav3.blkstn, Datsav3.year, Datsav3.mo
HAVING (((Count(Datsav3.day))<[Enter minimum number of monthly obs]));

It identifies the low ob count months fine but in the first case it just
selects everything.
I want to be able to blow away all records with low ob count months.

As I've said in previous questions I'm a meteorologist/climatologist. I've
been using Access among other things to analyze climate records. I pull the
hourly data for a particular station down from our tape archives and use
Access to compute a bunch of stuff. Data for some stations especially
outside the US can be especially sparse and sporadic. Do the math. Most
weather data is 3 hourly (many US stations report hourly). If a station
reports every three hours that's 8 per day times either 28, 30 or 31 per
month. So there should be ~240 obs per month. There are cases in the records
which for some stations that go back to the early 1900's where there might
be only a few 10 or 15 obs for a station. What to you think you get trying
to compute monthly climate info off of just a few obs for the whole month?
Garbage In = Garbage Out. So far I've been deleting to low obs months
manually out of the table. There must be someway I can let Access do the
work.

 

[John Spencer (MVP)]

You might try to handle this as a nested query.

Query one identifies those that meet you criteria:
SELECT Datsav3.blkstn, Datsav3.year, Datsav3.mo, Count(Datsav3.day) AS
CountOfday
FROM Datsav3
GROUP BY Datsav3.blkstn, Datsav3.year, Datsav3.mo
HAVING (((Count(Datsav3.day))>=[Enter minimum number of monthly obs]));

Then the second query uses that in a join.

SELECT *
FROM DatSav3 as D INNER JOIN QUERYONE as Q
ON D.blkstn = q.blkstn
AND D.[Year] = Q.[Year]
AND D.Mo = Q.Mo

You could also change the exists clause to include a WHERE CLAUSE that matches
the station, year and mo fields

PARAMETERS [Enter minimum number of monthly obs] IEEESingle;
SELECT *
FROM datsav3 AS A
WHERE exists
(SELECT Count(D.day) AS CountOfday
FROM Datsav3 AS D
WHERE D.blkstn = A.blkstn
AND D.[Year] = A.[Year]
AND D.Mo = A.Mo
GROUP BY D.blkstn, D.year, D.mo
HAVING (((Count(D.day))<[Enter minimum number of monthly obs])));