How would be the best way to do this in Access?
I have two tables. The first table has all the rows of data. The first
column of this table has a country name, the second has a year and the
third column is the data value.
The second table is just a list of standard years ie. 1980, 1985, 1990,
1995).
Is there a way to arrange a query so that all of the rows in table 1
which do not have a standard year (according to the list in table 2) in
the year column are noted and a new table (table 3) is created from
which does the following:
- for all rows in table 1 which do not have a standard year, the content
of year (in the year column) is changed to the standard year which is
closest. ie. if non-standard year is 1992, the standard year is 1990).
- a new column is added at the end with the field heading "notes". In
this column for the row, the original non-standard year ie. 1992 is put
as words "this data refers to 1992".
For example, if the tables are as follows then:
table 1:
country year value
austria 1992 11.5
table 2:
standardyears
1990
1995
table 3:
country year value notes
austria 1990 11.5 this data refers to 1992
Looking forward to your suggestions.
Matthew Kramer
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!