Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Question Unanswered: Access Specific Row Problem

    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.

  2. #2
    Join Date
    Nov 2002
    Posts
    154

    Smile Normalize instead

    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
    class
    payment

    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.

  3. #3
    Join Date
    Dec 2004
    Posts
    41

    Question

    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.

    Hope you understand and can help me.

    Thank you all!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    A listbox or subform would probably be a better choice then a normal textbox for this situation. That is assuming I'm correct that you want to display all of the classes for a given student?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2004
    Posts
    41
    Yes, but it must be in different textboxes.

    Student: name
    ID:

    .. rest of details ..

    Class 1 : Computes
    Cash : 1500$

    Class 2 : Soccerr
    Cash : ...$


    Like so.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can either use a listbox, which may look like:

    Student: Bob Dole
    ID: 3

    .. some details ..

    Class______________Cash
    International Politics___$5
    Agenda Obfuscation___$10

    or a subform (set to "continuous forms"):

    Student: Bob Dole
    ID: 3

    .. some details ..

    class1:International Politics
    Cash: $5

    Class2:Agenda Obfuscation
    Cash: $10




    You could actually get both formats using a subform, but I find listboxes to be a bit easier to manage. Either way should work out fine.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2004
    Posts
    41
    Listbox which probably be better indeed, but i'm not working on a blank project, i'm restricted with the companies terms and already built database data.

    That subform thing sounds really great, where can I learn about it?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well wait now, did you normalize the data as suggested in the first reply or not?

    Also, there's a wealth of subform tutorials all over the place... simply google for ms access subform tutorial. Also checkout the northwinds DB that comes with access as an example.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Dec 2004
    Posts
    41
    Yes, I did. It's normalized now.

    Can you link me to a good tutorial?

  10. #10
    Join Date
    Dec 2004
    Posts
    41
    Ok I managed to understand everything, thank you guys, for now.

    Might have another question later, about reports, but, THANKS!.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •