Sum if statement - Queries

[learning_codes]

Hi,

I hope you can help me out.

I have a table that will help me to find players within the age group:
Baseball
Hockey
Tennis
Soccer
Football


There are two age groups (25-27) and (30-24). Only soccer use the
age group (25-27) but all others use (30-34).

Did I do the right thing on the queries?

Sum(IIf([AgeGroupTable] like "Soccer",IIf([Age]>=25 and [Age]
<=27,'10',Sum(IIf([AgeGroupTable] not like "Soccer",IIf([Age]>=30 and
[Age]<=34,'15'))

Your help would be much appreciated.

Thanks

 

[MGFoster]

Hi,

I hope you can help me out.

I have a table that will help me to find players within the age group:
Baseball
Hockey
Tennis
Soccer
Football


There are two age groups (25-27) and (30-24). Only soccer use the
age group (25-27) but all others use (30-34).

Did I do the right thing on the queries?

Sum(IIf([AgeGroupTable] like "Soccer",IIf([Age]>=25 and [Age]
<=27,'10',Sum(IIf([AgeGroupTable] not like "Soccer",IIf([Age]>=30 and
[Age]<=34,'15'))

Don't use "LIKE" and "NOT LIKE". Use = and <>, respectively.

 

[John Spencer]

What are you trying to do. If you are trying to identify an age group
then there is no reason to use SUM

IIF(AgeGroupTable = "Soccer" and Age>=24 and Age <=27,"10",
IIF(Age >=30 and Age<=34, "15",Null))

That will return the STRING 10 for Soccer between the age of 24 and 27
or the STRING 15 for anyone with Age between 30 and 34 and null for
everyone else. If you want a result for age under 30 and baseball then
you need to specify that also.

If you are trying to sum values of 10 or 15 then remove the quotes and
add a sum around the expression.

Sum(IIF(AgeGroupTable = "Soccer" and Age>=24 and Age <=27,10,
IIF(Age >=30 and Age<=34,15,Null)))

If you want 15 for everyone that is not Soccer then the following
expression should work.

Sum(IIF(AgeGroupTable = "Soccer" and Age>=24 and Age <=27,10,
IIF(Age >=24 and Age<=34,15,Null)))

The first IIF will get all the Soccer between age 24 and 27 and return
10. The second IIF will return 15 for all players between 24 and 34
except those that met the criteria in the first IIF.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ken Sheridan]

Its not easy to see just what you want here, but I'd suggest you start by
getting all your data into tables, which is the only place where data should
be held in a relational database (Codd's Rule 1). Firstly a table Sports
with column Sport:

Baseball
Hockey
Tennis
Soccer
Football

Then a table SportAgeGroups with columns Sport, LowerAge, UpperAge:

Baseball 30 34
Hockey 30 34
Tennis 30 34
Soccer 25 27
Football 30 34

Relate the above two tables on Sport and enforce referential integrity.

Then a table Players with columns PlayerID, FirstName, LastName, Age (more
about this later) etc.

You can then find which players are eligible for which sports with, for
example:

SELECT FirstName, LastName, Age, Sport,
LowerAge & " – " & UpperAge AS AgeGroup
FROM Players, SportAgeGroups
WHERE Players.Age BETWEEN
SportAgeGroups.LowerAge AND SportAgeGroups.UpperAge
ORDER BY Sport, LastName, FirstName;

However, peoples' ages change from year to year, so its far better to store
each player's date of birth in the Players table You can then compute their
current age at any time using one of the methods you'll find at:


http://www.mvps.org/access/datetime/date0001.htm


Ken Sheridan
Stafford, England