I bet this is actually quite a simple query....

[Axess_Newbie]

Hey all

I wonder if someone can help me out on what I think should be a simple query (although i can't seem to do it!)

I've got a table that looks like this...

Table_IceCreamLovers

• Person_ID
• Age
• Gender
• PostCode
• Favourite_flavour

Now All I need to do is find out how many people there are in the following age groups:

<16
16-20
21-25
25-30
31-35
36-40
41-45
46-50
>50

At the moment, I'm having to 'Group By' age and count the primary key 'Person_ID'. Then I have to export the whole list to excel and manually group the counts for each age range. Painstaking!!

Is there an easier way to do this IN DESIGN VIEW??

Thanks guys - whoever can help gets a lifetime supply of cyber ice cream!

R

 

[redneckgeek]

One of the things Access lacks in it's query designer is the Case statement, which would be a lot cleaner.

However, you can nest a bunch of Iif statements and get the result you're looking for. One of your fields will contain:
Iif([age]<16,"<16",Iif([age]<20,"16-20",Iif([age]<25,"20-25", ....))))

Access will process the Iif tests from left to right, and categorize in the first True instance it encounters. You'll put your group by on that field

 

[Axess_Newbie]

One of the things Access lacks in it's query designer is the Case statement, which would be a lot cleaner.

However, you can nest a bunch of Iif statements and get the result you're looking for. One of your fields will contain:
Iif([age]<16,"<16",Iif([age]<20,"16-20",Iif([age]<25,"20-25", ....))))

Access will process the Iif tests from left to right, and categorize in the first True instance it encounters. You'll put your group by on that field

OK, that looks like it could be right....

but what do I do with this line??

Iif([age]<16,"<16",Iif([age]<20,"16-20",Iif([age]<25,"20-25", ....))))

This is pretty embarassing - I'm very new at Access!!!!!!!!

 

[redneckgeek]

Right now, you have two fields in your "grid": Age and Person_ID
You're going to put the "formula" I've supplied in place of your age field.
But you need to continue it out for each category that you're looking for.

 

[Rabbie]

The iif function has the following format

IIF(Condition,Value if condition is true,Value if condition is false)

You can nest this function as suggested and it will give you the age group for each person in you icecreamlovers table.

You can then build an aggregate query to count the records in each age group which is what I think you want.

 

[Axess_Newbie]

You guys are absolute superstars!

 

[redneckgeek]

Where's my ice cream?

 

[Rabbie]

Where's my ice cream?

I bet it melted as it sped through cyberspace at the speed of light. Probably boiled away

 

[Axess_Newbie]

For you guys:

http://www.unitedwaysb.org/Ice Cream.jpg


But heres another challenge to test your brains...

Here are my IFF statements

IIf([age]<16,"under 16",
IIf([age]>=16 and [age]<=20,"16-20",
IIf([age]>=21 and [age]<=25,"21-25",
IIf([age]>=26 and [age]<=30,"26-30",
IIf([age]>=31 and [age]<=35,"31-35",
IIf([age]>=36 and [age]<=40,"36-40",
IIf([age]>=41 and [age]<=45,"41-45",
IIf([age]>=46 and [age]<=50,"46-50",
IIf([age]>=51 and [age]<=55,"51-55",
IIf([age]>=56 and [age]<=60,"56-60",
IIf([age]>=61 and [age]<=65,"61-65",
IIf([age]>=66 and [age]<=70,"66-70",
IIf([age]>=71 and [age]<=75,"71-75",
IIf([age]>=76 and [age]<=80,"76-80",
IIf([age]>=81 and [age]<=85,"81-85",
IIf([age]>=86 and [age]<=90,"86-90",
IIf([age]>=91 and [age]<=95,"91-95",
IIf([age]>=96 and [age]<=100,"96-100",
IIf([age]>=101,"Over 100",

But I need one or two more statements that says "Anything that is not a number or empty".

Any Ideas?

Also I've just realised that Access isn't able to run all these statements in one go... it says the query is too complex for it (which makes me feel damn intelligent!!)

 

[redneckgeek]

I was wondering if you'd bump into that

To check for numbers:
Iif(OR([AGE] IS NULL, NOT ISNUMBER([AGE]),"UNKNOWN",...

I'd put that at the beginning, otherwise, the non-number data will evaluate to true for >101 before it gets to the NOT ISNUMBER test.

As far as the complexity goes....

It may take 2 or 3 queries joined together to actually get it done.
The 1st grabs your first 7 categories, the 2nd gets the next 7, etc
Unfortunately, you'll have to "join" those queries together with a UNION query, which can only be done in SQL view:

SELECT * FROM QUERY1
UNION ALL
SELECT * FROM QUERY2

 

[JohnB]

Have you thought of using Switch?

Switch([age]<16,"under 16",[age]between 16 and 20,"16-20",[age]between 21 and 25,"21-25"....True,"Unknown")

It can be easier than all those iif's!

 

[The_Doc_Man]

There is also the nasty little "demographic ranking" lookup table approach.

Have a table with two fields.

tblAgeGroups
Age, integer
AgeGroup, text(10)

Then write a query to JOIN the raw table to the AgeGroups table on their common field - Age.

Now sort or count the AgeGroups column of the query.

The table would contain value pairs (for example)

1, "<16"
2, "<16"
...
16, "16-20"
17, "16-20"
...
30, "26-30"
31, "31-35"
...
50, "46-50"
51, ">50"
52, ">50"

etc.

That plus the test suggested by RedNeckGeek should significantly speed up the process and allow you to change your mind about demographic breakpoints later if needed. The beauty is that you set up that table exactly ONCE and then leave it alone. The typing is a lot easier and the silly parentheses required for deeply nested IIF becomes less of a bother. For that many nestings, you would generate a statement that looks like the old LISP language, a.k.a. "Lots of Insipid, Silly Parentheses." (O.K., it originally represented "LISt Processing language" - but nobody ever used it once compilers learned how to deal with strings and finite state automata better.)

 

[raskew]

Hi -

This problem begs for the Partition() function (see help file and try the example against Northwind's Orders table).

The only problem is the need for <16 and >50 categories, which the Partition() function, in its native format, isn't prepared to handle.

I've started a separate thread http://www.access-programmers.co.uk/forums/showthread.php?t=159751, asking for some assistance converting an SQL Server solution, that addresses the stated shortcoming, to VBA.

You might want to take a look at the Partition() function and see if you could live with specific categories (e.g. 0-4, 5-9, 10-15) rather than the <16 consolidated category.

Best Wishes - Bob

 

[wazz]

i created a number field ([freightcost]) and entered some random numbers.

using this query:

'range starts at 16, ends at 50 and increments by 5.

SELECT distinctrow partition([freightcost],16,50,5) as Range,
count(MyTbl.freightcost) as Count
FROM MyTbl
group by partition([freightcost],16,50,5) ;

i got this:

Range	Count
  :15	1          'numbers <16 are included in the result
16:20	1
21:25	1
26:30	4
31:35	2
36:40	1
41:45	3
46:50	1
51:  	7          'numbers >50 are also included in the result.

 

[raskew]

Wazz -

Thanks for that! I'm kind of slow.

Take the Help File example and see if you can reproduce your results, including the <16 and >50. I'm not trying to challenge you, just can't make it work myself.

Best wishes - Bob

 

[wazz]

Take the Help File example and see if you can reproduce your results, including the <16 and >50.

hmm, that's what i did actually. (i called my field freightcost because the sample field is called freight). can't imagine why at this point why it's not working for you. maybe it's a data type issue? i used a number type (long) (arguments require whole numbers).

 

[raskew]

Wazz -

As I pointed out, I'm a tad slow.

Would you be so kind to post your code, directed to Northwind's Orders table (versus the Help File example).

Thanks - Bob

 

[wazz]

oic. i'll try it with northwind.

 

[wazz]

the sample worked as printed. i also changed the start number to 16 and that worked:

SELECT DISTINCTROW partition([freight],16,500,50) AS Range, 
count(orders.freight) AS Count
FROM orders
GROUP BY partition([freight],16,500,50);

result:

Range	Count
   : 15	229
 16: 65	312
 66:115	122
116:165	65
166:215	41
216:265	18
266:315	9
316:365	9
366:415	7
416:465	2
466:500	3
501:   	13

interesting that it works with a currency field.

 

[raskew]

Hmm,

Very interesting!

I need to play with this for a while.

Thanks so much for your speedy responses.


Bob