?
Solved

Multiple rows in an Access 2002 record

Posted on 2012-08-23
23
Medium Priority
?
360 Views
Last Modified: 2012-09-01
I'm inserting data into a new table from an existing table consisting of a clientId number and 9 text fields (note_line1, note_line2, note_line3, etc. with "note" information. Each text field represents a different line for the same note and should be inserted in the same record. For example, the result should look like this for each ClientId record:
Rec1   ClientId    note_line1
                           note_line2
                           note_line3
                           Etc.
Rec2   ClientId    note_line1
                           note_line2
                           note_line3
                           Etc.
I'm using statement:
SELECT tempCliNote.note_client, tempCliNote.note_date, [note_line1] & " & VbLf & " & [note_line2] & " & VbLf & " & [note_line3] AS [Note] INTO tempNote_Test
FROM tempCliNote
ORDER BY tempCliNote.note_client, tempCliNote.note_date;

The result is, all the fields are going into one row instead of multiple rows separated by
& " & VbLf & " &

What am I missing?
0
Comment
Question by:SpaceCoastLife
  • 9
  • 6
  • 4
  • +1
21 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38325957
Your code is creating one field called Notes that contains all the notes If you want different fields then you can't join them together using the &s
0
 

Author Comment

by:SpaceCoastLife
ID: 38325983
I want then all in one field called notes but I also want each field to be on a separate line within the same record
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38325994
VBCRLF does not mean anything in SQL.
VB=Visual Basic

If you want to do this, you can do this in a form (on the current event), or in a report (on the Detail Format event).

Something roughly like this as the ControlSource:
=[note_line1] & VbLf & [note_line2] & VbLf & [note_line3]
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38326017
You can really get crazy (if you are using Access 2007 or newer formats) and use a memo field for this...
...but it will require inserting the values using SQL and "formatting" them with HTML
(In HTLM, <br>, is the rough equivalent to vbcrlf in vba)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38326029
Sorry, ...in my code above it should be something like this:

Private Sub Form_Current()
    If Not Me.NewRecord Then
        Me.txtCompoundText = [note_line1] & vbLf & [note_line2] & vbLf & [note_line3]
    end if
End Sub
    ...for a form

And something like this in a report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtCompoundText = [note_line1] & vbLf & [note_line2] & vbLf & [note_line3]
End Sub
0
 

Author Comment

by:SpaceCoastLife
ID: 38326081
"If you want to do this, you can do this in a form (on the current event), or in a report (on the Detail Format event)." Hmmm. I'm pretty sure I've seen similar statements in queries previously. Can't be sure, tho. Changing the form or report is not an option.
I'm using Access 2002. Any other ideas on how to get multiple lines in the same field, same record?
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 38326117
Why on earth would you want to store the same information TWICE in the same record? Its a total waste of time, space and effort. There is no Practical reason for it that makes any sense whatsoever.
0
 

Author Comment

by:SpaceCoastLife
ID: 38326250
I don't think you are understanding what I'm trying to do. The data in each field i.e. note_line1, note_line2, note_line3 is different. In fact, It's a continuation of the same note.
note_line1 might have "Met with Mr. Smith at 10:00 AM. note_mite2 might contain "Mr. Smith appeared to be feeling better this morning". note_line3 - something else about Mr. Smith.

Our customer would like to see the contents of these fields as separate lines in the same record with only one field for the note:
ClientId     Met with Mr. Smith at 10:00 AM
                  Mr. Smith appeared to be feeling better this morning
                  Mr. Smith ......
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 38326286
Thats fine but you say you ALSO want to keep all nine notes fields as well in the table? What is the point was my question.

IF the customer wants to SEE the data then that is the job of the visual interface, forms, reports....

The data design is obviously very very poor. Why anyone would design a database with NINE Notes fields, one for each line of text is beyond anybody who has any experience of databases or programming at all!  

As you state that you CAN NOT alter the forms OR the reports, HOW are they going to be able to see this "NEW" field?
0
 

Author Comment

by:SpaceCoastLife
ID: 38326330
No, I don't want to keep all of the text fields, only one note field with all the other field content (separated by lines).
Why they did it the way they did is not my concern - and I would think any form with a text control as the control source would display it just fine.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 38326362
Well if you are going to remove the Nine seperate fields you will need to redo EVERY form and EVERY report that has ANY reference to the table concerned.

A simple text box will NOT display a field that has multiplelines of text in it.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38326464
I agree with some of Neilsr's points.
(although not as forcefully)
;-)

Typically this type of data is stored in a Parent/Child (One-To-Many) design.
One Customer--->Many Notes
Then what you are asking for is *easy* to do in a form or Report.
In addition, the data will be easier to manage and manipulate:
(you can get a count of the notes per customer, you can re-sort the notes, ...etc )

Not sure why you state that you cannot use a Form or report, ...especially if it gives you the exact output you are requesting...?

Remember, (As I stated earlier), this can be done in the way you are requesting if you use a Memo field in Access 2007 or newer formats.
But it involves using SQL, knowing HTML and VBA fairly well, ...but worst of all, you would have to "Store" redundant data.

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38326547
BTW

Another brute force option is to simply make one "Notes" field.
Then simply press:
    Ctrl+Enter
...between each note.
(Hold down the Control key and tap the Enter key)

This will insert a Line break between the Notes.
line Breaks
Some downsides to this technique are that:
A user can change the behavior of the Enter key
There is only one Notes field.
It is not "Normalized"
You have to make the row taller (manually) to see all the notes
(This will change the height of *all* the rows, regardless of the number of note/lines)
...etc

JeffCoachman
0
 

Author Comment

by:SpaceCoastLife
ID: 38326575
Jeff: I'm converting customer data to a different Db. That Db is frozen insofar as changes as is the fact it's Access2002. It also has only one note field. It's important to understand we have absolutely no option to change anything in the Db. What we do have control over is the data and how it goes into the Db.

I just created a new table as a test with 2 fields: ClientId, Note and entered a number for the client and "Line1" for the note. When I do a Ctrl Enter it moves to the 2nd line where I typed "Line2. Then again for "Line3".

Basically, that's all I'm trying to do with the INSERT statement and the 9 text fields.
And just to make a point - I then created a new form with 2 fields - ClientId, Note with corresponding control sources from the table.
The record displays EXACTLY the way it I want it to with multiple lines.

So, going back to my original question without everyone informing me it's a stupid idea, can you help me determine what code should I use to replicate the Ctrl Enter I used manually?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38326870
<without everyone informing me it's a stupid idea>
We did not know of your design constraints before now.
All we were trying to say was that this is not as easy as it seems, if you wanted to keep your original design..
;-)

There is no way (that I know of) to easily build a line break into an SQL statement.

But once again, it can be done in a 2007, but it has the potential of being a nightmare logistically.
...And with your constraint of not being able to control how data is entered, ...it may not even be possible...

Jeff
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 38326941
0
 

Author Comment

by:SpaceCoastLife
ID: 38341721
I've requested that this question be deleted for the following reason:

Apparently, there is no option to accomplish what I wanted to do. None, in any event, the Experts could identify.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38341722
I posted a sample that does precisely what you asked for.
http://filedb.experts-exchange.com/incoming/2012/08_w34/598595/Access-EEQ27840023HTMLRichTextIn.accdb

Did you investigate it?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38342098
I've just looked at Jeff's sample and I agree that it does exactly what was asked for in the question.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 38342374
Agreed, it seems to do exactly what it said on the tin!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38348967
...?
So will you be accepting my post?
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question