Query to select columns in a table

[Niall]

I have a table which has the following headings:

Name 2004 2005 2006 2007

Under the name field is all the staff members name. Under
each of the years (2004-2007) is the number of holidays
that member of staff is allowed in that year. I am trying
to build a query that asks a user to enter a year (2004-
2007) and I want the names of all the staff and their
entitlement for that year. Please note using []'s doesnt
return the required column.

Any help much appreciated

 

[Duane Hookom]

Your table structure is un-normalized. Year values are data values and
should be stored in a field not a field name. Each number of holiday values
should create its own record in a table so a staff member would have one
record per year.
Also, Name is a poor Name for a field since every object in Access has a
Name property. You would be much better off with fields like:
StaffFirstName
StaffLastName

If you really want to go off in your current "spreadsheet" direction, you
could use
SELECT [Name], 2000 + [Enter the Year ie 4 for 2004] as TheYear,
Choose([Enter the Year ie 4 for 2004], "No Data", "No Data", "No Data",
[2004], [2005], [2006], [2007]) as Entitlement FROM tblYourSpreadsheet;