By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 1,032 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

Admissions database - advice needed on calculating semester

P: n/a
I am creating a database for Nursery children, and part of the
reporting requirement, is to show those on the waiting list.

Children will be elligible for entry by age, and so based on their
date of birth, I would like a report that has calculated which intake
semester the child falls under.
So, children born 1-Sep to 31-Dec are Autumn, 1-Jan to 31-Mar are
Spring, and 1-Apr to 31-Aug are Summer

Somewhere in my database I'd like to record the childs intake semester
and output a report of those falling under the headings of autumn,
spring and summer. I need a calculation somewhere that records these
based on the childs date of birth.
I realise I can't have a table field calculated from another field
(can I?), so am seeking guidance on where and how best to store this.

I hope that is clear enough.

Regards
Neil
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
TC
In general you do not store "calculatable" values in a relational database
like Access. You calculate them, "on demand", typically via a stored query.

For example, you would never store a person's age. You would store their
date of birth, then calculate their age with a query like:

SELECT tblPersonDetails.*, (Date() - tblPersonDetails.DateOfBirth)/356 AS
Age
FROM tblPersonDetails

(That calculation is not quite correct for determining a person's age. But
I'm sure you see what I am getting at there.)

So, you need to learn about creating & saving queries, and doing
calculations within queries (as shown above). Also using functions like
IIF() within queries. Look all this up in online help.

HTH,
TC
"Neil Grantham" <ne****@btinternet.com> wrote in message
news:c6**************************@posting.google.c om...
I am creating a database for Nursery children, and part of the
reporting requirement, is to show those on the waiting list.

Children will be elligible for entry by age, and so based on their
date of birth, I would like a report that has calculated which intake
semester the child falls under.
So, children born 1-Sep to 31-Dec are Autumn, 1-Jan to 31-Mar are
Spring, and 1-Apr to 31-Aug are Summer

Somewhere in my database I'd like to record the childs intake semester
and output a report of those falling under the headings of autumn,
spring and summer. I need a calculation somewhere that records these
based on the childs date of birth.
I realise I can't have a table field calculated from another field
(can I?), so am seeking guidance on where and how best to store this.

I hope that is clear enough.

Regards
Neil

Nov 12 '05 #2

P: n/a
Neil,

Put the following function in a standard module:

Function GetIntakeSemester(ChildDOB) As String
Select Case Month(ChildDOB)
Case 1-3
GetIntakeSemester = "Spring"
Case 4-9
GetIntakeSemester = "Summer"
Case 10-12
GetIntakeSemester = "Autumn"
End Select
End Function

Now create a query that includes the DOB. In an empty column in the query put:
IntakeSemester:GetIntakeSemester(DOB)
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Neil Grantham" <ne****@btinternet.com> wrote in message
news:c6**************************@posting.google.c om...
I am creating a database for Nursery children, and part of the
reporting requirement, is to show those on the waiting list.

Children will be elligible for entry by age, and so based on their
date of birth, I would like a report that has calculated which intake
semester the child falls under.
So, children born 1-Sep to 31-Dec are Autumn, 1-Jan to 31-Mar are
Spring, and 1-Apr to 31-Aug are Summer

Somewhere in my database I'd like to record the childs intake semester
and output a report of those falling under the headings of autumn,
spring and summer. I need a calculation somewhere that records these
based on the childs date of birth.
I realise I can't have a table field calculated from another field
(can I?), so am seeking guidance on where and how best to store this.

I hope that is clear enough.

Regards
Neil

Nov 12 '05 #3

P: n/a
Neil,

I am also working on a Nursery database and have a function (to do
with calculating fees) you may be interested in, email me via gillian
dot l dot mitchell at talk21 dot com(no spaces and replace dot with
correct punctuation)

David
Nov 12 '05 #4

P: n/a
David,

I replied to you privately a couple of days after you posted this, as I
was very interested to hear about your Database.
I've yet to hear from you, I hope I interpreted your email address, it
certainly didn't bounce
Neil

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.