Access help

[Elias824]

First I have to complain at my boss for being so freaking picky about how all this stupid stuff looks.

Well this is for a report, I work for a drug clinic and we have everyone do a report when the come in and with the leave. The report asks in the last 30 days, how many days did you do x?

All the data gets grouped together into a report so we can see out totals of how many people did drugs how many days when the cam in and when they left. First the I have a querey that dose a group by the number of days, then a second column that dose a count of each day, ends up looking like this.


Number of Days, Number of people
0 ---------------------- 12
3 ---------------------- 22
4 --------------------- 7
13 --------------------- 25
30 --------------------- 65

Anywyays the report is usually pretty big looking cause if we had 1 person do drugs 15 times a month, and 1 person do them 25 its keeps adding lines. So anyways I need to group them into sections of 0, 1-10, 11-20 21-29 and 30, in some reasonable way. Right now im trying to replace one of the previous guys databases who had upwards of 300 queries and reports to do, well even less then this. So the simply I can make this the better.

edit* Maybe I can use an if statement or something before I do a count, that says if its between 1-10 set it = 10 or something? not sure if thats possible.

 

[KLin]

Use the iif(extended if) function to evaluate the value and replace it with a value of your choice.

ie in a field in the query you'd have something like iif(DayCount <= 10, 10, iif(DayCount >= 11 and DayCount <= 20, 20, iif(DayCount >= 21, 30)))

http://www.techonthenet.com/ac...tions/advanced/iif.php

 

[Elias824]

Ok well thats a great Start, here is what I have now.

IIf([B1c_Drug]=0,0,
IIf([B1c_Drug]>= 1 And [B1c_Drug]<=10,10,
IIf([B1c_Drug]>= 11 And [B1c_Drug]<=20,20,
IIf([B1c_Drug]>= 21 And [B1c_Drug]<=29,29,
IIf([B1c_Drug]= 30,30)))))

Now my other issue seems to be its not using the and part, it wont search the inbetweens, only the exact values. i.e it only works if the value is allreayd 0,10, 20, 29, 30. Maybe i have somthing backwards?

 

[KLin]

you can try using Between 1 and 10 instead of >= and <= to see if that works.

 

[PhatoseAlpha]

Couldn't a join on a quicky relation table work too? A table with Min, Max, Value.

Then a query like select stoners.stoner_name, stoner_ranks.stoner_level from stoners, stoner_ranks where stoners.bowlssmoked >= stoner_ranks.min and stoners.bowlsmoked < stoner_ranks.max

 

[KLin]

Originally posted by: PhatoseAlpha
Couldn't a join on a quicky relation table work too? A table with Min, Max, Value.

Then a query like select stoners.stoner_name, stoner_ranks.stoner_level from stoners, stoner_ranks where stoners.bowlssmoked >= stoner_ranks.min and stoners.bowlsmoked < stoner_ranks.max

That would be another way to do it. It would make change requirements easier probably(only need to change a range in a data record instead of changing query statements that reference the iif statement with static values).

 

[Elias824]

ok how would I do that? ive given up on this iff statement thing I cant get it working. Still the same bug even if I do only one if statment. Sorry for the slow reply been working on some other things.

 

[PhatoseAlpha]

Should be relatively simple.

Create a table, name it anything you like, I'm going to Use Count for this example.
Count has 3 fields - GROUP_NAME, MIN_OCCURANCES, MAX_OCCURANCES.
Open up the count table, add in the relevant data. For what you've give us, that would be something like

0, 0, 0
1-10, 1, 10
11-20, 11, 20
21-30, 21, 30
30, 30, 30

Then it's a simple join command. Without the exact table structure, I can't give you the exact query, but it should look something like:

Select Patients.*, Count.group from Patients, Count where Patients.daysUsed >= count.MIN_OCCURANCES and Patients.daysUsed <= count.MAX_OCCURANCES.