grouping data by age

[justme43]

Could it have something to do with my relationships

[nico5038]

A SELECT query can't block the database.
Can you post the SQL text here ?

Nic;o)

[NeoPa]

May I point out that unless you take some more care over your posts there is a danger that many of our experts will ignore your posts in future due to the obvious lack of care taken before posting, even to formulate your thoughts into readable English. I can tell you from my own perspective that I don't appreciate having to read most of your posts 3 times just to make some kind of sense. I suggest, if you're interested in receiving help in future, you take care to avoid giving the impression you value your own time so much more highly than the time you waste of our experts.

-Administrator.

[justme43]

I am trying not to waste your time. I have had some pressing work that has to be completed and that is why I have not posted anything. I have also been doing some reading trying to work out which is the best way to work out my problem as my data base will not let me do anything with it at the moment.
I have tried to export my tables to another data file but i am still having the same problem. Tonight I have spent on this. I am extremely sorry if I have upset you I am trying very hard to do my best. I really do appreciate all the time you have given me and my time is not more valuable than yours I regret giving that impression. Can you please give me some time to figure out what I have done. and accept my apology

[justme43]

In my reading today I found this I have not been able to try it out I was wondering what you thought of it
If you are trying to count by decades 0 to 9, 10 to 19, etc you can accomplish
this with just some math

SELECT DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate], 
 >"mmdd") > Format(Date(), "mmdd"), 1, 0)\10 AS [Decade], 
Count(MBRS.MbrBirthdate) AS CountByDecade 
FROM MBRS 
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
GROUP BY DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate], 
 >"mmdd") > Format(Date(), "mmdd"), 1, 0)\10

If you want to use your formula for age (which is a bit less accurate then)

SELECT ((DATE()-[MbrBirthdate])/365.25)\10 AS [Decade], 
Count(MBRS.MbrBirthdate) AS CountDecade 
FROM MBRS 
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
GROUP BY ((DATE()-[MbrBirthdate])/365.25)\10 
ORDER BY ((DATE()-[MbrBirthdate])/365.25)\10 DESC;

If you need date ranges that are different than that you would need a table
similar to Duane's. I would modify it slightly and make ageMax the cutoff
value that is for the first group I would have 10 not 9. I would then test in
the query to make sure the Age Group was LESS than the AgeMax. Modifying your
query to use that table

SELECT tblAgeGroup.AgeGroup, tblAgeGroup.AgeMin 
Count(MBRS.MbrBirthdate) AS CountOfMbrBirthdate 
FROM MBRS INNER JOIN AgeGroup 
ON ((Now()-MBRS.[MbrBirthdate])/365) >= tblAgeGroup.AgeMin 
AND  ((Now()-MBRS.[MbrBirthdate])/365) < tblAgeGroup.AgeMax 
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
GROUP BY tblAgeGroup.AgeGroup, tblAgeGroup.AgeMin 
ORDER BY tblAgeGroup.AgeMin DESC;

[nico5038]

I would use an Age function (stored in a module) like:

Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

    If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
                Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
            Age = Year(DateToday) - Year(Bdate) - 1
    Else
            Age = Year(DateToday) - Year(Bdate)
    End If
End Function

From http://www.mvps.org/access/datetime/date0001.htm (good site with many good samples and info!)

Now your query can use:

SELECT tblAgeGroup.AgeGroup, Count(*) AS MembersInGroup
FROM MBRS, AgeGroup
WHERE Age(MBRS.[MbrBirthdate] between tblAgeGroup.AgeMin AND tblAgeGroup.AgeMax
AND MBRS.Memtype<>"comp" 
AND MBRS.ExpDate>Date()
GROUP BY tblAgeGroup.AgeGroup
ORDER BY 1 DESC;

When the MBRS.ExpDate can be empty, an additional IsNull test should be added...

Getting the idea ?

Nic;o)

[justme43]

this has worked thanking you very much for your time and patience.

[nico5038]

Glad I could help. Success with your application "Down under" :-)

Nic;o)

[NeoPa]

I am trying not to waste your time. ...

I'm not upset. I'm merely trying to advise you how your posts can be seen from this side of the fence. Your posts have not been breaking any rules as such, so my moderator hat (& truncheon) were not needed.

If all your future posts are as free of confusion and typos as this one was, then I see no reason to worry.

I'm also pleased to see that you have received some help in my absence (which was related to me rather than trying to avoid this particular thread) over the last number of days. I will arrange to have Nico's helpful post set as the Best Answer to reflect this.