Results 1 to 15 of 15
  1. #1
    jediskipdogg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    7

    Query Not Showing All Records

    Consider me a self-taught Access newbie. I work for a company analyzing traffic crash data. I have done pretty good with using it to access an SQL database that holds all of our data.



    However, I am now trying to create a large query to pull data that a roads department is looking for. The main table is basically multiple fields with numbers. Those numbers all link to separate tables that convert the numbers into description words (we will call these code tables). The issue I am having is not all fields in the main table are always filled out. Therefore, of the over 9000 crashes in our system, only 27 are currently being returned as having everything filled out.

    From the Query Design View in the Criteria field I have added Is Null Or IS Not Null and it is still not returning anything additional. I read elsewhere that people said right click and do Join Properties on the lines between the two tables. Then selecting option 2 which selects all records from the main table and only those from the code table where joined fields are equal. I still end up only with 27 records.

    How do I get it to return all the records even if the main table has a blank field?

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    How many tables are you joining here?

    You want to start with your "main table", the one that has ALL the records you want to return (even if it does not have all the fields).
    Then, join the other tables to this "main table", using the right join to include ALL the records from the "main table", and just the ones from the other that match.
    Do NOT place any criteria on any fields yet. That will only reduce the number of records you return.
    Once you have that working correctly, you can then play around with Criteria, but only if you need to reduce the number of records being returned in the query.

    To handle NULL values created by your join, you can use the NZ function (see: https://www.techonthenet.com/access/...dvanced/nz.php)

    So say you wanted to return "Field1" from "Table2", and are getting NULLS where there is no match.
    Instead of return "Field1", use a calculated field something like this:
    Code:
    NZ([Table2].[Field1],"Not found")

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post sample of your data as well as the query SQL. If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  4. #4
    jediskipdogg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    7
    Joe,

    Right now I am joining one main table with 7 code tables that say what the numbers in the main table equal. Right now the main table has 8627 records in it but not all the fields are always filled out as it depends on what caused the crash and where it occurred.

    Here is the SQL code view if that helps at all...

    SELECT POLMGR_P_ACCID_OH_TAB.ACCID_NO, POLMGR_P_ACCID_OH_TAB.LATITUDE, POLMGR_P_ACCID_OH_TAB.LONGITUDE, POLMGR_P_ACCID_OH_TAB.ACCID_DTE, POLMGR_P_CRASH_SEVERITY_CD_VIEW.column_value_desc, POLMGR_P_ACCID_OH_TAB.DIR_FROM_REF_CARDINAL_CD, POLMGR_P_ACCID_OH_TAB.LOCATION_ROUTE_TYPE_CD, POLMGR_P_ACCID_OH_TAB.ROUTE_NUM, POLMGR_P_INCID_TAB.PRIMARY_STREET_NAME, POLMGR_P_ACCID_OH_TAB.DISTANCE_TO_REFERENCE, POLMGR_P_ACCID_OH_TAB.DIRECTION_TO_REFERENCE, POLMGR_P_ACCID_OH_TAB.REFERENCE, POLMGR_P_INTERSECTION_CD_VIEW.column_value_desc, POLMGR_P_OCCURRENCE_CD_VIEW.column_value_desc, POLMGR_P_ROAD_CONTOUR_CD_VIEW.column_value_desc, POLMGR_P_ROAD_CONDITION_CD_VIEW.column_value_desc, POLMGR_P_ROAD_CONDITION2_CD_VIEW.column_value_desc , POLMGR_P_CONDITION_LIGHT_CD_VIEW.column_value_desc
    FROM (((((((POLMGR_P_ACCID_OH_TAB LEFT JOIN POLMGR_P_INCID_TAB ON POLMGR_P_ACCID_OH_TAB.P_INCID_NO = POLMGR_P_INCID_TAB.P_INCID_NO) LEFT JOIN POLMGR_P_CRASH_SEVERITY_CD_VIEW ON POLMGR_P_ACCID_OH_TAB.CRASH_SEVERITY_CD = POLMGR_P_CRASH_SEVERITY_CD_VIEW.column_value) LEFT JOIN POLMGR_P_INTERSECTION_CD_VIEW ON POLMGR_P_ACCID_OH_TAB.INTERSECTION_TYPE_CD = POLMGR_P_INTERSECTION_CD_VIEW.column_value) LEFT JOIN POLMGR_P_OCCURRENCE_CD_VIEW ON POLMGR_P_ACCID_OH_TAB.OCCURRENCE_CD = POLMGR_P_OCCURRENCE_CD_VIEW.column_value) LEFT JOIN POLMGR_P_ROAD_CONTOUR_CD_VIEW ON POLMGR_P_ACCID_OH_TAB.ROAD_CONTOUR_CD = POLMGR_P_ROAD_CONTOUR_CD_VIEW.column_value) LEFT JOIN POLMGR_P_ROAD_CONDITION_CD_VIEW ON POLMGR_P_ACCID_OH_TAB.ROAD_CONDITION1_CD = POLMGR_P_ROAD_CONDITION_CD_VIEW.column_value) LEFT JOIN POLMGR_P_ROAD_CONDITION2_CD_VIEW ON POLMGR_P_ACCID_OH_TAB.ROAD_CONDITION2_CD = POLMGR_P_ROAD_CONDITION2_CD_VIEW.column_value) LEFT JOIN POLMGR_P_CONDITION_LIGHT_CD_VIEW ON POLMGR_P_ACCID_OH_TAB.CONDITION_LIGHT_1_CD = POLMGR_P_CONDITION_LIGHT_CD_VIEW.column_value
    WHERE (((POLMGR_P_CRASH_SEVERITY_CD_VIEW.version)=2) AND ((POLMGR_P_INTERSECTION_CD_VIEW.version)=2) AND ((POLMGR_P_OCCURRENCE_CD_VIEW.version)=2) AND ((POLMGR_P_ROAD_CONTOUR_CD_VIEW.version)=2) AND ((POLMGR_P_ROAD_CONDITION_CD_VIEW.version)=2) AND ((POLMGR_P_ROAD_CONDITION2_CD_VIEW.version)=2) AND ((POLMGR_P_CONDITION_LIGHT_CD_VIEW.version)=2)) OR (((POLMGR_P_CRASH_SEVERITY_CD_VIEW.version)=2) AND ((POLMGR_P_INTERSECTION_CD_VIEW.version)=2) AND ((POLMGR_P_OCCURRENCE_CD_VIEW.version)=2) AND ((POLMGR_P_ROAD_CONTOUR_CD_VIEW.version)=2) AND ((POLMGR_P_ROAD_CONDITION_CD_VIEW.version)=2) AND ((POLMGR_P_ROAD_CONDITION2_CD_VIEW.version)=2) AND ((POLMGR_P_CONDITION_LIGHT_CD_VIEW.version)=2));


    No matter what I change, I'm only getting 27 records or none.

  5. #5
    jediskipdogg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    7
    I should add the reason for all of the "version" at the end is each of the code tables has two versions of data in them. I need to utilize version 2 only or else all of my results are duplicated.

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Right now the main table has 8627 records in it but not all the fields are always filled out as it depends on what caused the crash and where it occurred.
    Are all the fields in your main table that you are joining on populated?
    Are all the fields you are joining on in the other tables populated?

    Having blank/empty field is OK, but it shouldn't be on the fields you are joining on.

    When approached with a problem like this, I like to do in small steps.
    First, create just join one table to your main table and add any criteria you want.
    Now, check your results. Are they what you expect?
    If so, then continue on and add the next table.
    Repeat these steps, adding one table at a time, and checking the results.
    If you do this, it will become very evident where your problems reside.

  7. #7
    jediskipdogg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    7
    No, they are not always popilated in the main table. That is part of the problem and the second I kink the two tables, ml the number of results changes. I want to return 8627 rows of data, no more, no less. Eventually I will be running the query monthly and exporting it for a roads department so the date range will be small making the data returned smaller. But once I link a table from a field that doesn't always have anything in the main table for that field, my data goes down.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Quite possibly you will have to do 7 filtered queries then join those queries to the main table. Can nest queries. Something like:

    SELECT main.*, q1.* FROM (SELECT * FROM table1 WHERE field = 2) AS q1 RIGHT JOIN main ON q1.ID_fk=main.ID;

    Build the 7 queries. Then build the compound query using the main table and 7 queries. Copy/paste the SQL for each query into the compound query then delete the 7 query objects.

    There is a limit of 64,000 characters in a query statement, should be enough for you.
    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.

  9. #9
    jediskipdogg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    7
    I was thinking joined queries but it's not as if I'm doing a link like..

    Table A -- Table B -- Table C

    Im doing...

    Table A -- Table B
    Table A -- Table C

    And there is no similar main number in A that can be linked to B and C. B and C are simply code tables that give descriptions to the data in A.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am not suggesting a chain. Each query will join to the main table just as you did with tables.

    Revised my previous post possibly after you read it.
    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.

  11. #11
    jediskipdogg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    7
    Maybe I'm just lost or still having issues. So I tried to create ONE query by itself, the field is filled out 8515 times instead of the total 8627 times. Here is what I have...

    SELECT POLMGR_P_ACCID_OH_TAB.ACCID_NO, POLMGR_P_ACCID_OH_TAB.CRASH_SEVERITY_CD, POLMGR_P_CRASH_SEVERITY_CD_VIEW.column_value_desc, POLMGR_P_CRASH_SEVERITY_CD_VIEW.version
    FROM POLMGR_P_ACCID_OH_TAB RIGHT JOIN POLMGR_P_CRASH_SEVERITY_CD_VIEW ON POLMGR_P_ACCID_OH_TAB.CRASH_SEVERITY_CD = POLMGR_P_CRASH_SEVERITY_CD_VIEW.column_value
    WHERE (((POLMGR_P_CRASH_SEVERITY_CD_VIEW.version)=2));

    Yet it is only returning 8515 records. How do I get the records that are blank to return?

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    How do I get the records that are blank to return?
    What exactly is blank, surely not the whole record? It would be a criteria depending on the field name, in the line below the version criteria - so that the SQL would read WHERE verions=2 OR fieldname Is Null.

  13. #13
    jediskipdogg is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2018
    Posts
    7
    Quote Originally Posted by aytee111 View Post
    What exactly is blank, surely not the whole record? It would be a criteria depending on the field name, in the line below the version criteria - so that the SQL would read WHERE verions=2 OR fieldname Is Null.
    Here is the query as I have it built so far. I started it over from scratch so I could find errors as they came along.

    ACCID_NO ACCID_DTE LATITUDE LONGITUDE CRASH_SEVERITY_CD
    2005000000689 1/17/2005 8:19:00 AM 0 0
    2005000000691 1/17/2005 9:09:00 AM 0 0 3




    So the query above is missing a Crash Severity Code for the first accident. That is not an issue for our record purposes and the way our system operates. However, when releasing this report to another department I don't want them to see a 3, I want them to see what 3 means which is where the following table comes in to play.

    column_value column_value_desc column_abbr rollup_column_value rollup2_column_value jxdm_rollup_value status_cd catg_desc tree_order version
    1 Fatal
    1
    1 L

    1
    1 Fatal
    1
    1 L

    2
    2 Injury
    2
    2 L

    1
    2 Injury
    2
    2 L

    2
    3 PDO
    3
    3 L

    1
    3 PDO
    3
    3 L

    2
    4 Unknown
    4
    4 L

    1



    3 is equal to PDO and I want my query to show PDO for the second crash but remain blank for the first one. Keep in mind we are using version 2 (final column) for all of our records. The state changed crash reports many years ago resulting in duplicating many codes but with a version 1 or 2. I'm not worried about historical, so all will be 2 for me. What I am getting once I link the two tables together in the query is I lose the first record since it has the Crash Severity Code as blank. I need that record still in my query.

  14. #14
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You cannot right join where the field on the left is blank, so I am not sure what you are showing us. If the field used in the join on the left table is blank or null, then you will need a prior query to pick up this record, and then your second query will need to join on a different field.

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    You need Left Join, not Right Join!

    Code:
    SELECT a.ACCID_NO, a.CRASH_SEVERITY_CD, Nz(b.column_value_desc,""), b.version
    FROM POLMGR_P_ACCID_OH_TAB AS a LEFT JOIN POLMGR_P_CRASH_SEVERITY_CD_VIEW AS b ON a.CRASH_SEVERITY_CD = b.column_value
    WHERE (((b.version)=2));
    
    NB! Unless you convert version number to string, you can't use NZ(b.version,"") in query. But you can use anything NZ(b.version,0) or NZ(b.version,999"") or NZ(b.version,-1) there.

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

Similar Threads

  1. Access Query showing duplicate records
    By andrew.ang8843 in forum Queries
    Replies: 10
    Last Post: 12-08-2017, 02:23 AM
  2. New Records not showing in Query
    By snowygirl1 in forum Access
    Replies: 2
    Last Post: 12-11-2016, 09:51 PM
  3. Report or query not showing all records
    By weslake77 in forum Reports
    Replies: 3
    Last Post: 05-04-2012, 01:33 AM
  4. Query not showing all records
    By gazzieh in forum Queries
    Replies: 2
    Last Post: 02-17-2011, 08:11 AM
  5. Replies: 1
    Last Post: 02-16-2011, 09:08 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