6 Replies Latest reply on Jul 17, 2009 10:39 AM by kunaaldesai

    value list from unrelated table

    kunaaldesai

      Title

      value list from unrelated table

      Post

      Hello guys,
      I am facing an unusual problem here. Well in order to explain you my problem, I will go through my real scenario.

      I have two tables

      1 Student Info
      2. Prize control

      Student info has all the student information like name , last name along with the points.

      -- Prize control has all the prize related information like Prize_Id, name and points that prize is worth of.

      Now I have a pickup layout which is operated when student comes and picks up a prize but there is one condition for that
      - The prize should be from prize control table

      ( when he/she picks up a prize worth some points then thso epoints are deducted from his/her account)

      I am using a popup menu to show list of prizes in this lay out.

      I do understand that for that I have to relate both student info and prize control. But how can I do that when none of them have any common field.

      I am really confused. I hope somebody can help me.<!--   / message   -->










        • 1. Re: value list from unrelated table
          comment_1
             When a specific prize is picked by a student - is it then no longer available to other students?
          • 2. Re: value list from unrelated table
            kunaaldesai
               Prize_control is basically type of the prizes  available to studens like bagpacks, towel and waterbottle and any student can pick up anyhting as far as it is within his buying capatity( I mean as far as he  ahs enough points to acquire that prize). 
            • 3. Re: value list from unrelated table
              comment_1
                 Then you should use three tables intead of just  two: Students, Prizes and Awards - the last one being a join table between Students and Prizes.
              • 4. Re: value list from unrelated table
                kunaaldesai
                  

                Thats what my problem is. How can I make a join between student and prize when there is no common field between them.

                 

                Student_ info Prize_control

                 

                 

                ID Prize_ID

                name Prize_name

                last name Points

                points

                email

                comments 

                 

                 

                Here we can  see that both tables have points field but they mean completely different in this context point in student info is the points earned by a particular student and point in prize control is points that a prize is worth of. and moreover none of them is a primary key of that table. 

                • 5. Re: value list from unrelated table
                  comment_1
                    

                  I think your problem is that the relationship between Students and Prizes is many-to-many (if I assume correctly that a student can "acquire" more than one prize). Therefore you should use a third table as the join. The fields you need for this are (roughly):

                   

                  Students:

                  • StudentID (auto-entered serial)

                  • Name 

                  ... (more fields that describe the student)...

                   

                   

                  Prizes:

                  • PrizeID (auto-entered serial)

                  • Name 

                  • Value (the "price" in points)

                  ...

                   

                  Awards:

                  • StudentID

                  • PrizeID

                  • Value (lookup from Prizes, in case the "price" changes in the future)

                  • Date

                  ...

                   

                   

                  and the two relationships:

                   

                  Students:: StudentID = Awards:: StudentID

                   

                  Prizes:: PrizeID = Awards:: PrizeID

                   

                   

                  ---

                  BTW, i believe that points earned by the student should also be recorded in the Awards table (credit instead of debit), so that you can easily get the points balance.

                  • 6. Re: value list from unrelated table
                    kunaaldesai
                      

                    Hii,

                            Thank you very mcuh . That seems to be working and I think that would be a final solution for me . Thank you very very muck for your help.