Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Record explosion in Queries (97/SR2)

    This is probably something real simple that I am doing wrong. I have been wresting with it for about a week and finally gave up and am looking for help.
    I have a DB that I use to keep track of our Sunday School Students. I have the following tables:
    Families-Last Name, address, etc. with a FamilyID as the key
    Students-Information about each student, with a StudentID key, students get tied to parents with a Form/subform, there is also a grade# field.
    Grade Levels-Grade # and Grade Name, grade# is the key

    The reason I use Grade # and Name is that at the end of the year, I run an Update Query to increment the Grade# by one to move everyone up one grade.
    When I combine the 3 tables in a query, everthing runs fine, currently I have 161 active students.
    I also have another table for Grade and Group distribution. We combine grades and it can change and I have found that by having that information in a table, I never need to adjust individual grade level on student records.
    Here is my problem, when I open the query and add the Grade and Group Distribution, joining on the Grade# field, I get twice as many records as I got before.
    What am I doing wrong?
    Thanks for any help.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Record explosion in Queries (97/SR2)

    I don't understand what the "Grade and Group Distribution" table does or how it relates to the other tables. Apparently, there is a one-to-many relationship here that you aren't recognizing that is causing the extra records.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Record explosion in Queries (97/SR2)

    Mark,

    Thanks for the help. I didn't have the relationships set up properly, (and, actually, you forced me to go back and study relationships more, I think I have a fairly good understanding now) so I got that straightened out. The problem is, it wasn't the relationships that was causing the problem. No, it was good, old fashioned, knowing just enough to be dangerous!

    Each student in the DB is assigned to a grade, and then each grade is assigned to a group. Since we have 2 services, each grade actually gets assigned to 2 groups, since we don't know ahead of time which service the student will be attending, we have to include them in both. Which means when I ran the query with the groups, each student was listed twice, which doubled the number of records. Yes, I said "D'oh" when I figured it out. Thanks again for your help.

Posting Permissions

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