change fields in query by user input

F

finster26

I have fields that are years (ie. y2005, y2006, y2007). Each field has data
such as "CR" or "CS". Is it possible to have a single query search a field
based on user input? Example: User chooses year 2006 and "CR", the query
would search in the field y2006 for "CR" for each record.

Thanks,
BOb
 
D

Duane Hookom

Is there any way that you could normalize your table so you have a field for
the Year rather than multiple year fields?
 
F

finster26

I looked at that will no success.This is a street maintenance projection
database. A street will get coded to have a maintance treatment such as "CR".
"CR" will be placed in the year field (y2006) teh year it would happen. I can
then pull up all the streets schedule for maintenace for y2006 by treatment
code ("CR").

I looked at using the treatment code for the fields then putting the year as
data. This would scatten the maintenace work for a particular year among a
range of fields. The fields would then need to repeat once the maintaneace
cycle is restarted for year further out.

STREET OL CR CS/P MO CR CS/P OL
Main St 2005 2008 2011 2015 2018 2022 2025
Olive St 2003 2006 2010 2014 2018 2023 2026
 
Top