Searchng for ways to group"Ages" into sub-categories or sub-queries

[djclinton15]

Hi. I’m working on a database consisting random birthdates. I sort of hesitant to mention within this same database I also have a table of randomly first & last names. At some point I will need to input randomly birthdates into this table. For now let’s focus on the Birthdates table in which I have a query which contains a field of ages ranging from 18 to 65 years old. The field of ages is a calculated field from birthyear, Age: DateDiff("yyyy",DateSerial([BirthYear],1,1),Date(). Here where I’m stuck. Of course, I can create a sub-query for the ‘birthdates’ off of the ‘ages.’ However, I want to group the ages together based on what I’ll refer to as, ‘AgeRanges.’ This would not be a calculated field & would need to be manually inputted into each Record. First, let me try to explain with some examples. Say I have 30-Records all with ages from 20 to 29 then in the ‘AgeRanges’ field I would individually input the ages ranges, “20-29”. Likewise, if have 10-Records all with ages from 60 to 69 then in the ‘AgeRanges’ field I would individually input the ages ranges, “60-69”.

Here’s what I’m looking to have my query be:
Ages Age Ranges
19 18-19
25 20-29
31 30-39
49 40-49
52 50-59
60 60-69

Yes, I know there are many errors or miss calculations with my proposal. Unfortunately, I learned with Access, just because you want a query to come out a certain way doesn’t mean it’ll always possible. I could manually type them in, however that field wouldn’t be calculated/updated yearly. In addition, no of course no one nor do I want to manually input the age ranges for every age if you have over 1,000 Records. So, I thought of this idea. I created a separate table called, Age_Ranges_tbl with 2-fields. The first field would be name, “Ages” starting with the ages, 18 to 69 (set as the Primary Key). And the second field would be my “AgeRanges” accord to the “Age.” Again, if the age field is, “45” then the “AgeRange” field would be, “40-49.” Next, I opened up my Birthdates query & my Age_Ranges_tbl & I joined the 2 objects on the “Ages” field. I tried 2-approaches. 1) in my Birthdates query I took the “Age” field from the Age_Ranges_tbl & plugged it into the birthdates query. Tried running the query, but got the message, “Type mismatch in expression.” Even tried changing the Join Properties around, but got same error message. 2) For kicks, (although it doesn’t make sense) instead of putting the “Ages” field from the Age_Ranges_tbl into the Birthdates query I tried instead taking the “AgeRange” field from the Age_Ranges_tbl & plug in into the birthdates query, but received the same error message, “Type mismatch in expression.” I cannot understand why my first approach isn’t working in taken the, “Ages” field from the Age_Ranges_tbl & putting in the Birthdates query? I have a sense that this is all too complex for anyone to reply back. I tried my best in explaining.



Any advice/suggestions would be greatly appreciated! Thanks in advance!

[June7]

Is the Age field in the lookup table an Integer type? Build a query that calcs the age. Then use that query to join with table in another query.

Another way is to calculate the groups in query:

AgeGrp: Switch(IsNull([Age]),"unknown", [Age]<=19,"18-19", [Age]<=29,"20-29", [Age]<=39,"30-39", [Age]<=49,"40-49", [Age]<=59,"50-59", [Age]<=69,"60-69", True,"70+")

Can even build a function with that calc and call function.

[CJ_London]

also take a look at the partition function. Providing your ranges are consistent in length (perhaps make your 18-19 group 10-19) it should work for you

https://support.office.com/en-gb/art...7-c94278274dc5

[djclinton15]

Hi June, thanks for responding to my post! (You too, Ajax) June, the formula you provided I'm not sure what to exactly do with. Please allow me to give you a few details about the root of where the 'Age' fld is derived from & what I had tried. Please excuse me for repeating myself, but the Age fld is calculated fld from a Birthdate fld. With your formula (of course, I don't know what I'm doing) I opened a new query, took the Age from the Birthdate query. Next, I tried plugging in your formula & where you have unknown in quotes I assume that I need to put the name of the field which contains the ages. Either way, I received a syntax error. Might you offer me some further assistance? Thanks.

[Micron]

The problem I'm seeing with [Age]<=29,"20-20" (etc) is that 19 will evaluate to True, yet doesn't fit into that category. Not having used the Switch function, I might just be talking into my hat (whatever that expression means).

The partition function is news to me. Will have to play with it. Thanks, Ajax!

EDIT - I overlooked that [Age]<=29,"20-20" is probably supposed to be [Age]<=29,"20-29"
comment seems valid still.
I suppose you could simply have a table like
...
20 20-29
21 20-29
22 20-29
...
30 30-39
31 30-39
32 30-39
...
although not nearly as fancy as other methods.

[June7]

Sorry for typo of 20-20, yes should be 20-29. I also forgot quote mark after "70+". Edited that post. More about Switch() https://support.office.com/en-us/art...3-F47504F9E379. Might research Choose() as well.

Yes, any age under 19 will be caught in 18-19 group.

No, "unknown" is exactly that, text not field. This accommodates possibility that birthdate field is empty.

Example query:

SELECT *, DateDiff("yyyy",[BirthDate],Date()) AS Age, Switch(IsNull([Age]),"unknown",[Age]<=19,"19 and Under",[Age]<=29,"20-29",[Age]<=39,"30-39",[Age]<=49,"40-49",[Age]<=59,"50-59",[Age]<=69,"60-69",True,"70 and Over") AS AgeGrp
FROM Employees;

or

SELECT *, DateDiff("yyyy",[EffDate],Date()) AS Age, Partition([Age],0,100,10) AS AgeGrp FROM Employees;

Did you try the table approach?

[djclinton15]

June, I didn't want to leave you hanging, but I think this one is out of my league in comprehending. 1) I don't understand what you mean by creating an Age Table & something about a lookup field & building queries from that Table. What queries? Anyway, I never dealt with lookup tables. 2) I know nothing about SWITCH & never saw that word used in Access before today. 3) In your last query expression, you have, 'From Employees'---? I'm not working with Employees nor any other data. I’m not sure what to do with the formula you provided? Does it go into a query, VBA, or …? Here’s what I did. I randomly (on a website) generated over 1,000 birthdates & put them into a Database table. Next, I created a query from this table to extract the month & the year per each birthdate/Records. So, now I have over 1,000 Records containing A) Birthdates & B) Ages ranging from 18 to 69. Remember, the Age field is a calculated field from the birthdates. Pay no mind to the month field; it’s irrelevant.

I hesitate in mentioning this because it may only confuse my mission. I also randomly generated over 1,200 first & last names, which I put into a Database Table. Within this, ‘First & Last Names’ I also have a field for, ‘Birthdate’, & ‘Age’ which are blank. Actually, I created a query for the First & Last Name Table & made the ‘Age’ field to be a calculated field. So let me give you an example of why I created this random Birthdate/Age Table & a random list of first & last names. Let’s say I needed to start a company with 25- employees (ages ranging from 18-69) -&- 75-customers (ages again ranging from 18 to 69). With my employees, upon hiring date I know that I want 3-employees to be under the age of 20, 10-employees between the ages 20 to 29, 5-employees between the ages of 30-30 etc etc etc. S,o if I wanted to randomly input birthdates into my employee’s table (according to the individual employee’s ages) I want to create a query, which has the age ranges I.E. (20-20) with a sub-query having all of the birthdates between the ages of 20 & 29. Please forgive me, I’m very-very tired & am making total nonsense here. I just wanted to say thanks! I did received several suggestions, except I’m not skilled enough to excute them.

[ssanfu]

The field of ages is a calculated field from birthyear, Age: DateDiff("yyyy",DateSerial([BirthYear],1,1),Date().

If you are meaning a calculated field in a table, this is a bad thing to do. You should read: "Calculated fields belong in queries, not tables"

[June7]

1) You mentioned creating an AgeRange table (this would be a 'lookup' table for 'looking up' the appropriate range for a particular age) in your original post. I described in post 2 how to use it.

2) Switch() is an intrinsic function, I provided a link for more info, also Choose() and Partition() are intrinsic functions - have you reviewed the links?

3) Employees was a table name I picked for example, use whatever your table name is that has birthdates

Customary design is a table of Employees with birthdates. Build a query with a calculated field to determine employee age. Then another table (the 'lookup') defines the range each age should fall into. Build another query that joins the first query with the 'lookup' AgeRanges table. Now each employee is associated with a range.

If you don't have two tables, then do the age range calculation in query as already shown, using whatever table has birthdates. I showed examples of SQL statements using Switch and Partition functions. Performing calculations in queries is basic Access functionality. Have you completed an introductory tutorial book?

If you need more help, then provide database for analysis following instructions at bottom of my post.

[djclinton15]

Steve, not to bother you, but there are a few rules that I think are right. 1) I always put ALL of my calculated fields into a query; NEVER within a table. 2) When naming any Objects (Tables, Queries, Forms, etc.) NEVER use spaces between words. Me, I use underscore between words. I.E. instead of naming a Table, 'Last Names' (incorrect) I would name the Table as, 'Last_Names'. Likewise, when naming fields, NEVER use spaces. However, say I have a field named, 'LastName'. In a Query you can separate the 2-words by putting a colon ":" after the last word followed by the original field name.I.E. 'Last Name: LastName'. 3) Although this is not "a rule" per-say. I once saw a database where within the Object name was abbreviated the type of Object. Some people name there objects as, 'tblLastName', 'qrylastName', frmLastName'.

I guess, t

o many that method makes sense, however, to me it was confusing. Instead, I sort of came up with my own method by using Underscores between words & putting the abbreviated Obeject's name at the end. I.E. 'Last_Name_tbl', 'Last_Name_qry', 'Last_Name_frm'. I don't know why I shared ths, but...

[djclinton15]

June, I'm trying to work o on your suggestons & if it's okay I'll get bac to you in a few days

[djclinton15]

June, although I’m sending you this database, I still would like to go over the tables, queries, & fields with you. Understand, I abbreviate my fields but I’ll try my best in explaining. Let’s start with my 2-tables. Table-1: Birthdates_tbl with 2-fields. I have an ID/Auto Number field called, ‘BrthDtID’. I have this field set as a Primary Key, although this field is totally irrelevant. The second field in this table is the ‘Birthdates’. Table-2: Ages_Range_tbl also with 2-fields, ‘Ages’ (Primary Key) & ‘AgRngs’ (age ranges). I think this might be what you would call a lookup table? I’ll illustrate in a minute.

I also have 2-queries from the Birthdates_tbl. In Birthdates_qry1: I have all of my calculated fields off of the ‘Birthdates’ field, I.e. ‘Month’, ‘Birthyear’ & ‘Age’. These fields are also irrelevant. Now, in Birdates_qry2 is where I took the ‘Age’ field from the Birthdates_tbl -&- the ‘AgRngrs’ field from the Ages_Range_tbl. I joined the 2-queries, from the Birthdates_qry1 I have he’Age’ field & from the Ages_Range_tbl I have the ‘AgRngrs’ field. In the 2-queries joined by the ‘Age’ field & in the Join Properties dialogue box regardless of how I join these 2-fields I get the same error message, “Type mismatch in expression”. I don’t understand, please explain.

[Micron]

The error message is quite clear and descriptive, and researching it would provide tons of places that would elaborate on the meaning. In short, it means you are trying to use a data type in some way whereby the type you are trying to pass/join/insert/update/etc. is not compatible. You probably are trying to join text and number fields together - or some similar mis-match. Check the data types of the 2 joined fields. If the issue isn't there, then it's in a query field that your're trying to calculate in the same mis-matched fashion.
EDIT
This just turned a light on

‘Ages’ (Primary Key)

Definitely not!! Making a PK on volatile information goes against the cardinal rules of PK fields. Some would say to never make one out of meaningful data, but to do so on something that changes over time is worse. Or am I interpreting this wrong, i.e. no one or no thing related to that age number will ever get any older?

[June7]

@Micron, Age is not volatile in the table. It is intended to link to calculated Age field.

Might want to give fields different names to avoid this confusion. Maybe AgeID.

Age in Ages_AgeRange_tbl needs to be a Number (Integer) type. Now the join on that PK and the calculated Age works.

[djclinton15]

Thanks June, what you said makes totl sense I don't know why I had the 'Age'field as a short text & nt a number fied.