Help in creating database (1 Viewer)

mahen

Registered User.
Local time
Today, 16:50
Joined
Jan 13, 2008
Messages
56
Need help to create tables and relationship for following please.

Student table
StudentA - Id (primary key- no duplicate)
StudentB - Id (primary key- no duplicate)
StudentC - Id (primary key- no duplicate)
StudentD - Id (primary key- no duplicate)
StudentE - Id (primary key- no duplicate)
StudentF - Id (primary key- no duplicate)
StudentG - Id (primary key- no duplicate)
and so on.........

Subject & Fee table

Maths - £2.00
English - £2.00
French - £3.00
Science - £3.00

1. Student can be from same family and they get discount only in English if they have siblings.
if two children from same family they pay £3.00 (not £4.00)
if three children from same family they pay £4.00 (not £6.00)

2. One student can have many subjects.

Each family get only one receipt that include Student name, Id, subject they are taking, fees, totals.

How do I do the tables, query, relationship and the calculation.?

Thanks
- Mahen
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:50
Joined
Jul 9, 2003
Messages
12,313
In your student table are you proposing a separate column for each student?
 

mahen

Registered User.
Local time
Today, 16:50
Joined
Jan 13, 2008
Messages
56
Help on MS Access 2003

Thank you for your response

Yes. seperate column for each student. Student table contents more fileld than this. Don't know how to in enter in a table if any sibling student are. That on the entry window I should have filed for any sibling (how many) Payment will depend on that.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:50
Joined
Feb 28, 2001
Messages
18,746
You desperately need to study "Database Normalization" before taking another step. You are about to drive yourself off the cliff into the abyss of useless databases.

You have students, subjects, fees, and the chance that a student will have a sibling in the same class, thus enabling a discount.

Table: Student
StuID - prime key (autonumber unless you have a better key)
Student information such as Firstname, Lastname, Midname, AddrLine1, AddrLine2, City, etc.

Table: Courses
CrsID - prime key (autonumber unless you have a better key)
Course description information including start/end dates

Table: Roster
CrsID - foreign key to Courses
StuID - foreign key to Student
AddDate - when student was added to roster
EndDate - when student left the course (for any reason)
WhyLeft - a reason code (up to you as to how you approach it) - passed class, dropped class, died, etc.

Table: Siblings
StuID1: FK to a student
StuID2: FK to a student who is the SIBING of StuID1

Then the relationship is many/many for Student/Courses, joined via a JUNCTION table (which you can learn about by searching this forum) and filtered so that a student in last year's course won't show up in this year's roster - even though you have a record of the student taking the course.

Before befuddling you with all sorts of things that are confusing to you, I would very strongly advise some reading and research on normalization. Where you are headed is at best a minefield and at worst an intractable mess.
 

CraigDolphin

GrumpyOldMan in Training
Local time
Today, 08:50
Joined
Dec 21, 2005
Messages
1,583
You are about to drive yourself off the cliff into the abyss of useless databases.

I think I spy some of your literary tendencies sneaking into your posts DocMan :)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:50
Joined
Jul 9, 2003
Messages
12,313
LOL..........

>>>You are about to drive yourself off the cliff into the abyss of useless databases<<<

Best one I've seen in ages !

Now that puts me in mind of a little competition we could have between ourselves.
 

mahen

Registered User.
Local time
Today, 16:50
Joined
Jan 13, 2008
Messages
56
Access Help

Thank you and I will go through and come back to you.
regards,
- Mahen
 

rolaaus

Registered User.
Local time
Today, 08:50
Joined
Feb 12, 2008
Messages
84
I would use the table structure doc_man has posted, with one exception. the sibling table only needs to be a field in the student table that points to the ID of another student that is the first students 'sibling'. This is a self-reference, or a field in a table that refers to the unique field of the same table.
 

mahen

Registered User.
Local time
Today, 16:50
Joined
Jan 13, 2008
Messages
56
Hi Rolaaus,
Could you explain to me bit more in detail please.
regards,
- Mahen
 

rolaaus

Registered User.
Local time
Today, 08:50
Joined
Feb 12, 2008
Messages
84
Table: Student
StuID - prime key (autonumber unless you have a better key)
Student information such as Firstname, Lastname, Midname, AddrLine1, AddrLine2, City, etc.

Table: Courses
CrsID - prime key (autonumber unless you have a better key)
Course description information including start/end dates

Table: Roster
CrsID - foreign key to Courses
StuID - foreign key to Student
AddDate - when student was added to roster
EndDate - when student left the course (for any reason)
WhyLeft - a reason code (up to you as to how you approach it) - passed class, dropped class, died, etc.

The only thing I would change is;
Table: Student
StuID - prime key (autonumber unless you have a better key)
Firstname
Lastname
Midname
AddrLine1
AddrLine2
City
State
Zip
Sibling - Foreign Key (look-up) to StuID

There is however, one problem with this scenario. You would have to enter a person without a sibling, then enter the sibling, then come back and add the sibling into the original person (or create a procedure that would update the 'Sibling' field of the first person, when you enter the 2nd person and add the 1st person as a sibling to them.

Also, this would not address scenarios where there might be more than 2 people who are siblings (ie. triplets). I believe Access 2007 started using multi-valued fields, which means you could add more than 1 StuID in the 'Sibling' field at the same time, but you may not need that much integration, if you are doing this to allot for "family discounts" - if they only get a discount for adding more than 1 child (in other words, only 1 price discount, instead of an additonal price discount for each additional child).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:50
Joined
Feb 28, 2001
Messages
18,746
Generally, self-cross-referencing tables are best handled by a junction table that has the same table on both sides of the junction. You can then have as many sibs as needed by making one entry per sibling to be counted.

I.e. Joe has a StuID. His brother Bob has a StuID. In the Sibling table , you would have a record showing {Joe's StuID, Bob's StuID} ... AND to keep it from getting insane, add a record showing {Bob's StuID, Joe's StuID} - since sibling relationships are bidirectional. This has the negative result that you need to add two entries per sibling relationship, but makes it easy to find sibling counts by doing a DCount() for left-hand StuID. If the person has a counted sib, the Dcount will be not less than one. If the person has no sib,
the count will be zero.
 

Users who are viewing this thread

Top Bottom