Hey Guys, first of all let me say Thanks, in advance. You are all great and I appreciate your help.
I have a table, built like this:
Class 1 | Payment 1 | Class 2 | Payment 2 | Class 3 | Payment 3
The classes are being selected by a list of options. Each student is given a class and a payment for that class.
Here is example of data
John | Class 1 -> computers payment 1 -> $1000
Shawn | Class 2 -> computers payment 2 -> $1000
now, I have a query which can grab all people, that are signed to computers class.
select * from table, where class 1 or class 2 or class 3 = computers
In addition for taking the class name, i need to take the amount of cash
paid. my problem is, the it changes, sometimes computers may be class 2
and sometimes it can be 3. I need to take the right payment row, according
to which class it is, so I can calculate the right amount of cash.
right now, the query works as I shown below, and in the payment field, I always output Payment 1 thru the report itself, by using a textbox which is linked to that field, although some users as you've seen have their computers class as class 2, so the query takes the wrong amount of cash.
Thank you for reading so far, and thank you for your help, you're a life saver.
The biggest problem that I can see is that you are making a common mistake. What you should do instead is have two tables, the student table and the payment table, which are hooked together in a one to many relationship (connected by studentid or whatever the key for the student table is). The idea in a properly normalized database is only to have items that deal directly with the table's key in that table, so that retyping of essentially the same field would not have to occur. So, your payment table would look like this:
paymentid--the key--an autonumber in this case
studentid--foreign key, the key of the student table
You could even forgo the paymentid and make a compound key of studentid and class if a particular student could only ever sign up for a particular class once. Then, all you would have to do for your SQL would be to write something like SELECT * FROM tblPayment WHERE (class = such and such or class = another thing or ...). I have found that staying away from making multiple fields that are basically holding the same info to be the best policy. Best of luck to you.
Thank you, that helped me and now i'm up and running, but now I found another problem which I hope you guys will be able to help me solve.
Now I have everything just as 'feetdontfailme' said, I got a payment id, and multiple students can be signed on multiple classes etc.
Although, let's say I have
John (id=50) computers 1500$
John (id=50) soccerr 400$
the query I use in my form takes the first results and put it in the form feilds just fine, what i need to do now, is to determine whether it's the second, or maybe third class of the user, and then insert it into the other fields.
Now they all display the info of the first class, and I have no idea how to reach the rest.