Results 1 to 10 of 10
  1. #1
    rbennion is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    17

    Concatenated fields with Is Not Null in WHERE issue

    Hello all,

    Newbie here, both to Access and AccessForums. I studied Relational DBs and SQL Server in school but the company i am working for uses Access, so i'm relearning quite a bit and i hope you all can be patient with me.

    I am trying to concatenate two different sets of fields, then eliminate any nulls for an export. See below how i have it currently.

    SELECT [Patient Name (L,F)].[Patient Name (L,F)], [EMSConDate] & " " & [EMSConTime] AS Expr1
    FROM [Full ACTION part 1], [Patient Name (L,F)]
    WHERE ((([EMSConDate] & " " & [EMSConTime]) Is Not Null));

    The Patient Name (L,F) is another query that is simply a concatenation of a first and last name fields.

    When i run this it generates like 1.5 millions records for a DB with only 1246 records.



    Is there a way to concatenate patient first and last name, also concatenate EMS contact date and time, then eliminate null EMS contact dates and times from my results? Obviously not all patients come via ambulance, and i only want to look at those who did.

    Thank you all and PLEASE correct me if i'm not clear or not following some form of etiquette as this is my first post.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    There is no JOIN clause. This is a Cartesian query - every record in each table associates with each record of other table.

    Don't include the space (" ") in the concatenation in the WHERE clause. Including the space means the value has a space and therefore not null even if the two fields are 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
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You are getting all the records from [Full Action Part 1] combined with all the records for [Patient Name]! Hence the millions of records being returned. The two queries need to be linked via a matching field (click on one and drag to the other).

    Next, get each query working separately. Add the not null to each query prior to coming in here.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The cause of the number of records has been mentioned, but I'll add that a better test would be:

    WHERE [EMSConDate] Is Not Null AND [EMSConTime] Is Not Null

    The concatenation can be a performance nightmare.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    rbennion is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    17
    Thanks for all the quick responses. I'll have to play with them to see what works and follow up

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    And one more point.... naming objects.

    You have a table named "Patient Name (L,F)" AND a field named"Patient Name (L,F)". This can get confusing.
    You have used special characters (the parenthesis), punctuation (the comma) and spaces in object names. Very bad idea...

    "Patient Name (L,F)" could be "PatientNameLF" or "PatientName_L_F". I would have two fields, "LName" and "FName", instead of one field.
    It is easier to concatenate fields than split them.


    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not use look up FIELDS, multi-value fields or calculated fields in tables.
    Do not begin object names with a number.
    ------------------
    Examples:
    Bad field name: "RA#" (has the hash mark")
    Good/better field names: "RANum", "RA_Num", "RA_Number"

    Bad field name: "Bagged Y/N" (has space and slash")
    Good/better field names: "BaggedYN", "Bagged_YN", "Bagged_Y_N"

  7. #7
    rbennion is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    17
    @ssanfu, thanks for the tip!

    @june7 and aytee111 were right. i joined the tables on their ID and all is running well. and yes it definitely effects execution times though

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    1552516 records to be exact? (1246 * 1246) That's what a Cartesian query does. Just thought I'd add something to the explanation of that.
    Poor performance now?
    This can be one reason: [EMSConDate] & " " & [EMSConTime]
    Not only are you still concatenating fields, you're comparing the concatenation to Nulls...
    ([EMSConDate] & " " & [EMSConTime]) Is Not Null
    As was already mentioned, concatenate in forms/reports, especially if the query performance is not good, and don't check concatenations with spaces for being Null.
    Also test for better performance if you index any unique table fields (in design view).
    Not sure why you've split date and time anyway? Why isn't it one field?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    rbennion is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    17
    Micron, unfortunately my entire database is an extract from another data source that is out of my control to change the date/time to one field.
    Thanks for the explanation and tips. I'll have to play around with the indexing to improve performance. However i'd like to ask, even if i am successful altering the current index structure to speed up this query's performance, won't that potentially slow other queries? Pardon my ignorance on this, too. I will openly admit i could do some brushing up on how to index for performance before asking other people questions. But is that a logical question?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If indexing helps one query, it would help any query using that field but provide no benefit to any other query. Think of an index as a table of contents at the start of a book (insofar as the benefit it provides), though I'm not saying it looks like one.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 2
    Last Post: 07-02-2015, 02:24 PM
  2. Replies: 16
    Last Post: 11-24-2014, 05:23 PM
  3. Replies: 23
    Last Post: 11-06-2014, 02:27 PM
  4. combobox won't display concatenated fields
    By merlin777 in forum Forms
    Replies: 4
    Last Post: 10-25-2014, 04:36 PM
  5. Sum values in concatenated fields
    By jdrubins in forum Reports
    Replies: 4
    Last Post: 09-01-2009, 07:20 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