Results 1 to 6 of 6
  1. #1
    CARANJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    3

    Query doesn't pull all data from tables

    Any guesses as to why a query would not pull all the data from a table? The query is matching data from one prior query to data in another table.

    TABLE 1 - LISTS ALL OUR EMPLOYEES - 5348 records:
    employee list
    employee's budget #
    (and a bunch of other fields, not relevant to this question)

    QUERY 1:
    contains all our budget #s
    administrator assigned to oversee each budget #



    (the link is the budget #s, as you might guess)

    QUERY 2 THAT I'M ASKING ABOUT:
    results desired is a list showing all 5348 employees, their respective budget #, and the related administrator
    (purpose is to have a handy list to respond to employee budget questions... ("Oh, I see you're in budget 12345, I'll call Susan who is your admin.")

    RESULTS COME BACK AS DESIRED *BUT* WITH ONLY 4388ish RECORDS -- ??? It makes no logical sense, everything else is fine.

    Any suggestions what I should check?

    Thanks,
    Cindi
    (newcomer, first post)

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,618
    Are you sure all budget numbers in BudgetAdministrator table/query?

    Try a Find Unmatched query to see if numbers in Employees not in BudgetAdministrator. Can use the query wizard.

    This is essentially a query that joins the two datasets with join type "Include all records from employees ..." with criteria under the BudgetAdministrator budget number field of Is Null.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CARANJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    3
    You mean, if there are employees with budget #s NOT in the administrator query, then the resulting query would just skip them? That might be it--we did add a bunch of budget #s recently, employees might've been moved into them but my table doesn't have them...? I will check that, thanks, and post back.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,618
    The query won't show record if it is INNER JOIN. Use OUTER (LEFT or RIGHT) to list all employees even if there is no matching BudgetAdministrator record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847

  6. #6
    CARANJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    3
    Quote Originally Posted by CARANJ View Post
    You mean, if there are employees with budget #s NOT in the administrator query, then the resulting query would just skip them? That might be it--we did add a bunch of budget #s recently, employees might've been moved into them but my table doesn't have them...? I will check that, thanks, and post back.

    Answering my own answer, LOL... Just posting a last follow-up to confirm that, indeed, was the problem -- if there is missing information, the query simply skips the record. It returns only records that have all the requested info. Thus, employees in my table without budget numbers were ignored. When I went back to the table and updated all their info, and re-ran the query, all records were captured.

    THANK YOU VERY MUCH to you all for your assistance!
    Cindi

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

Similar Threads

  1. Replies: 8
    Last Post: 10-23-2013, 04:37 PM
  2. Query to pull multiples of same data from a table
    By mpreston14 in forum Queries
    Replies: 1
    Last Post: 05-24-2013, 02:01 AM
  3. Replies: 1
    Last Post: 01-14-2013, 11:28 AM
  4. Query doesn't return all data
    By hawzmolly in forum Queries
    Replies: 6
    Last Post: 03-26-2010, 09:12 AM
  5. Only pull out curtain data from a query
    By mela in forum Queries
    Replies: 0
    Last Post: 12-08-2009, 12:20 PM

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