I am rather new to creating databases (actually, this is my first project). I have a database that tracks training classes of students. A monthly report has to be sent out containing all classes attended that month. The table columns include: Student, Class1, Class2, Class3 etc. If I run a parameter query where criteria is
Criteria: Between [Start] And [End]
Or: Between [Start] And [End]
And so on...
The problem is this:
When this query is run, it brings up the whole record. If an individual has taken Class1 within the criteria parameters, and Class2 outside of the parameters, Class2 shows up. Is there any way to make Class2 cell blank or viceversa without making separate queries? I would like the results to appear on one table. Thanks for any help!
Beside a programming problem, you have a very serious design problem in your database. This problem of yours is called normalisation, and if you create a table like : Tab1(Student,Class1,Class2, ... , ClassN) you have no such thing like a normalised database.
I think your database should look more like this:
Student(StudID,StudName, ... ) 'other descriptive fields of a student
After that you can create a crosstab query in MS Access that will extract the data you need for your report, exactly as you like.
The primary key of table StudAttendClass is composed of all fields: StudID,ClassID,DateAttend. This means that a student can attend the same class twice the same day (at different hours). If you want this to be allowed, you shoul enter another field in StudAttendClass, HourAttend (and make it a part ok PK), or to enter values in format mm/dd/yyyy hh:ss in the datetime field DateAttend. This PK problem is a time representation design problem (it's a very common problem). But, this is entirely another subject ...
Anyway, good luck. If you have problem with your crosstab query tell us and we will help you.
Thank you for the help, but what is the point of normalization? As of right now, everything else works, and each student has one record. We schedule each new student with multiple classes upon arrival, and we have all entries on one form, to ease the process of registration. As far as I understand, only one table may be the governing source of a form. To input data seems confusing... Another issue is justifying the change with my boss, who likes to look through the master table for input errors. This would seem almost impossible with a normalized database. Like I explained earlier, I am very new at this whole process...
Thanks again for the help!
The point of normalized database is to have a database, otherwise you only have a collection of data. if you create the table as you described you don't need Access (or any other DBMS for that matter), you can do it with Excel for instance.
You will can not benefit of SQL synatx for obtainig the results you want (because the whole SQL is based on databases not on data collection). For instance try to calculate a grade average for a student, how would you do it (assuming you have antother "database table" like: StudentID, Class1Grade, Class2Grade and so on, and you only fill in with values the classes that your student attended). How would you count the number of classes that the student attended at? How do you summarize the values of grades (I hope not Class1Grade+Class2Grade+....+ClassNGrade).
You said that a form can be based on a single table. False you can create a form (in this stupid thing called MS Access) based on a table OR ON A QUERY.Yes, you can make the form that your boss is looking at based on a crosstab query -> the result is the same for your boss and you still have a database.