Piecing back together a chopped up note

[HamotIntern]

I have a real challenge on my hands. The hospital I work at has a program called CareVue. It has an AllBase database that we have successfully linked to Access. CareVue has many flaws. The one that I am attempting to deal with now is when a nurse or a doctor enters a note; CareVue chops it into 70 character chunks and makes a new record for each chunk. Therefore, in the database, you have multiple records with identical information except for the value_ column and a column that numbers the chunks so that you can put them back together. What we want to do is concatenate them back together into one long string of text using a query. We cannot change what CareVue does and so our only option is to deal with the output it gives us. I have attached the SQL code that I have so far.

SELECT systpe_freenote.notetime, systpe_freenote.notetitle, systpe_dbcodeconfig_set.longlabel, systpe_user_.namelast+' '+systpe_user_.namefirst+' '+systpe_user_.proftitle AS EmpName, systpe_user_.employeeno, systpe_cfgpatients.name_, systpe_cfgpatients.medrecnum, systpe_string60_set.elemid, systpe_string60_set.value_

FROM systpe_cfgpatients INNER JOIN (systpe_user_ RIGHT JOIN ((systpe_dbcodeconfig_set INNER JOIN (systpe_freenote LEFT JOIN systpe_string60_set ON (systpe_freenote.cid = systpe_string60_set.cid) AND (systpe_freenote.oid = systpe_string60_set.oid) AND (systpe_freenote.gprid = systpe_string60_set.gprid)) ON (systpe_dbcodeconfig_set.oid = systpe_freenote.notetype_cid) AND (systpe_dbcodeconfig_set.elemid = systpe_freenote.notetype)) LEFT JOIN systpe_notecfg ON systpe_freenote.notetitlecfg_cid = systpe_notecfg.oid) ON systpe_user_.oid = systpe_freenote.userid) ON systpe_cfgpatients.gprid = systpe_freenote.gprid

WHERE (((systpe_freenote.notetime)=#5/1/2005 18:45:0#))

ORDER BY systpe_freenote.notetime, systpe_string60_set.elemid;

 

[fredericofonseca]

Does the AllBase database allow you to create User defined functions?

If so I would create one function that given a particular key identifier would query the table in question and join all the corresponding records, and then return that as a string to your "standard" selects.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

You can also do it on Access itself

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

Public Function get_names(key As String) As String
Dim rs As Recordset
Dim Sql As String
Dim sstring
Sql = "select description from demo where name1 = '" & key & "'"
Set rs = Application.CurrentDb.OpenRecordset(Sql)
sstring = ""
While Not rs.EOF And Not rs.BOF
    sstring = sstring + rs.Fields("description").Value
    rs.MoveNext
Wend
Set rs = Nothing
get_names = sstring
End Function

executing with
SELECT name1, get_names(name1)
FROM demo2;


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[miller1975]

Hello Frederico,
Lookign for a reponse to the invoice query please

 

[dhookom]

There is a generic concatenate function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

There are also two or more FAQs in this forum that provide solutions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[HamotIntern]

Duane,

I really appreciate your little concatenate function. However, it doesn’t quite work for me. I get a “Join expression not supported.” error when I try to run my query. Any thoughts?

 

[fredericofonseca]

Please post your code using the function from Duane so we can evaluate it.

Note that my function and Duanes are basically the same, but mine has hardcoded values. Easy to change also, but both should work.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[HamotIntern]

I got the last post problem figured out, it was a stupid mistake. However, I am still have problems with Duan's basConcatenate module. When I run the below code, it hangs on
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

The exact error is,

Run-time error '-2147217900 (80040e14)':
Syntax error (missing operator) in query expression 'systpe_freenote.note =5/5/2005 7:32:00 AM'.

Here's the code,

SELECT systpe_freenote.notetime, systpe_cfgpatients.medrecnum, systpe_freenote.notetitle, systpe_dbcodeconfig_set.longlabel, systpe_user_.namelast+' '+systpe_user_.namefirst+' '+systpe_user_.proftitle AS EmpName, systpe_user_.employeeno, systpe_cfgpatients.name_, Concatenate("SELECT value_ FROM systpe_string60_set WHERE systpe_freenote.notetime =" & [notetime]) AS Notes

FROM systpe_cfgpatients INNER JOIN ((systpe_user_ RIGHT JOIN ((systpe_dbcodeconfig_set INNER JOIN systpe_freenote ON (systpe_dbcodeconfig_set.elemid=systpe_freenote.notetype) AND (systpe_dbcodeconfig_set.oid=systpe_freenote.notetype_cid)) LEFT JOIN systpe_notecfg ON systpe_freenote.notetitlecfg_cid=systpe_notecfg.oid) ON systpe_user_.oid=systpe_freenote.userid) LEFT JOIN systpe_string60_set ON (systpe_freenote.cid=systpe_string60_set.cid) AND (systpe_freenote.oid=systpe_string60_set.oid) AND (systpe_freenote.gprid=systpe_string60_set.gprid)) ON systpe_cfgpatients.gprid=systpe_freenote.gprid

ORDER BY systpe_freenote.notetime, systpe_string60_set.elemid;

Let me know if you need anything else to help me.

 

[PHV]

Perhaps this ?
, Concatenate("SELECT value_ FROM systpe_string60_set WHERE cid=" & [systpe_freenote].[cid]) AS Notes

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HamotIntern]

I kind of stepped away from this problem for a little while, now I’m back. PH, where exactly should I place that piece of code?

 

[HamotIntern]

I don't want this thread ending with me asking a stupid question. Right after I submitted it I realized how evident the answer was.