Results 1 to 4 of 4
  1. #1
    SmithTS86 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    2

    One-to-many table relations yielding way too many query results

    Hello,

    I am working on a database containing information about people. Each person has some basic information, such as their name and position. The Basic Information table contains these records for each person, with a unique sequential number for each person as the primary key.



    However, each person also has additional information about their previous positions, which is made up of a table with the dates and position title for each previous position they have held. I have this stored in a Positions table, where each record contains the date range, the position, and the identification number from the Basic Information Table.

    There is another table, called Education, which similarly lists the date, school, and degree of any education this person attained, as well as the identification number from the Basic Information Table.

    Finally, there is one last table, called Certifications, listing similar information on all certifications received for each person.

    I have then linked the identification numbers from each table to the Basic Information table. The problem is that when I run a query for, say, people who have a PhD degree, and want to display only their names, I get a list containing the same name many times over; if I choose to display all information, I find that I am getting back all possible combinations. So what I get returned is a list of all the previous positions combined with their first certification, then all the previous positions combined with the second certification, and so on. So rather than get twenty names (of the twenty people who have PhDs), I get thousands of entries with the twenty names repeated.

    I'm not sure if this is a problem in my table design and linking, or in my query design. If this is not clear, please let me know and I will give more concrete examples.

    Thank you for your help.

  2. #2
    CGM3 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Atlanta, GA
    Posts
    49
    Try using DISTINCT in the query. For example--

    SELECT DISTINCT LastName FROM Employees

    --will only give one occurence of SMITH no matter how employees with the last name of Smith are in the Employees table.

  3. #3
    SmithTS86 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    2
    Thanks, I think that will do what I need for now. Ultimately, I am planning to turn the records into an adjacency matrix for social network analysis, but I think this will do the trick.

    I appreciate the quick response!

  4. #4
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    Also, look up how to set up a many-to-many relationship. Normalization is the key here for you. Set up the db properly from the beginning and you will be saved many headaches in the future.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-14-2010, 08:02 PM
  2. Replies: 0
    Last Post: 02-24-2010, 12:56 AM
  3. Populate Table with query results
    By sparkyboy2406 in forum Queries
    Replies: 1
    Last Post: 02-23-2010, 03:51 PM
  4. two relations to one table?
    By kannuberg in forum Forms
    Replies: 19
    Last Post: 09-27-2009, 11:25 AM
  5. Combine Query Results to One Table
    By pr4t3ek in forum Queries
    Replies: 0
    Last Post: 12-19-2008, 06:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums