Filtering combobox list based on user selected year and more

[ole.christian.birkeland]

Hi.

I developing an Access DB for registration of historical info (1880 -
present) on sail boats (boat type, owner, year of purchase,
participation in regattas etc). Approx. 700 different sail boats is
estimated to be entered into the DB.

This leads to my problem which concerns a form for entering regatta
participation. I'm trying to filter the list of boats based on boat
type and year of purchase (which the user selects using 2 comboboxes),
since one boat may have changed owner several times, but can only have
one registered owner at a specified point in time (at least in this
case).
I'm using VBA to retrieve the year and boat type and to change the
query for the combobox listing the available boats (using .RowSource
and .Requery).

The queries I have tried so far returns all rows with boats of the
desired type, but either from all years prior to, and including, the
chosen year, or only those who've changed owner that particular year.

What I'm looking for, is a way to return only those rows with boats of
the given type, AND with the latest ownership registration up to the
chosen (filtering) year. See example below

PS! The data in the BOAT_REG_YEAR column will most likely not be sorted
in chronological order in the actual DB.

Table:

REG_ID BOAT_ID BOAT_TYPE BOAT_REG_YEAR
---------------------------------------------------
1 1 A 1880
2 1 A 1923
3 1 A 1935
4 1 A 1988
5 2 A 1945
6 2 A 1981
7 2 A 2001
8 3 B 1989

Desired output from query if the filtering parameters are Year=1990 and
BoatType=A :

REG_ID BOAT_ID BOAT_TYPE BOAT_REG_YEAR
---------------------------------------------------
4 1 A 1988
7 2 A 2001


Is this possible using one query? If I need to use 2 queries, what is
the best way to achieve this?

Regards,
OC

 

[Michel Walsh]

Hi,


something that can look like:

SELECT a.*
FROM myTable As a
WHERE a.boatID = FORMS!formname!suppliedBoatIDweSeek
AND a.boatYearOfRegistration=
(SELECT MAX(b.boatYearOfRegistration)
FROM myTable As b
WHERE b.boatID=a.boatID
AND b.boatYearOfRegistration <= FORMS!FormName!YearWeSeek)




The innermost query pick the maximum date of registration, for a given boat
and a given year, that still occur before the year you seek. Having that
important piece of information, we can know try to find the record, for that
boat, with that said date, role that performs the outermost query.



Hoping it may help,
Vanderghast, Access MVP

 

[ocb]

Hi,
and thanks for your reply. It put me on the right track towards the
desired boat list.
I used the query I mentioned earlier which returns all rows with boats
of the
desired type from all years prior to, and including, the
chosen year (with chronological YearOfRegistration) as the "inner"
query, and used the LAST() function on the YearOfRegistration column in
the "outer" query. See example below.

SELECT a.regID, a.boatID, a.boatType, Last(a.boatYearOfRegistration) As
RegYear
FROM (SELECT b.regID, b.boatID, b.boatType, b.boatYearOfRegistration)
FROM myTable As b
WHERE b.boatType=FORMS!FormName!BoatTypeWeSeek
AND b.boatYearOfRegistration <= FORMS!FormName!YearWeSeek)
As a
GROUP BY a.regID, a.boatID, a.boatType;

As far as I've tested, it returns only those boats I was hoping for.

Regards,
OC

PS! I'm using a new Google groups account with a throwaway email
address, but I guess the email address from my first post is on its way
into the spammers address books... C'est la vie.