Query Field Criteria

[Jeff]

I have an Access program with several tables, queries and reports;
thousands of data points.

I have a field named "Capacity" belonging to tblCapacity which also
has fields plantCode, Year...

I want to create a query to display the capacity for 2006, 2007, 2008,
2009, 2010 for each plant.
i.e. split the field "Capacity" into 5 separate fields - can this be
done by supplying "Criteria"?
It would look like this:


Plant Company Capacity 2006 Capacity 2007 Capacity
2008 ....
- - -
- -
- - -
- -


A cross-tab query is unacceptable.

Thanks for the help and the whole shindig. Can't stop addicted to the
shindig.

 

[Guest]

Don't know why a crosstab is not acceptable, but you could do it this way:

Create a calculated field for each year and use an IIf statment to include
the value for the record in the column for the year:

Capacity_2006: IIf([TheYear] = 2006, [Capacity], Null)
Capacity_2007: IIf([TheYear] = 2007, [Capacity], Null)

And so on. Note. If your field is named Year, change it. Year is a
reserved word in access (the name of a function) and in this case, I can
almost guaranty problems if you don't change it.

 

[Jeff]

Thanks. It works now.