Hi Matthew,
Wow, that's a lot of years going across the page - if you link this
spreadsheet into the database, you should get each year as a column in the
resulting table eg. [ID], [Year1], [Year2], [Year3], ... [Year100].
You then need to set up a query that returns the results each year:
Query1: [ID], "Year1" as Year, [Year1] as Measure
Query2: [ID], "Year2", [Year2]
Query3: [ID], "Year3", [Year3]
You then need to join up these queries in a UNION query
SELECT * from Query1 UNION
SELECT * from Query2 UNION
SELECT * from Query3 UNION
etc.
A bit of a tip: so long as the first query has the right column headers
("Year" and "Measure"), then the whole query will get those columns, even
though the subsequent queries don't, which saves you some time and effort
in setting up the queries.
Since you've got more than 100 years of data, it will be a bit laborious to
set up in the first place, but it should be easy to add to after that.
The only problem you might run into is a limit on the number of queries you
can append together using the UNION statement: I don't know of any, but if
it happens, you may need to append the data into a separate table instead.
Hope that helps - I'd appreciate some indication of how it works out for
you!
--
Message posted via
http://www.accessmonster.com