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:
Here are the results of the query showing duplicate records that I need to merge or concatenate I guess:
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