Query Help

[dbProject]

Thanks for reading...I need all the suggestions I can get.
Access 2000 db.
We have project with 10 categories - each with their own set of criteria (txt or values that need to be met by customers).
Query Cols are: Acct #, Name, Start Date, Category, Jan07...Dec08 (etc.)
Start dates are staggered.
At any given time, I need a count of the values that have met the criteria for at least 6 months.
(For instance, if Cust ABC started the program in May '07, they have until
April 30, 2008 to reach the criteria for Category X six times - how many times have they met the criteria as of today?)
I figure I need to keep each category in a separate query, then combine the results in a report....any ideas on the calculation?
I've tried turning values into 1 & 0's and summing, but the variable start date is making it messy.
Thanks for the help.

[nico5038]

Your problem is cause by the table design:

Query Cols are: Acct #, Name, Start Date, Category, Jan07...Dec08 (etc.)

The Jan07.. Dec08 shouldn't be in one row.
Best to transform your table to:
Acct #, Name, Start Date, Category, YearMonth, other fields.

This will allow an easy filter on the needed period by using e.g. a
WHERE YearMonth Between '200701' and '200708'

Getting the idea ?

Nic;o)

[dbProject]

Nico; thanks. Unfortunately, the data is being pulled in from a Notes db which is in that format; I was hoping to avoid having to write code to transpose the data. As it is, I'm having to write an enormous amount of code to translate the data as much of it is values such as "1:28" or "1:45" - which come in as date/time - rather than comparitive values. I'll just have to deal with it....! Thanks again.

Your problem is cause by the table design:

Query Cols are: Acct #, Name, Start Date, Category, Jan07...Dec08 (etc.)

The Jan07.. Dec08 shouldn't be in one row.
Best to transform your table to:
Acct #, Name, Start Date, Category, YearMonth, other fields.

This will allow an easy filter on the needed period by using e.g. a
WHERE YearMonth Between '200701' and '200708'

Getting the idea ?

Nic;o)

[nico5038]

There's a way to "normalize" the data using a UNION query like:

select acct#, '200701' as YearMonth, Jan07 as Value from tblX
UNION
select acct#, '200702' as YearMonth, Feb07 as Value from tblX
UNION
select acct#, '200703' as YearMonth, Mar07 as Value from tblX
UNION
etc...
 

The result can be used or stored in a (temp) table. Perhaps that can save some coding.

Nic;o)