Admissions database - advice needed on calculating semester

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

re: Admissions database - advice needed on calculating semester

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" <neil40@btinternet.com> wrote in message
news:c66a9b10.0401191708.3af75fa3@posting.google.c om...[color=blue]
> 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[/color]

re: Admissions database - advice needed on calculating semester

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
resource@pcdatasheet.com
www.pcdatasheet.com


"Neil Grantham" <neil40@btinternet.com> wrote in message
news:c66a9b10.0401191708.3af75fa3@posting.google.c om...[color=blue]
> 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[/color]

re: Admissions database - advice needed on calculating semester

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

re: Admissions database - advice needed on calculating semester

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!