By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Query Help

P: 2
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.
Jan 24 '08 #1
Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jan 28 '08 #2

P: 2
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)
Jan 30 '08 #3

nico5038
Expert 2.5K+
P: 3,072
There's a way to "normalize" the data using a UNION query like:
Expand|Select|Wrap|Line Numbers
  1. select acct#, '200701' as YearMonth, Jan07 as Value from tblX
  2. UNION
  3. select acct#, '200702' as YearMonth, Feb07 as Value from tblX
  4. UNION
  5. select acct#, '200703' as YearMonth, Mar07 as Value from tblX
  6. UNION
  7. etc...
  8.  
The result can be used or stored in a (temp) table. Perhaps that can save some coding.

Nic;o)
Jan 30 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.