Query to run through each instance. MS-Access

Question

I have a MS-Access database with two tables which I would like to query from, the basic table schema is shown below. I am looking to pull out the details for the earliest parish church in each parish – and in the instance that there is no church with ‘parish’ in the name; I would like to pull out the earliest church.

SITEDETAIL:

Site
Reference No. |  Civil Parish |  Site Name         |  NGR East  | NGR North   
1                Assynt          Old Parish Church    6137        3172
2                Assynt          St. Marys            6097        3870
3                Assynt          New Parish Church    6249        3490
4                Bower           Grimbister           2095        4067
5                Bower           St. Andrews          2304        3194
6                Halkirk         Firth Parish Church  7136        3450
7                Holm            Strath Parish Church 4586        2045
8                Holm            St Nicholas Parish   4132        3146    

SITEDATES:

Site 
Reference No.  | Date
1                1812
2                1300
3                1900
4                1760
5                1750
6                1838
7                1619
8                1774

I have written a query that pulls out all the instances of ‘parish’:

SELECT SITEDETAIL.SITEREFNO, SITEDETAIL.CIVPARBUR_CDE, SITEDETAIL.SITENAME, SITEDETAIL.NGRE, SITEDETAIL.NGRN, SITEDATES.DATE

FROM SITEDETAIL INNER JOIN SITEDATES ON SITEDETAIL.SITEREFNO = SITEDATES.SITEREFNO
WHERE (((SITEDETAIL.SITENAME) Like "par*"));

However, this does not take into account the instances of multiple/no churches with ‘par*’ in the name.

Is it possible to create an SQL query that runs through each civil parish and selects the earliest ‘parish’ or earliest church, or is it necessary to write a perl script to run through them? Is this possible using DBI?

Desired output:

Site
Reference No. |  Civil Parish |  Site Name         |  NGR East  | NGR North   | Date
1                Assynt          Old Parish Church    6137        3172          1812
5                Bower           St. Andrews          2304        3194          1750
6                Halkirk         Firth Parish Church  7136        3450          1838
7                Holm            Strath Parish Church 4586        2045          1619

NB:In the case of Assynt, 'Old Parish Church' is selected despite being older because of having 'parish' in the name.

Answer 1

The following query should get you what you need. It's a little long, but it does the trick:

`select LIST.Civil_Parish, SD.Site_name, LIST.MSite_Date
from
(
select Civil_Parish, min(Site_date) as MSite_date
from SiteDetail
where Boolean = 1
group by Civil_Parish

union

select Civil_parish, min(Site_date) as MSite_date
from SiteDetail
where Civil_parish not in
(select Civil_parish
from SiteDetail
where Boolean = 1)
group by Civil_Parish) as LIST 
left join sitedetail SD on LIST.Civil_Parish = SD.Civil_Parish and LIST.MSite_Date = SD.Site_Date`

Please note the following:

1) I am using PowerUser's boolean suggestion. If the Boolean column has value 1, then the row is a Parish Church, and 0 if it is not.

2) I combined the tables "SiteDates" and "SiteDetails" for the purpose of this example, as they are 1 to 1.

The core of the query is A) finding the oldest Parish church in a Parish, then B) find Parishes without Parish Churches.

The code for A) is as follows:

'select Civil_Parish, min(Site_date) as MSite_date
from SiteDetail
where Boolean = 1
group by Civil_Parish'

We then union that with the oldest churches in parishes that do not have a parish church:

'select Civil_parish, min(Site_date) as MSite_date
from SiteDetail
where Civil_parish not in
(select Civil_parish
from SiteDetail
where Boolean = 1)
group by Civil_Parish'

We then join the union query (named "LIST" here) with our original "SITEDETAIL" table on Parish and Date to bring in the church name.