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

 

[ghacig]

TheAceMan1,

Thank you for the references, they were very helpful. I think I understand what you mean.

Briefly,

Each patient is identified by the MRN (PK of Demographics).
Each patient can have only one EEG on any given date. So the MRN-EEGDate combination is unique.
Each EEG has 5 components:
1. EEGTechnical: one recordset per EEG
2. EEGBaseline: one recordset per EEG
3. EEGInterictal: several recordsets per EEG
4. EEGSeizures: several recordsets per EEG
5. EEGImpression: one recordset per EEG

Besed on my understanding of the problem, I propose to restructure the table relationships as such:

<EEGImpression>
EEGID (PK) as text
MRN (FK) as text manyside with Demographics
Date as date
(other fields discribing the impression)

<EEGInterictalSession>
EEGInterictalSessionsID (PK) as autonumber
EEGID (FK) as text manyside with EEGImpression
EEGInterictalSummary as text (memo field)

<EEGInterictal>
EEGInterictalID (PK) as Autonumber
EEGInterictalSessions ID (FK) as integer manyside with EEGInterictalSessions
(other fields describing the EEG)

I will restructure also the other tables removing MRN and Date fields and replacing them with EEGID as FK.

In order to ensure the uniqueness of the MRN-Date combination, I propose to make:

EEGID = [MRN] & "-" & [Date] instead of making MRN and Date a compoud key.

Now the question, can I do the change and update my current forms-subforms or I need to reconstruct the forms from scratch.

Please let me know if I got it right.

Thanks.

George

 

[TheAceMan1]

[blue]Please let me know if I got it right. [/blue]

Way ta go George! . . . . . [purple]Now your playing/talking with power.[/purple] I think I see the makings of a Relational Database Programmer here! . . . .

[blue]can I do the change and update my current forms-subforms[/blue]

Yes you can, but hang on, as we need to go over assignment of the primarykey [purple]EEGID[/purple]. [blue]Remember: Its Tables, PKs & Relationships that have to be right, and determine the ease of your design.[/blue] Get something wrong here and Whamo, some months down the line the design crashes, with tons of data sitting there. I've been here many times in the past, and looking for more than just coffee!

[blue][purple]In order to ensure the uniqueness of the MRN-Date combination[/purple], I propose to make:

[purple]EEGID = [MRN] & "-" & [Date][/purple] instead of making MRN and Date a compoud key.[/blue]

Although you can have it your way, my efforts here are to heighten your view of [blue]PKs[/blue]. Besides, what you purpose is an unecessary complication.

[blue]A [purple]PrimaryKey[/purple] is simply a unique identifier for each record in the table its assigned to, and only that table![/blue]

A [blue]common mistake[/blue] here is [blue]adding real world value to the PK.[/blue] For instance, the PK represents an account number or social security number. You'll notice, and if you think about it, throughtout your manipulation of records, [blue]the PK simply has to be unique[/blue], no matter what you call it!

Another common mistake, is [blue]trying to include some value that will relate to another table.[/blue] Although this can be done, its the [purple]wrong way to think of PK.[/purple] Besides it violates the quote above [purple]"only that table"[/purple]. This can be considered . . . [purple]thinking outside the box![/purple]

[blue]Unless something depends on saving your life:

The best Primary Key is a [purple]single numeric field![/purple][/blue]

Record manipulation and searching/sorting/lookup is fastest with numeric PKs (considering text requires a character by character check). This comes into play espcially if you expect your record count to be abundant.

[blue]The secondary function of a Primary Key is as the [purple]one side[/purple] of a join to another table.

In a join your simply saying; you can't have a record in the [purple]Child Table[/purple] without a corresponding matching PK in the [purple]Parent Table[/purple][/blue]

Here its the join thats important, not the names.

[blue]All in all, knowing our Pks have to be unique, the primary objective in formulating and using a PK is ease of record management in the database. Much more often than not, this will be a single numeric field.[/blue]

Understand George, this is just more food for thought. You can have your PKs anyway you like. I am trying to keep you from mumbling to yourself in the future. Just simply give PKs their due attention and always think in simple terms.

To help you in your PK assignments, remember there are [blue]easy ways to validate and check for duplicate fields.[/blue]

Your [blue]MRN[/blue] is already unique, but its text type. Any chance it can be numeric?

Your all important [blue]EEGID[/blue] can be autonumber or numeric. Appending the date as part of the PK is ambiguous, espcially since the date should be a part of the [blue]EEGInterictal[/blue]. If your worried about duplicate dates, a small amout of validation code will prevent this.

Now, without having to start from scratch, just delete all relationships between tables and do your editing.

I'll stop here to see what you think or want to do.

See Ya! . . . . . .

 

[ghacig]

TheAceMan:

Thanks again for your help. MRN is actually a number and I can change the DataType of MRN into number.

So, EEGID will be an autonumber.

I totally agree with you. The last thing I want is a crashing database with tons of data. This is whyI have been holding off entering any data until I get the program ready.

So, if you think the following structure is OK, I will delete all relationship and edit the tables and reset the relationships. But, how do I update the forms and subforms?


<EEGImpression>
EEGID (PK) as autonumber
MRN (FK) as number manyside with Demographics
Date as date
(other fields discribing the impression)

<EEGInterictalSession>
EEGInterictalSessionsID (PK) as autonumber
EEGID (FK) as number manyside with EEGImpression
EEGInterictalSummary as text (memo field)

<EEGInterictal>
EEGInterictalID (PK) as Autonumber
EEGInterictalSessions ID (FK) as number manyside with EEGInterictalSessions
(other fields describing the EEG)

Again, thanks a lot for all your help.

George

 

[TheAceMan1]

[blue]The last thing I want is a crashing database with tons of data.[/blue]

I didn't mean to scare you when I said:

[blue]Get something wrong here and Whamo, some months down the line the design crashes, with tons of data sitting there.[/blue]

What I really meant to say was: you may reach a point in making certain additions to the database, and find the nature of your Relationships & PK's manke it next to impossible to do so, or required resturcting (with so much data) brings the design process to a halt. [blue]We don't design crashing databases.[/blue]

Moving On . . . . .

Another area I forgot to address is [blue]your naming convention.[/blue] Or, more specifically, [blue]the length thereof.[/blue] [purple]Strictly for purposes of readability[/purple], names should be as short as possibile. More abbreviations if anything else. Consider deciphering a page full of those long Form & IDs you have! [blue]This will come into play espcially in reading queries & code.[/blue]

[blue]EEGID[/blue] is perfect. Short, to the point, can't mistake what it is, and [blue]easily readable![/blue]

[blue]EEGInterictalSessionsID[/blue] on the other hand is something else!
[blue]EEGIS_ID[/blue] or [blue]ISID[/blue] is certainly [blue]more readable.[/blue]

I think you get the idea. As long as you (the programmer) recognize it at a glance, thats all you need. [blue]You'll appreciate this the next time you read a Query, SQL, or Code.[/blue] [purple]This is one of those things we do to make things easier for ourselves![/purple]

Moving On . . . . .

For future reference in the database field, when you [blue]specify a fields data type[/blue], always state exactly what it is. Instead of [blue]Numeric[/blue], it should be [blue]Integer[/blue], [blue]Long Integer[/blue], or [blue]Single[/blue] . . . ect, [blue]Memo[/blue] or [blue]Text[/blue] or [blue]Yes/No[/blue].

[blue]So, if you think the following structure is OK[/blue]

Yes . . . it looks just fine. I query the table containing the Date Field, but I'll leave that up to you. If it needs to be moved to another table, I'm sure it will readily come to you.

[blue]But, how do I update the forms and subforms?[/blue]

For the sake of [blue]versatility[/blue] your going to [blue]base the recordSource of each form on SQL[/blue].

1) For each form, open the form in [blue]design view[/blue], and open the [blue]porperties[/blue] window for the form.

2) On the [blue]Data Tab[/blue], put the cursor on the [blue]RecordSource line[/blue], then click the [blue]three elipses[/blue] just on the right.
If you don't go directly to the [blue]query builder[/blue], you'll probably have to answer 'Yes' to a prompt to get there.

3) Make the query to return the fields you desire for the form. [purple]Important . . . do not click save when done![/purple]

4) Click the [blue]Close Button[/blue] of the [blue]Query Builder[/blue], then click 'Yes' in the prompt to save. You'll return to the form.

5)Click Save in the form.

Now just redesign the form. Any fields you don't already have will be in the [blue]Field List[/blue].

Thats it for redesigning the forms.

Let me know how it goes or what you need. Mean time I'll modify the cde for the Memo . . . . .

See Ya! . . . . . .

 

[mustangcoupe]

just to re-enforce what aceman said about the date field... I had code problems with using a field just called date recomend ... eegdate or something else.





TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[TheAceMan1]

ghacig . . . . .

[blue]mustangcoupe[/blue] is correct in their support. Iwas talking about table location.

Do not use the word [blue]Date[/blue] as a field name. Its a function name is access (reason mustangcoupe had a problem).

Try [blue]ImpDate[/blue] . . . . . anything else but [blue]Date[/blue] . . . .

See Ya! . . . . . .

 

[ghacig]

Thanks mustangcoupe for the tip.
Thanks TheAceMan for your help. Things went well!

The only thing left is the code to pack the fields from the Interictal form into the memo field in the sessions form.

Thanks.

George

 

[TheAceMan1]

ghacig . . . . .

I'm doing the code direct so you don't have to type anything.

If you've changed any table names (since your last post of the tables), post them.

Post trhe form names.

See Ya! . . . . . .

 

[ghacig]

TheAceMan,

Here are the table name and forms and relationships.

Note the word EEG has been changed to VEEG throughout.

Table EEGImpression is now simply: VEEG

<VEEG>
VEEGID as autonumber, PK
MRN FK as text many side to Demographics
VEEGDate as Date
(Other fields)

<VEEGIISession>
IISessVEEGID as autonumber, PK
VEEGID FK as LongInteger manyside to VEEG
IISummary as memo

<VEEGInterictal>
IIVEEGID as autonumber, PK
IISessVEEGID FK as LongInteger, manyside to VEEGIISession
IIComment as memo
(Other fields)

FORMS:

mother form is called:
frmVEEG based on a query of Demographics and VEEG

subVEEGIISession subform in frmVEEG linked via VEEGID

subVEEGII subform in subVEEGIISession linked via IISessVEEGID

I hope this is clear. Thanks a lot for your help.

George

 

[TheAceMan1]

ghacig . . . . .

So sorry for the delay here. I just happened to check and found you did answer my last post (somehow I did'nt receive notification, or its possible I deleted it ny accident).

[blue]Public Sub PackEEG()
   Dim frm As Form, sfrm1 As Form, sfrm2 As Form, Memo As Control
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim Pack As String, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   Set frm = Forms!frmVEEG
   Set sfrm1 = frm!subVEEGIISession.Form
   Set sfrm2 = sfrm1!subVEEGII.Form
   Set Memo = sfrm1!EEGInterictalSummaryName
   Set db = CurrentDb()
   DL = vbNewLine & vbNewLine
   
   SQL = "SELECT * " & _
         "FROM VEEGInterictal " & _
         "WHERE [IISessVEEGID] = " & sfrm1!IISessVEEGID & ";"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      Msg = "No Records available in VEEGInterictal for " & _
            "current VEEGIISession!" & DL & _
            "Sorry!"
      Style = vbInformation + vbOKOnly
      Title = "No Records Found Error!"
      MsgBox Msg, Style, Title
   Else
      rst.MoveFirst
      
      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
      
      Memo = Pack
      DoCmd.RunCommand acCmdSaveRecord
      Set rst = Nothing
   End If
   
   Set db = Nothing
   Set sfrm2 = Nothing
   Set sfrm1 = Nothing
   Set frm = Nothing
   Set Memo = Nothing
   
End Sub
[/blue]

See Ya! . . . . . .

 

[ghacig]

TheAceMan1

Thank a lot for the code. I am still having a problem. It may be because I not using it right. I created a command button on the VEEGIISess form, next to the memo field and copied the code as a private on_click.

I am getting the following message:

Run-time error '3075':
Syntax error (missing operator)in query expression '[IISessVEEGID]='

When I click on debug, the following line gets highlighted:

Set rst = db.OpenRecordset(SQL, dbOpenDynaset)



I know you wrote it as Public sub, however, I was not sure where to put it.

Thanks.

 

[TheAceMan1]

[blue]I know you wrote it as Public sub, however, I was not sure where to put it. [/blue]

Its just fine where it is . . .

[blue]Run-time error '3075':
Syntax error (missing operator)in query expression '[IISessVEEGID]='[/blue]

My simulation works just fine here, including all changed names! Lets try [blue]full referencing[/blue] for the SQL statement. Post back the code you have up to the end of the SQL statement before replacing the SQL with the following:

[blue]   SQL = "SELECT VEEGInterictal.* " & _
         "FROM VEEGInterictal " & _
         "WHERE ((([VEEGInterictal].[IISessVEEGID])= " & _
                sfrm1!IISessVEEGID & "));"[/blue]

See Ya! . . . . . .

 

[ghacig]

TheAceMan1,

Thanks again for your help. I figured out the problem. It works only if I start by typing something in the memo field. I think the reason is that I need to start a recordset in the VEEGIISession table before I can execute the command. Any suggestions?

Another question, how do I make sure that there is only one VEEGIISession per EEG? meaning, there should be a single IISessVEEGID for each VEEGID?

Thanks.

George

 

[TheAceMan1]

[blue] I think the reason is that [purple]I need to start a recordset in the VEEGIISession table[/purple] before I can execute the command[/blue]

This is how it should be. [blue]You have to have a record in the parent form, before you can enter a record in the child form.[/blue]

Aparently you do not have [blue]Referential Integrity[/blue] on and should do so. In the relationships window right-click a join, select edit, then make sure [blue]Enforce Referential Integrity[/blue], Cascade Update/Delete is checked. This will prevent saving whats called an [purple]Orphaned Record[/purple].

As far as the SQL in the code is concerned there was no [blue]IISessVEEGID[/blue] in [blue]subVEEGIISession (new record)[/blue] to ping against!

[blue]how do I make sure that there is only one VEEGIISession per EEG? meaning, [purple]there should be a single IISessVEEGID for each VEEGID?[/purple][/blue]

This raises an issue. Eariler in the thread you said:

[blue]The problem is with the EEGInterictal table because [purple]several recordset can exist for the same MRN/Date[/purple] ...[/[/blue]

This was the reason the Session table/form was added, so you could view the seperate recordsets for a single MRN/Date. Now you want to limit Sessions to a single record per Date!

In any case, to limit [blue]subVEEGIISession[/blue] as prescribed perform the following:

In a module in the modules window copy/paste the following code:

[blue]Public Sub SessAddHandler()
   Dim frm As Form, sfrm As Form
   Dim Allow As Property, Criteria As String
   
   Set frm = Forms!frmVEEG
   Set sfrm = frm!subVEEGIISession.Form
   Set Allow = sfrm.Properties("AllowAdditions")
   Criteria = "[VEEGID] = " & frm!VEEGID
   
   If frm.NewRecord Then
      Allow = True
   Else
      If DCount("[IISessVEEGID]", "VEEGIISession", Criteria) = 0 Then
         Allow = True
      Else
         Allow = False
      End If
   End If
   
   Set Allow = Nothing
   Set sfrm = Nothing
   Set frm = Nothing

End Sub[/blue]

Then in the [blue]Current[/blue] event for [purple]frmVEEG[/purple] copy/paste the following:

[blue] Call SessAddHandler[/blue][/code]
Next in the [blue]AfterUpdate[/blue] event for the form [blue]subVEEGIISession[/blue] copy/paste the following:

[blue]   Call SessAddHandler[/blue]

See Ya! . . . . . .

 

[ghacig]

TheAceMan

Thanks a lot for all your help. You have been great. You helped me solve a lot of problems with my database. I owe you.

Thanks again.

George

 

[TheAceMan1]

ghacig . . . . .

Glad we could help you.

You don'twe me anything . . . . but if you ever want to do something worthy in this way . . . . [blue]Make A Donation to Tek-Tips![/blue]

See Ya! . . . . . .