O
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
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