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!