Field combining several others 2

[ghacig]

I have a form with multiple fields. It is a continous form, allowing entry of more than one record for the same person.

I have a memo field (Summary) in the form footer that combines all the fields. I concatenated all the fields in the summary field. I would like the summary field to combine the contents of all the records together, not one record at a time. Anyway to do that.

Thanks.

George

 

[RickSpr]

Combining data from multiple records calls for an aggregate function. However, there is no aggregate function for concatenation, so you would have to do this with VBA code.

The question, though, is why would you want to? In order for your continuous form to work in the first place, you must already have the data stored in nice, discrete fields anyway. Combining it and storing it separately would be redundant, and it would be difficult to break it apart again.

Why don't you describe what you intend to with this summary field, and perhaps we can suggest a better approach. (It is helpful if you use actual table, field, form, and control names.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ghacig]

Thanks Rick,

The form describes a medical procedure. Ultimately, I would like to generate a report that have the information in a paragraph format, in 'plain english'. If I have a summary field, I could edit it on the form by adding pure structure items, such as prepositions, commas etc and then base the report on the summary field.

Fields are:
EEGFrequency (that's a number)
EEGPattern
EEGMorphology
EEGDescription1
EEGDescription2
EEGDescription3

and others.



I would appreciate any suggestions.

George Ghacibeh

 

[TheAceMan1]

How are ya ghacig . . . . .

This is still not clear! What your doing with the [blue]Summary/Memo Field[/blue] in relation to the report is understandable. Its the [blue]concatenating/combining process of records[/blue] thats a mystery!

If you could give an example of this [blue]concatenating/combining process and results[/blue], it would certainly be an eye opener!

See Ya! . . . . . .

 

[ghacig]

Thanks AceMan1

Say for example, I have for the same patient:

Record1:
EEGFrequency: 2
EEGPattern: intermittent
EEGMorphology: spike-wave
EEGDescription1: frequent
EEGDescription2: left
EEGDescription3: temporal

and Record 2:
EEGFrequency: 6
EEGPattern: bursts
EEGMorphology: sharply controured
EEGDescription1: 2 seconds
EEGDescription2: left
EEGDescription3: temporal

If I concacenate the fields into a single control in the report, it would look like this:

The patient had frequent intermittent 2Hz spike-wave disharges from the left temporal lobe.

The patient had 2 seconds 6Hz bursts of sharply controured discharges from the left temporal lobe

I need the flexibility of fixing the text before generating the report as such:

The patient had frequent intermittent 2Hz spikee-wave discharges in addition to 2 second bursts of 6 Hz sharply contoured activity from the left temporal lobe.

If I could combine records into a single summary/memo field, I could do the editing in the form and base the report on the Summary/Memo field.

I hope this is clear. I would appreciate any help.

Thanks a lot

George

 

[TheAceMan1]

OK ghacig . . . . .

Combining the records in the memo field is easy. I spent quite a bit of time looking for a way to help you with the text your appending, but [blue]it appears appended gramatics are not static and thus up to you[/blue]. A shame Access can't help you better with this.

I believe the Memo Field is a part of each record. That is, [blue]its in the same table as the EEG fields of interest[/blue]. If its not, [blue]you should make it so[/blue], as I'm sure you have no desire to [purple]reconstruct a report again & again[/purple].

The following code will Pack the records line by line, each with a line seperator, each line in the field order given in your example. You can of course change this order, or add additional fields at your discretion. Don't forget to substitute the proper MemoControlName in [purple]purple[/purple]:

[blue]Public Sub PackEEG()
   Dim rst As DAO.Recordset, Pack As String, DL As String
   
   Set rst = Me.RecordsetClone
   DL = vbNewLine & vbNewLine
   
   Do
      Pack = Pack & rst!EEGDescription1 & " "
      Pack = Pack & rst!EEGPattern & " "
      Pack = Pack & rst!EEGFrequency & " "
      Pack = Pack & rst!EEGMorphology & " "
      Pack = Pack & rst!EEGDescription2 & " "
      Pack = Pack & rst!EEGDescription3
      
      rst.MoveNext
      
      If Not rst.EOF Then Pack = Pack & DL
   Loop Until rst.EOF
   
   Me![purple][b]YourMemoControlName[/b][/purple] = Pack
   DoCmd.RunCommand acCmdSaveRecord
   Set rst = Nothing
End Sub
[/blue]

You can call the routine from just about anyway. For testing purposes, make a command button and copy/paste the following code into the [blue]On Click[/blue] event of the button (put the cursor on the event line then click the three elipses):

[blue]Call PackEEG[/blue]

After you satisified with testing, I strongly advise you [purple]setup a HotKey combination[/purple] to use instead of the command button. You need this [purple]for safety[/purple] because its too easy to click the button (besides being inviting) and [purple]overwrite previously edited Memo Content[/purple] (I'll bet that'll get under your skin!).

[purple]Be aware: No error checking is done in the code for empty recordsets or null fields.[/purple]

See Ya! . . . . . .

 

[ghacig]

Hi TheAceMan1

Thanks for the time and effort.
It did work, however, I am still having problems.

I did what you suggested. The Summary/Memo field is in the footer of a continous form.

First, if I put the command button in the details section, it doesn't' work, when I put it in the footer, it worked. I am not sure why.


Second, say I enter two records and click the command button, it works. Now, if I add a third record and click the command button or if I simply click the button again without adding an extra record, I get a Run-Time error.

The other problem is that the summary/memo field is record specific, since the summary/memo field belongs in the same EEG table. So when I select a record, the Summary field changes accordingly.


Any suggestions?

I was wondering, if I have two memo fields, one is RecordSummary and the other is PatientSummary. I could concatenate all the fields of any given record into RecordSummary as such:

RecordSummary = [EEGDescription1] & " " & [EEGPattern] and so on...

I will write it as AfterUpdate code for each and every field, this way RecordSummary will "fill as you go" for each field.

Then, I aggragate the RecordSummary fields of each record into the PatientSummary field.

Is is possible, and how do you aggragate fields from multiple records?

The other option would be to make a separate table (EEGSummary) that contains the following fields:

PatientID
Date
EEGSummary (memo)

and insert it as subform in the footer of the EEG form, linked by PatientID and Date. Then aggregate the RecordSummary of the EEG fom into the EEGSummary field of the EEGSummary subform. I am not sure if this is feasable. But this way, regardless of the number of records in the EEG form, there will be a single EEGSummary for each PatientID/Date combination.

Thanks again for your help. I really appreciate it.

George Ghacibeh

 

[TheAceMan1]

OK ghacig . . . .

I'm current running a simulation I setup. In the mean time we need to check on a library.

Open any code window, then select [blue]Tools[/blue] - [blue]References[/blue]. In the listing your looking for [blue]Microsoft DAO 3.x Object Library[/blue] (3.6 or the highest yoy have). Make sure its checked, and using the up arrow, push it up as high in priority as it will go.
If it wasn't checked, do a little more testing.

Let me know if it was checked or not, and if it was, if it was at its highest priority.

See Ya! . . . . . .

 

[ghacig]

Thanks again TheAceMan1

I did check DAO 3.6 prior to testing, however did not push it up. I will do that and retry.

 

[TheAceMan1]

ghacig . . . . .

As long as DAO 3.6 is higher than [blue]Microsoft ActiveX Data Objects 2.x Library is what counts.

Now . . . . although we can fix the code, there's still a problem . . . . I'll explain after the code is rectified. So copy/paste the following [purple]line[/purple] where you see it in the code:

[blue]   Set rst = Me.RecordsetClone
   [purple][b]rst.MoveFirst[/b][/purple]
   DL = vbNewLine & vbNewLine[/blue]

Test it to see that it works.

Now the operational problem.

Because the Memo field is part of each record, [blue]you'll be updating the Memo with the full current record listing at the time you hit the button, for whatever record[/blue]. This is undersirable and adds unneeded data, [blue]causing the database to unnecessarily grow[/blue]. Besides the fact that [purple]this indicates you just about have to go to a specific record to get the full results unless you update them all![/purple] . . . . . See what I mean? . . .

So [blue]the memo field is in a bad place[/blue] as far as your table structure is concerned. Problem is were talking a little redesign here, [purple]as the memo field belongs in a parent table to the one your using now[/purple]. You still have the prime objective of saving all editing so you can print the same reports at your discretion, [blue]without retyping it all![/blue]

So this is the magic question:

[blue]Is there a Parent Table related to the one your using now?[/blue]

If not, what impact would it have on you in adding one?

Oh . . . . and I didn't forget all your previous questions. I'm sure you can see that for now, their academic . . . . Myybe we can pick them up later . . . .

See Ya! . . . . . .

 

[ghacig]

TheAceMan1,

Thanks again. I do understand the problem. Actually, the table in question is part of a large, complex database that encompasses a sort of Electronic Medical Record System. The mother table of the entire database is Demographics, where the primary key is the Patient ID (called MRN). Now, there are five tables addressing the EEG:

EEGTechnical
EEGBaseline
EEGInterictal
EEGSeizures
EEGImpression: this is the mother table that contains MRN, Date, and several fields summarizing the findings.

The table we have been discussing is actually the EEGInterictal table. For the Technical, Baseline, and Impression, there is only one recrodset per MRN/Date combination. For the Seizures, there are several recordsets, but each has to be described separately. So, for the Technical, Baseline, Seizures and Impression,I made the summary memo a concatenation of all the fields. It worked out fine. The problem is with the EEGInterictal table because several recordset can exist for the same MRN/Date combination and they need to be described in a single paragraph in the report.

In terms of Forms, the mother EEG form is based on a query from Demographics and EEGImpression, containing: MRN, LastName, FirstName, AGE and Date. In the mother form, there are five Tabs each containing a subform of the five EEG tables, each linked to the mother form via MRN and Date.

I could possibly add a memo to the EEGImpression table, and put the control in the EEGImpression Subform, if there is a way to Pack the recordsets from the EEGInterictal into it.

I hope this is clear. I appreciate your help. Thanks.

George

 

[ghacig]

TheAceMan1,

The code works with the correction you suggested. Thanks. Remains the operational problem!

George

 

[TheAceMan1]

[blue]The problem is with the EEGInterictal table because [purple]several recordset can exist for the same MRN/Date combination[/purple] and they need to be described in a single paragraph in the report.[/blue]

Because of this you can't just put a memo in the [blue]EEGImpression Table[/blue]. That memo could only comprise all the [blue]EEGInterictal[/blue] recordsets.

The only way to do this properly is to add an intermediary table. For now lets call it the [blue]EEGInterictal Sessions[/blue] table. It would only require three fields:

[blue]InterictalID as AutoNumber
MRN as YourCurrentDataType
Date as YourCurrentDataType[/blue]

The Sessions Table [blue]is linked to the Mother Table[/blue] in same way as the EEGInterictal Table. As is the new Sessions subform.

[blue]The EEGInterictal subform now becomes a [purple]Child[/purple] to the Sessions subform, linked via InterictalID[/blue]. Now, on the tab where you had the Interictal subform, you now have a [blue]subform Interictal[/blue] within a [blue]subform Sessions[/blue]. [purple]Now you can navigate thru the sessions and view the individual recordsets![/purple] As a byproduct, the [blue]perfect place for the Memo Field arises[/blue] . . . . [purple]The Sessions Table/Form![/purple]

Now . . . be aware! The biggest change [blue]throughout the database[/blue] will be:

1) Referencing the Interictal subform has changed! Any current references will not work and have to be updated.

2) Your information gathering for the report has changed, as far as 1 above is concerned.

Now we can get back to those questions you asked about the [blue]Operations of the Memo Field.[/blue] But first, let me know what you think of this, and wether you want to continue . . . .

See Ya! . . . . . .

 

[ghacig]

The AceMan1,

Thanks again for your help. I certainly want to continue. This is a problem that may come up in the future in other parts of the database and I would like to find the right solution.

So, to get things straight:

I create a new table: EEGInterictalSessions that includes the Memo/Summary fields, in addition to the ID, MRN and Date.


I insert the EEGInterictalSessions subform in the place of the current EEGInterictal subform, then insert the EEGInterictal subform in the EEGInterictalSessions subform.
I am not sure I understand how the EEGInterictal subform would be linked to the Sessions subform via Interictal ID.
Shouldn't it be linked via MRN and Date?

Now, will the code you gave me work if applied to the Memo field which is now in the Sessions subform? or will I need another code?

Thanks.

George

 

[TheAceMan1]

OK ghacig . . . . You want to continue . . . .

Right now before you do anything else, [purple]BackUp the DataBase[/purple] so you can come back to square one if necessary and keep working.

Your right about the adding the new table, only your not seeing relationships.

The [blue]EEGInterictalSessions table should be on the Many Side[/blue] of a [blue]One To Many Relationship[/blue] with EEGImpression table. [blue]Linked via MRN and Date[/blue]. I need to know if this is a compound key or what? Just how are you linking? . . . .

The [blue]EEGInterictal table should be on the Many Side[/blue] of a [blue]One To Many Relationship[/blue] with EEGInterictalSessions table. [purple]But don't incorperate this relationship yet[/purple], as first the InterictalID has to be included as the [blue]ForeignKey[/blue], in order to complete the relationship. You no longer need [purple]MRN and Date[/purple] in the EEGInterictal table. But a PrimaryKey has to be added in its place to tag the details.

Stated Specifically:

[blue] One [purple]MRN and Date[/purple] of the EEGImpression table [purple]can have many sessions/b][/purple] in the EEGInterictalSessions table. Each session is tagged with InterictalID autonumber.

The EEGInterictal table [purple]becomes the details of each session[/purple] linked via InterictalID. This seperates out the recordsets of each session.[/blue]

I hope this is a little clearer . . . . and mind you, [purple]gotta get the tables right or the design could easily come to a halt.[/purple]

I need to know if you can start with fresh data, or if you need to keep current data?

If you need to keep current data, then we have to go thru the process of assigning ID's to the new fields and maintain related data, which may take code if your records are abundant.

I also need to know what the PrimaryKey was of the origional EEGInterictal table?

See Ya! . . . . . .

 

[ghacig]

OK,

Actually, MRN and Date are not a compound key. Each table has an autonumber as its primary key, except the Demographics table, where the primary key is MRN. The relationship between Demographics and all other tables is a one-to-many relationship.

As it is now, the relationship between EEGImpression and EEGInterictal is a many-to-many relationship using both fields, MRN and Date.

The primaryKey for the EEGInterictal table is an autonumber called EEGInterictalID.

I do not have data yet, I am still constructing the program. So this is not a concern.

Please forgive my ignorance, I am not sure I know what a ForeignKey is.

Thanks.

 

[TheAceMan1]

ghacig . . . .

If there's anything you don't understand . . . . [blue]just ask![/blue] [purple]I'll side step and make sure you do! [/purple]Besides, for what were about to do, you need to understand everything.

[blue]I am not sure I know what a [purple]ForeignKey[/purple] is.[/blue]

[blue]A [purple]ForeignKey[/purple] is usually [purple]the connecting field in a relationship in the table on the Many Side[/purple] of a One To Many relationship . . . .

In the table on the one side, its usually (not always) the [purple]ParentKey/Primarykey[/purple].

So . . . in a One To Many relationship its usually: [purple]PrimaryKey on the One Side[/purple], connected to the [purple]ForeignKey[/purple] (usually same name as the PrimaryKey) [purple]on the many side.[/purple]

In the database world you'll usually see these keys related/referred to as [purple]PK[/purple] & [purple]FK[/purple] . . .[/blue]

[blue]As it is now, the relationship between EEGImpression and EEGInterictal is [purple]a many-to-many relationship[/purple] using both fields, MRN and Date. [/blue]

I have my doubts that this is a true [purple]Many To Many[/purple] relationship (I could be wrong), either way I need to see the related fields as they are before we continue. So, using the following format post the [blue]EEGImpression and EEGInterictal [/blue] tables and any intermediary tables between the two if they exist:

[blue]<TableName>
FieldName PK as DataType OneSide To <TableName>
FieldName FK as DataType ManySide To <TableName> (if exist)
FieldName as DataType
FieldName as DataType[/blue]

Example

[blue]<tblAccounts>
AccountID PK as AutoNumber OneSide To <tblDetails>
Fname as Text
Lname as Text
AcctTag as LongInteger

<TblDetails>
DetailID PK as Autonumber
AccountID FK as LongInteger ManySide To <tblAccounts>
Product as Text
Quantity as Integer[/blue]

Also, I need you to [purple]confirm your understand[/purple] the tables & relationships were installing and that you see its what you need, in reference to what you have now . . .

See Ya! . . . . . .

 

[ghacig]

TheAceMan1

Thanks for the explanation. Here are the tables as you requested:

<EEGImpression>
EEGImpressionID as AutoNumber
MRN as Text Manyside to <Demographics>
EEGImpressionDate as Date
EEGImpression as Text
(other fields as either interger or text)
EEGImpressionComment as Memo (this is the summary memo)

<EEGInterictal>
EEGInterictalID as AutoNumber
MRN as Text ManySide to <Demographics>
and ManySide to <EEGImpression>
EEGInterictalDate as Date ManySide to <EEGImpression>
(other fields including frequency, pattern, description as Text)
EEGInterictalComment as Memo

As you see, both tables are on the many side of a one-to-many relationship with Demographics, but they are in a many-to-many relationship between each other using both the MRN and Date fields. I may need to change and adjust the relationship. This is still doable since I don't have any data stored yet.

Thanks for your help.

George

 

[TheAceMan1]

George . . . . .

There's too much wrong with relationships as posted [blue](a kind of overkill).[/blue] Eventually ([blue]sooner[/blue] than later), you would find yourself have nothing but trouble. The relationship structure also shows an understanding that needs to be raised another level (not trying to sound demeaning here).

The biggest problem is with [blue]MRN[/blue]. The join between [blue]EEGImpressions[/blue] & [blue]EEGInterictal[/blue] loops [blue]MRN[/blue] back on itself in EEGInterictal, with the same [blue]MRN[/blue] join from [blue]DemoGraphics[/blue]. [purple]Hows Access to know who's controlling MRN in EEGInterictal . . .[/purple] [blue]DemoGraphics[/blue] or [blue]EEGImpressions[/blue]?

As a small side step on [blue]Many To Many Relationships[/blue], this is the hardest relationship to understand. In fact most critiques on it get complex. A few years ago I ran across the simplest explanation ever:

[blue][purple]A Many To Many Relationship[/purple] is like teachers & students . . .

One Teacher can have many students, and [purple]one student can have many teachers[/purple].[/blue]

Keep this mind whenever you think about this type relationship.

Moving on . . . . I need a short explanation of what [blue]EEGImpressions[/blue] & [blue]EEGInterictal[/blue] are and how they relate to one another. [purple]But not how they relate according to your table relationships, rather, how they relate in your mind as someone learned in the field.[/purple]

Then a small secnario of the same patient coming in and having more than one test on different days, and what you expect to happen in the tables.

Take your time with this and keep it short, as I'm pretty much with you and just clarifying what needs to be done. I do wish we could've finished this sooner, but we have to get over these humps . . . no way out . . .

[blue]I am believing: One [blue]EEGImpressions[/blue] on any date, can have many (more than one) [blue]EEGInterictal[/blue].[/blue] Your explanations should bear this out true or false . . . . .

See Ya! . . . . . .

 

[TheAceMan1]

George . . . .

[blue]At your leisure[/blue], go over the following references and get to know them if you can. Particularly for the present task, see Relationships.

These cover most of the basics (in good detail) of [purple]Relational Database Design[/purple].

[blue]Normalizing Tables[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;209534&Product=acc2000

[blue]Table Relationships[/blue]

http://support.microsoft.com/default.aspx?scid=kb;en-us;304467&Product=acc2000

[blue]Fundamentals of Relational Database Design[/blue]

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

See Ya! . . . . . .