Results 1 to 12 of 12
  1. #1
    AccessDennis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6

    Concatenate values from related records

    I am a police captain using MS Access connected to our records management system by way of ODBC link to tables that comprise our RMS system. I am trying to pull all calls for service where "*overdose*" or "*OVERDOSE*" appears in a table which contains a field which is comprised of free text entered into the dispatch record for the call.



    I have to link 2 tables (DispatchDetail & CallNarrative) with a one to many relationship. If I run a query of a specified date range for the keyword(s) I get duplicate records returned from the field "VarPortion1" in my table "CallNarrative" because of the one to many relationship between the tables.

    After many many hours trying to figure out how to merge these results into one record I think I found the solution but can't seem to make it work. Allen Browne posted a message for a similar issue and can be found at http://allenbrowne.com/func-concat.html

    I have never created a VBA module before so I assume I am making some sort of syntax error when I try to follow his example. Here is my query:Click image for larger version. 

Name:	MS Access Query.jpg 
Views:	35 
Size:	134.0 KB 
ID:	24510

    Here are the results of the query showing duplicate records that I need to merge or concatenate I guess:

    Click image for larger version. 

Name:	Query Results.jpg 
Views:	35 
Size:	222.2 KB 
ID:	24511


    It appears I need to create a module to combine the "CallNarrative" from multiple records into one result for the "CallNum".

    If Allen Browne has this:
    SELECT CompanyName, ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])
    FROM tblCompany;

    How would that translate for me using my query above?

    Any help would be greatly appreciated.

    Dennis
    Attached Thumbnails Attached Thumbnails MS Access Query.jpg  

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,434
    SELECT [CallNum], ConcatRelated("varPortion1", "Query1", "[CallNum] = """ & [CallNum] & """") FROM DispatchDetail;



    You probably want to rename Query1.

    To create the module, type CTRL-G and the visual basic editor will open. From the menu select INSERT then Module.
    Add the words OPTION EXPLICIT as the second line, then paste the function below that. When you close the module, it will ask for a name. I like basCommon.

    I don't know if Allen's code will eliminate duplicate CallNum lines. It might leave the varPortion1 data combined on duplicate lines. If it does, we need more work, if not, GREAT.

  3. #3
    AccessDennis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6

    Error msg

    Dave,

    Thanks for the suggestion. I followed your instructions but I received a compile error. Not sure if this matters but I should have told you that all fields in these tables are text (I am guessing because I use Data Dictionary Files to connect through ODBC).


    Here is the error msg:

    Attachment 24514

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,434
    That doesn't look like an error from Access. It's probably an error from the ODBC linkage, which is nothing I have experience with.
    Were there any other error messages?

  5. #5
    AccessDennis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6
    No. Just the compile error

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,914
    You can't just put an SQL statement in a Standard Module, it needs to be a procedure.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    AccessDennis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6
    RuralGuy,

    I am a relative novice. I don't understand how to put and SQL statement in a standard module, as a procedure.

  8. #8
    AccessDennis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6
    Dave,

    I reentered your function with no spaces between lines of text and now I don't get a compile error message. However when I close it, it doesn't ask me for a name. It just stays there as Module1.

    I apologize for my ignorance but I am a novice. Assuming this is correctly entered but is just named module1 how do I get it to apply to my query?

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,034

  10. #10
    AccessDennis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    6
    Option Compare Database
    Option Explicit
    SELECT [CallNum], ConcatRelated("varPortion1", "Query1", "[CallNum] = """ & [CallNum] & """") FROM DispatchDetail;

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,914
    Quote Originally Posted by AccessDennis View Post
    RuralGuy,

    I am a relative novice. I don't understand how to put and SQL statement in a standard module, as a procedure.
    Have you already put the
    Public Function ConcatRelated() function in a standard module?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,434
    You need to paste Allen Brown's function into Module1, not your query. The name Module1 is OK.
    The function begins with PUBLIC Function and ends with END Function, quite a few lines of code. That's ALL that goes into Module1 after the first two lines.

    The query:

    SELECT [CallNum], ConcatRelated("varPortion1", "Query1", "[CallNum] = """ & [CallNum] & """") FROM DispatchDetail;

    uses that function to get your concatenated results!

    So, save query1, you're gonna create a new query using it as a source.

    Create a new blank query (no tables included), switch to SQL view and paste
    SELECT [CallNum], ConcatRelated("varPortion1", "Query1", "[CallNum] = """ & [CallNum] & """") FROM DispatchDetail;

    into it. Run the new query and see what you get.

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

Similar Threads

  1. Replies: 11
    Last Post: 02-11-2015, 01:30 PM
  2. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  3. Replies: 9
    Last Post: 01-26-2013, 11:06 PM
  4. Replies: 1
    Last Post: 03-13-2012, 06:11 PM
  5. Avoiding Duplicates - Concatenate Related?
    By WBosman in forum Access
    Replies: 10
    Last Post: 05-18-2011, 09:05 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 - Senior Forums