School Database

[Anwaar Ul Hassan]

Hello everybody,

I am creating a school database. I am new to MS Access. I need to create
database for monthly fee receiving. As you know that there is academic
session. I need to create dab for a particular session say 2008-2009. How do
I create table and make their relations. Do I create a seperate table for
months and seperate tabel for sessions. Do I have to keep on adding sessions
or is there any that sessions add up automatically. Say if I create a tabel
for sessions and I add just 2008-2009. Is it possible that sessions add
automatically.

 

[tina]

being new to Access, you need to learn the basics of relational design
principles *first*. then you need to analyze your process, so you can apply
those principles to building the tables and relationships you need to house
your data. when that base structure is in place, then you move on to
building queries, forms, and reports so your user can interact with the
data. to get started, see http://home.att.net/~california.db/tips.html,
focusing first on tips 1 and 2.

hth

 

[Linq Adams via AccessMonster.com]

The link Tina gave you is an excellent source for learning the basics of
relationships and normalization of databases. Here's a couple more for after
you get a handle on that. These go into the nuts and bolts of developing a
database:

Chrystal has an excellent “basics†tutorial:

http://www.accessmvp.com/strive4peace

This is a little more advanced, but still a great starting point, having 23
well written, well defined, clearly named chapters.
:
http://www.functionx.com/vbaccess/index.htm

 

[Anwaar Ul Hassan]

Well thanks for the reply and these links are really useful to me. What I am
asking is I have four tables now that is, a students table(name of all
students), a months table(name of months that is April to March. This is one
session.), a session table(like session 2008-2009, 2009-2010......), and a
fee table(in which I create fields like students name, session,months,
monthly fee, annual fee, admission fee, and received by). I read that access
doesnt support many to many relation so I have created another table with the
name "months detail" in which I have given session ID (1 which is 2008-2009
and the names of 12 months and 2 which 2009-2010 and the name s of 12
months). How should I relate them and do I have to make another table or not.

 

[tina]

okay, follows my tables/relationships suggestions. in some instances, it may
be the same as what you already have - i'll leave it to you to compare my
suggestions with what you have built, and where there are differences,
decide which setup more closely mirrors your real-world process, while
following relational design principles.

tblStudents
StudentID (primary key)
FirstName
LastName
<other fields that describe a student>

tblSessions
SessionID (primary key)
SessionDescription (this would be the 2008/09, 2009/10, etc. data; one
session year = one record)

tblMonths
MonthID (primary key)
MonthName
<your post suggested that a session year runs from April of one year through
March of the next year. if so, i'd populate the list as
MonthID MonthName
1 April
2 May
3 June
4 July
5 August
6 September
7 October
8 November
9 December
10 January
11 February
12 March

that way you can sort the list, for reports and such, by MonthID, and get a
sort that is true to your session year.

tblFees
FeeID
FeeName
<any other fields that specifically describe a fee>

tblTransactions
TransID (primary key)
Description (such as charge, payment, debit, credit)

tblEmployees
EmpID (primary key)
FirstName
LastName
<other fields that describe an employee>

tblStudentFees
StuFeeID (primary key)
StudentID (foreign key from tblStudents)
SessionID (foreign key from tblSessions)
MonthID (foreign key from tblMonths)
FeeID (foreign key from tblFees)
TransID (foreign key from tblTransactions)
Amount
TransactionDate
EmpID (foreign key from tblEmployees)
<the last field, EmpID, would identify the "received by" employee, whoever
handled the transaction>

note that i didn't tie months directly to sessions, and included key fields
from both tables in the student fees table. i did that because the list of
fees you posted suggests that not all fees will be assessed monthly -
"annual fee, admission fee".

i have little practical experience with setting up a database that tracks
money transactions, so it's very likely that i'm off the mark here, from an
accounting standpoint. but someone with more experience may read the thread
and offer better suggestions.

hth