#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    3
    Rep Power
    0

    Ms Access Xp - Aotu Fill Of Fields In Form On Certain Criteria


    Please help me, i am new to db, I am still trying to finish a db for my school who will be hosting the inter-school atheletic meet. I have 3 table:-

    tblStudents : id, name, paricipant no, division, and checkboxs for events 100m, 200m, 400m, 800m, 4 x 100m relay, high jump. and empty feilds for event no, heats no, lane no.

    tblDivision : division id, Boys1, Boys2, Boy3, Girls1, Girls2, Girls3

    tblSchools : school id, school name, tel. no.

    i have made individual query to sort students taking part in for every event.

    I have a form for each query, with the empty feilds too.

    for eg. the form will show records for students in event 100m sorted by each division. Then 1 filter by say Boys1,
    it shows 25 students taking part.
    Now i have to type in the event no. in one coloum for all records which is the same. Is ther a way to just fill once and it automatically fills the rest.

    Next, i have to divide the 25 students in to heats, not more than 8 per heat. and fill in the coloum for heats as Heat1 for 7 students,Heat2 for another 6 students,Heat3 for another 6 students & Heat4 another 6 students for a event distribution. Only one creteria no two schools can be in the same heat.
    Arter which i will have to assing the lane for students of the individual heats.

    Is there a way this second part be done by access by itself.

    Very sorry for the long text, I would appreciate if anyone can try my db & can anyone chat to me too on this matter as i am learning the basics. My thanks in advance.
  2. #2
  3. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,817
    Rep Power
    1099
    Is ther a way to just fill once and it automatically fills the rest.
    You can use an Update Query. Basically you'll update a particular field with a specified value based on certain conditions.

    As for the other part, I think there would have to be some programming involved b/c it requires conditionals, but I could be wrong about that.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    3
    Rep Power
    0
    Thanks for ur kind response, i am not sure how to put the right string on the control data or at the grid for the update query. would appreciate help on it.
  6. #4
  7. mod_dev_shed
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2002
    Location
    Atlanta, GA
    Posts
    14,817
    Rep Power
    1099
    MS Access is so much more difficult to help somebody do something in b/c I can't just give you code, but what the hell, here goes:

    I'm making the assumption that you have set up any relationships b/w the tables already and that you did this correctly.

    In your database, go to Queries and Create query in Design view. Select the tables that have fields that you need for the query. Tht is, any table where a field must be equal to a particular value or a field that you want to update. At the top, on the toolbar, there will be an icon that by defualt, looks like two tables, one on top of the other -- it will say Query Type when you mouse over it. Select the arrow next to it and choose Update Query. The labels to the left of the grid change. Under the appropriate column, set the Update To grid to the even number. Under the other columns set Criteria to whatever you need it to be. Any criteria on the same line gets AND applied to it, which is what you'll need here I belive. Then you can run the query.

    I'm sorry I can't be real specific here as to what to set what field -- you'll just have to play with that a little, so please please please have a backup before you do this -- results could get ugly.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    3
    Rep Power
    0
    thanks jharnois, i will try it later, as i am going to a attend a freinds wedding now.

IMN logo majestic logo threadwatch logo seochat tools logo