I need help Pls!!!

[darksniper]

I am trying to make a report to check what classes the student are enrolled in, how did the students pay, per month or per class or they are not enrolled with any classes.


here is a better example of what I would like in a report.
for the report of active pay and expired pay students

Martial Arts Student Class Report:

Student Name: John
Status: Account Active
(when all classes are active, preferably if I could make the font green)
Enrolled in: Judo Expires: 45 Days
Enrolled in: Aiki Ju Jitsu Expires: 5 Days


Student Name: Kristin
Status: Account Active
(when some classes are active, preferably if I could make the font yellow of " Account Active")
Enrolled in: Judo Expired: 10 Days
Enrolled in: Aiki Ju Jitsu Expires: 14 Days

Student Name: Don
Status: Account Expired
(when none classes are active, preferably if I could make the font red of " Account Active")
Enrolled in: Judo Expired: 10 Days ago
Enrolled in: Iai Jitsu Expired: 14 Days ago

Student Name: Jess
Status: Account Inactive
(when no classes are registered, preferably if I could make the font grey of " Account Active")
Reason: Student Went to school.

Student Name: Jessica
Status: Account Active
Enrolled in: Judo Expires in: 6 classes
Last Class was on: 8-July-2005

----

Thanx.

 

[The_Doc_Man]

Perfectly possible but will require a complex set of tables.

You need tables for students, classes, and enrollments. The latter will be an example of what we call a junction table.

The classes or the enrollments will include date data. Depends on whether you count a class starting in July for a given starting level to be the same or different as a class staring in October for the same starting level of student.

Depending on the answer to that question, you would put the expiration date either in the enrollment table or the class table. The enrollment table would include student ID, which should be invariant for the lifetime of the student and the class ID, which depends on the aforementioned choice.

I would also study "Normalization" to understand where to put the date - and WHY you put it there.

 

[darksniper]

so I need a main table where I enter all of the info, and then it divides the info and puts them into diff tables. so I need to join fields in diff tables.

main input class table
-students table
-classes table
-enrollments table.

so that is 4 tables

and is there a tutorial for "Normalization"

 

[ScottGem]

There is plenty of info on normalization available, I suggest you google the word for tutorials and articles.

No you don't enter info in a main table and it divides it. You create tables that logical group information in a relational and normalized design. You then create forms bound to those tables and input directly to them. You can use subforms if you want to enter data into mutliple tables from one form.

 

[The_Doc_Man]

Scott has it right. Google "Database Normalization" and select any good university or college site as your primary source. You will see references to "normal forms." This is a reference to the "degree of normalization" of your database. If you can get to at least 3rd normal form, you are in great shape. Forms for 4th & 5th exist as well as some highly specialized forms. But it is rare to need them. Get to 3rd and worry about the others only as an afterthought.

Now, here is something to remember. Access will NEVER do anything you didn't ask it to do. It won't store anything you didn't set up with a repository for your desired data. If you want to "mine" your data, you get to store the "nuggets" of information. What you get from a relational database includes accidental or intentional correlation of data you didn't expect to be correlated.

If you don't design a split table yourself, you won't have a split table. If you don't design a JOIN, you won't have a JOIN. Tain't automatic. Other themes like that abound in Access. Strictly do-it-yourself.

one hint, though. Access Wizards can often help design things. Be warned, they are dumber than a box of rocks, but they can build a nice basic structure that you can later come back and customize. I do that all the time. Use them. Just don't trust them farther than you can throw your CPU enclosure.

 

[ScottGem]

Let me add my concurrence with what Doc_man said. The Access wizards are great at setting up a basic structure. I use them a great deal as a shortcut to building forms, reports, etc. But I always go in and do a degree of customization after I let the wizards do their magic. At the very least I need to rename the controls according to naming conventions.

 

[darksniper]

I have read the normalization, to lvl 3, so my final table will have be just a table to relations with numbers, but how can I make a report from that that will look like in my main post.

here is what I have understood from the normalization lvl 3

Table 1

id: name:
1 user 1
2 user 3

Table 2

id: activity:
1 judo
2 iai do
3 aki budo

final Table 3

id: name_id: Activity_id:
1 1 1
2 1 2
3 2 1
4 2 3




I still dont understand how can I can I make a report from the final table, that will do, what I have in my origianal post,

- I need the report to display the name only once, then make a sub category and show all classes the user is unrolled in.

so that when the report reads table 3 it would make something like that

Report:

user 1
. Judo
. iai do

user 3
. judo
. aiki budo

that is like the simplest version of what I need.

the full version of what I need is

Martial Arts Student Class Report:

Student Name: John
Status: Account Active
(when all classes are active, preferably if I could make the font green)
Enrolled in: Judo Expires: 45 Days
Enrolled in: Aiki Ju Jitsu Expires: 5 Days


Student Name: Kristin
Status: Account Active
(when some classes are active, preferably if I could make the font yellow of " Account Active")
Enrolled in: Judo Expired: 10 Days
Enrolled in: Aiki Ju Jitsu Expires: 14 Days

Student Name: Don
Status: Account Expired
(when none classes are active, preferably if I could make the font red of " Account Active")
Enrolled in: Judo Expired: 10 Days ago
Enrolled in: Iai Jitsu Expired: 14 Days ago

Student Name: Jess
Status: Account Inactive
(when no classes are registered, preferably if I could make the font grey of " Account Active")
Reason: Student Went to school.

Student Name: Jessica
Status: Account Active
Enrolled in: Judo Expires in: 6 classes
Last Class was on: 8-July-2005

so in other words
- the user enter when a payment expires then ms acess calculates the ammount of days left untill the payment expires then displays in the the report for each cource.
If the payment is allready expired then the ms access calculates how many days ago it expired and displays it.
-If the student pays by classes, then the program looks at how many classes student came in and then displays in the report how many left.
-allso the colors are different red, yellow, green, grey. Depending is the srudent have payd or not,

I can make a table for the colors, classes,.. but how can I tell it to make a report, with color code everething, and display how many days left, or how many classes left for selected cource, but the student name is diplayed only once, basically the way it is in the report.


If I could write it in Java, I would, make a small prog that does everthing, but I am totally not familiar with ms access.

 

[ScottGem]

First it would be better if you named your tables more descriptively. Its not clear whether you have your tables correct or not. You would start with something like this:

tblStudents
StudentID (PK Autonumber)
First
Last
other info about student

tblCourses
CourseID (PK Autonumber)
Coursename

Several things are still not clear. Do you need to schedule classes? Does a student get assigned to a specific class being given at a specific time or just a notation that they have taken that class? What about a payments table? How does status work? what are the business rules on expiration?

A lot of the things you are asking about would need answers whether you are developing in Java, C++ or Access.

Once you get your tables setup properly. Then you would need to create queries that return the data you want. You can then produce reports based on those queries. The report you ar asking for is fairly easy to do once you have the queries setup. To setup the queries you need your table structure setup properly.

 

[darksniper]

here I will explain it a bit different way, for example I am the owner of some small isp, and I make dayly report of all of my users, I have some users that use dial up internet, high speed,.. some pay by hours, some pay by month and some pay annually.

I need to make a report that will have the name of all users once, diplay all services that they use from me, and for each service they use, the report should say when it will expire if it didnt expire yet,or how many hours left.

so in other words i need to do the same thing with martial arts classes and students, I just thought isp example would be better so that everyone would understand.

so here is what I have, I have a table where I input all of the student information, adress, and everething.

the other table takes the student id and the student name, and I list all the classes he or she is enrolled in, then the other table reads the student id, name, and enrolled classes. and then I choose the method of paymnet. I could have made it a bit simpler, but I need to do it for normalization, so that this table would look something like this

id:..Sudent id:..Cource id:..Method of Paymnet:.. Lenght of payment:.. Expire date:...date of payment:
1...1............2.................Monthly....................1......................12-jul-05................12-jun-05
2...1............3.................Per Class...................10........................01-jan-06...........6-may-05
3...2............1.................Monthly.....................12........................05-sep-06..........5-sep-05

and so on

where
student id refers to student name
cource id refers to the name of the cource

and so when I do the report, I need it to show only one name, all the classes they are enrolled in, and how many days for each class left "day expired - current day"and for the classes the teacher will add then in separetly every time the show up to class. so in other words the report should look like the ones in my previous post.

 

[ScottGem]

First, instead of using what you think is an apt analogy, why not just explain your actual situation? I understand your situation. The ISP example didn't help at all. You have students taking classes and you want a report, by student, of the classes they have enrolled in and when they expire.

However, I pointed out several things that were unclear. You didn't respond to any of them. You need to clear up whether you want to record enrollment in courses or specific classes. You need to explain what the rules are on expiration. A subscription to an ISP is different from enrollment in a class. The ISP subscription is open ended. It may expire after its billing period. Classes don't work that way. A student signs up for a class that generally has a duration.

I've told you that you can do the report you want. Ive given you some broad instructions on how. We cannot give you specifics because you are leaving several things unclear. In other words, you need to help us help you. If we ask for specific info, then give it to us.

 

[Sergeant]

What Scott said.

Also, darksniper, I think you are still missing the concept of the relational database:
You store related data in separate, related tables.
You tie what you want back together with a query.
You use this query as the data source for your report.
In the report, you can use nifty features like groupings, so that parent fields will only be printed once and will be followed by successive values from the child records.

Also, look at the term above "business rules". If you have constants, store them and use them. Don't store the "datePaid" and the "duration", and the "expiryDate". That is just bulky and plain unnecessary. (There are circumstances where you might want to do it that way, but they would also involve business rules to define them...which you haven't defined here.)

Take it one step at a time: define your rules, get your data set up properly in the tables, THEN make a query, THEN move on to a report.

 

[darksniper]

my martial arts teacher cant remember every time when the student owes hime money and for which class, so he want just to go on the computer and look at the reports that is sorted by students and then it show all of the martial arts classes the students are enrolled in, ex:"karate, judo, aiki budo..." and for every martial art class that the student is enrolled in, it should display if the students owes the teacher money for the class or not, or when will the students payment for the class will expire.

here are terms of payment:
montly ->pay by month or 2 month etc
per classes -> pay for 6 classes, 12 classes, 24 classes etc...

-this is the "main table" where everthing is goin to be entered and stored

ID/student # 1,2,3...
Categorie/Martial arts class category, can be multiple, ex:Judo,Karate...
Start date: the date the student joned the this martial arts school.
family name:
First name:
adress
e-mail
cell phone
work phone
home phone
birth date
profession
student:y/n
school establishement: if a student is set to yes
minor: y/n
parents name: if minor is yes
emergency contact name:
emergency contact phone #:
reference:
other disciplines: like any other martial arts classes the student took.
Comments:

from the main talble it will populate another table with

Student Name:
Martial Arts classes:

and the for each martial arts class the teacher will enter date of payment and method of payment (by month or by # of classes) and if it is by month then the teacher enters when the month will expire. if it is by class then the teacher enters how many classes left and the date when the student showed up last time for that class.

then from that there will be a report sorted by student name: and the martial arts classes the student is enrolled in. and for each martial arts class it will indicate how many days left untill student payment is expired if the student payed by month (Date expired - Current date), if the student payed by classes then it show how many classes left for each corse the student is enrolled in.

like this

Martial Arts Student Class Report:

Student Name: John
Status: Account Active
(when all classes are active, preferably if I could make the font green)
Enrolled in: Judo Expires: 45 Days
Enrolled in: Aiki Ju Jitsu Expires: 5 Days


Student Name: Kristin
Status: Account Active
(when some classes are active, preferably if I could make the font yellow of " Account Active")
Enrolled in: Judo Expired: 10 Days
Enrolled in: Aiki Ju Jitsu Expires: 14 Days

Student Name: Don
Status: Account Expired
(when none classes are active, preferably if I could make the font red of " Account Active")
Enrolled in: Judo Expired: 10 Days ago
Enrolled in: Iai Jitsu Expired: 14 Days ago

Student Name: Jess
Status: Account Inactive
(when no classes are registered, preferably if I could make the font grey of " Account Active")
Reason: Student Went to school.

Student Name: Jessica
Status: Account Active
Enrolled in: Judo Expires in: 6 classes
Last Class was on: 8-July-2005



another report that is sorted by cources Ex(Judo, Karate...) and shows the student in each class.

when I meantion class or cource I mean (Judo, Aiki ju jitsu, karate etc...)
when I say classes left I mean, if the student payed for 12 classes and showed up for 2, the he has 10 classes left, if there is anything else that is not understood ask me.

and the other thing right now I just have the "main table", I didnt create any other tables.

I need all the ability to search for students by name, for example if the student has payed again,

and I dont know if it is possible to email reports to teacher e-mail, any report...

 

[Sergeant]

from the main talble it will populate another table with

Student Name:
Martial Arts classes:

This is what I'm talking about in my post above. We don't have a student table and then populate another table with the data. You really need to realize how this works before you can embark on a full-scale project like you are wanting to do. Things are stored in different tables by functional division of the fields involved: People table holds only info that describes each person; We draw the line at any piece of information that ties people together, like what classes they are enrolled in; We make a table of class enrollment to document what classes people are/were enrolled in; We make a table of classes to define those classes. We might even make another table to track payment history and produce enrollment status.
Once we have that set up, we don't need to "populate" another table...all we need to do is query the tables properly. We can get any data, arranged any way we want, using a query (or two, or three).

Under the heading of business rules, you need to think about: Will we ever offer a discount? Will we have specials? Will special rates apply only to certain timespans? Will special rates not apply to paid-up members? Will the rates (price/duration per class) ever change? When and to whom do we want to apply the changed rates? Are there different rates for differently qualified people? Do you just want to compute these factors in your mind each time and just manually enter the expiry date?
Databases are good at storing data if we set them up right...but most people want more than just "storing data"...you could use a spreadsheet for that. Most people want a management SOLUTION that keeps them from having to really think about all the aspects that feed into it.

I encourage you to read and understand the advice that people here are giving you. You seem to be missing some of the finer points being made. The bottom line is this: IT ALL STARTS WITH TABLES! Get those right, and everything else goes swimmingly.

 

[ScottGem]

Ok, first we know what report you wanted, you don't need to keep repeating it.

You need to normalize your data better:

tblStudents
StudentID (PK autonumber)
FamilyName
Firstname
Address
City
State
Postalcode
E-mail
DOB
OccupationID (FK to an occupation lookup table)
SchoolID (FK to a school lookup table)
EmergencyContactID

You don't need the Y/N, if the SchoolID is null then they aren't student.
You don't need Minor, that can be calculated from DOB. You should have an emergency contacts table and just use the ID to link them. Other discliplines should be a Join table. Phone Numbers should be in a separate table like this:

tblPhones
PhoneID (PK Autonumber)
PhoneTypeID (FK to Phone type lookup)
PhoneNumber

Next you need another table like this:

tblActivity
ActivityID (PK Autonumber)
StudentID (FK)
ActivityTypeID (FK)
StartDate
PaymentMethodID
PaymentTerm

Once you have data in that strucutre, you can build a query joining tblStudents to tblActivity and list each current activity and when it experies.

 

[darksniper]

ok, I got the tables made, I am working on linking them all up to together. and allso, how can I make it so if occupation is not a student , school is blanked out, and the same thing with minor field.

Allso I am reading reading the training guide from microsoft, so i could do more stuff, and understand how to use them.

 

[jsanders]

Dark,
Don’t take this wrong please.
But you’re not using what you learned in Judo.
You need to use your opponent against himself. This time it’s the organization of data.

I always recommend this series of books to any one getting started in a new program.

Access for Dummies
http://www.amazon.com/exec/obidos/s...ps-1_stripbooks_4203429_2/002-4058809-0764058

You will find the Microsoft books nearly impossible to understand. Try the dummies books first

For a man looking for an Ippon must first learn to fall, and then an OSoto Gari or a Hiza Garuma wil come naturally.


Get the basics before you Kiai

Hajime,

Joe

 

[ScottGem]

ok, I got the tables made, I am working on linking them all up to together. and allso, how can I make it so if occupation is not a student , school is blanked out, and the same thing with minor field.

Allso I am reading reading the training guide from microsoft, so i could do more stuff, and understand how to use them.

You shouldn't have to "work" on linking them. As soon as you add the foreign key field, you have your link. You can formalize the relations in the relations window, but that's not absolutely necessary.

What you need do is set the conditional controls to either not visible or not enabled and then change them in the After Update event of the choice controls.

For example: you would have a combobox for occupations (cboOccupation) and another for schools (cboSchool). Set the Visible property of cboSchool to No. In the After Update event of cboOccupation use the following code:

If Me!cboOccupation = student Then
Me!cboSchool.Visible = True
Else
Me!cboSchool.Visible = False
End If

Note: for student substitute the PK value for student from the Occupations table. You need to make sure you are comparing to what is actually stored with the control.

I already told you, that you don't need a "minor" field. You can derive minor from the DOB.

One last point, you don't place fields on forms. Fields are the individual pieces of info that make up a record. You have controls on forms. Controls may or may not be bound to a field in a table. This is a subtel but important distinction.

 

[darksniper]

The book I am trying to read is by Trillium Consultants, allso how do I add (FK), and where do I put the code you just gave me, to show or hide sertain fields.

---

DarkSniper...

 

[ScottGem]

Again, a FK (foreign key) is just another field in a table. It needs to be the same datatype as the field it will link to. As for where to put that code, reread the note it tells you.

 

[darksniper]

here is the diagram, tell me if I made a mistake, because I get a data integrity error, when I try to add data to one table and view it in another.