RE: [MS_AccessPros] Updating table with Age Group entry

 

John

Yahoo is doing some weird things with posts the last week or so. Hopefully,
They'll get things fixed.

Regards,

Bill

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Tuesday, July 19, 2011 3:02 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Updating table with Age Group entry

Three days for a post to show up? Ridiculous!

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John Viescas
Sent: Saturday, July 16, 2011 7:03 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Updating table with Age Group entry

Duane-

That's probably more efficient as well, but Art would need to add "from" and
"to" columns to his lookup table to make this work.

AgeGroupID AgeGroupName AgeMin AgeMax
1 18 and Under 0 18
2 19 - 21 19 21
3 22 - 25 22 25
4 26 - 30 26 30
5 30 - 35 30 35
6 36 - 40 36 40
7 41 - 49 41 49
8 50 and over 50 999

QueryA:
SELECT StudentID,
DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")) AS
Age, (SELECT AgeGroupID FROM tlkpAgeGroup WHERE
DateDiff("yyyy",[tblStudent].[DOB],Date())+Int(Format(Date(),"mmdd")<Format([tbl
Student].[DOB],"mmdd")) BETWEEN tlkpAgeGroup.AgeMin And tlkpAgeGroup.AgeMax) As
AgeGroup
FROM tblStudent

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Duane Hookom
Sent: Saturday, July 16, 2011 2:05 AM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Updating table with Age Group entry

I would not use Switch() since WHEN the age groups change, you would need to
change the expression. Consider changing your age group lookup table to contain
the AgeGroupID, FromAge, ToAge, and AgeGroupName. You can then add this table to
your query without any joins. Set the criteria under the calculated age to:

Between FromAge and ToAge

This will allow you to place the AgeGroupID and AgeGroupName in the query
output.

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
From: dbalorenzini@yahoo.com <mailto:dbalorenzini%40yahoo.com>
Date: Fri, 15 Jul 2011 10:38:10 -0700
Subject: Re: [MS_AccessPros] Updating table with Age Group entry

This works great. Is there way in Query B that I change AgeGroupID to a numeric
field so I can link it to tlkpAgeGroup table so I can pull out the AgeGroupName
field to display the name of the Age group?

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
<mailto:alorenzin%40live.com>
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: John Viescas <john@viescas.com <mailto:john%40viescas.com> >
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Friday, July 15, 2011 12:23 PM
Subject: RE: [MS_AccessPros] Updating table with Age Group entry

Art-

Try this:

QueryA:
SELECT StudentID,
DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")) AS
Age, Switch([Age] <= 18, 1, [Age] <= 21, 2, [Age] <= 25, 3, [Age] <= 30,4, [Age]
<= 35, 5, [Age] <= 40, 6, [Age] <= 49, 7, True, 8) As AgeGroup
FROM tblStudent

QueryB:
SELECT Count(StudentID) As Total, QueryA.AgeGroup
FROM QueryA
GROUP BY QueryA.AgeGroup

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Art Lorenzini
Sent: Friday, July 15, 2011 6:56 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] Updating table with Age Group entry

OK this worked up to the point I tried to add the Count:

SELECT Count(tblStudent.StudentID) AS Total,
DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")) AS
Age, Switch([Age] <= 18, 1, [Age] <= 21, 2, [Age] <= 25, 3, [Age] <= 30,4, [Age]
<= 35, 5, [Age] <= 40, 6, [Age] <= 49, 7, True, 8) As AgeGroup
FROM tblStudent

I understand tha aggregate error but how do I put the expressions into a GROUP
BY?

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
<mailto:alorenzin%40live.com>
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: John Viescas <john@viescas.com <mailto:john%40viescas.com> >
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Friday, July 15, 2011 11:47 AM
Subject: RE: [MS_AccessPros] Updating table with Age Group entry

Art-

Put those two expressions in a query on your table, then run it to see if you
like the result. You can then GROUP BY on the AgeGroupID to get what you want.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Art Lorenzini
Sent: Friday, July 15, 2011 6:37 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: Re: [MS_AccessPros] Updating table with Age Group entry

OK, that make sense but I how would I do it in a query? I need to list the age
groups and their appriopriate student counts.

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
<mailto:alorenzin%40live.com>
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: John Viescas <john@viescas.com <mailto:john%40viescas.com> >
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Sent: Friday, July 15, 2011 11:33 AM
Subject: RE: [MS_AccessPros] Updating table with Age Group entry

Art-

Because the age changes over time, you should NOT store age group in your table.
You should calculate it in a query:

Age: DateDiff("yyyy", [DOB], Date()) + Int(Format(Date(), "mmdd") <
Format([DOB], "mmdd"))

AgeGroupID: Switch([Age] <= 18, 1, [Age] <= 21, 2, [Age] <= 25, 3, [Age] <= 30,
4, [Age] <= 35, 5, [Age] <= 40, 6, [Age] <= 49, 7, True, 8)

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Art
Sent: Friday, July 15, 2011 6:12 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] Updating table with Age Group entry

I have a table called tblStudent:

tblStudent
StudentID PK
LastName
FirstName
DOB
AgeGroupID - FK to tlkpAgeGroup

The tlkpAgeGroup table contains the following records:
AgeGroupID AgeGroupName
1 18 and Under
2 19 - 21
3 22 - 25
4 26 - 30
5 30 - 35
6 36 - 40
7 41 - 49
8 50 and over

What I am trying to do is update the AgeGroupID field in tblStudent with the
appropriate number based on the DOB. Any ideas?

Thanks,

Art Lorenzini,
Sioux Falls, SD

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (11)

Recent Activity:

• New Members 8
• New Files 6

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___