imprting text from Excel

[Dovidle]

I have an Excel workbook that contains cells with a lot of text. The text contains line-breaks. These were inserted using Alt+Return. (Return on its own does not produce a line-break, but moves you to the next cell.)


When I import this table into Access, the line breaks are removed.
I have the same problem if I try just to link my Access table to the Excel workbook.
Help, please.

[TheShabz]

Why would you need a line break in a database table to begin with? Access and Excel are two totally different things. Access shouldn't be treated as Excel on steroids.

[Dovidle]

Thank you for your interest in my application, and why I need line breaks.

The database consists of several tables:

• a list of all the tunes in the repertoire of a band
• the venues we have played at each tune at
• several different announcer's introductions to each tune depending on the venue and whether we have played there before
• progress on each tune at rehearsals

Other tables may be added later. The primary key is the number of the tune written on the band parts, and in which order the parts are stored in the pads.

The tables that exist so far are in Excel, but could just as well be tables in Word.

I need to be able to extract, sort and print different selections and combinations from different tables to provide the basis for performances and rehearsals. The records containing the text of announcements etc need line breaks to make them easily readable, just like this message.

I do hope that this provides you with the information you need to help me solve the technical problem of why Access removes the line breaks when the cells are imported from Excel.

Thanking you in anticipation.

[TheShabz]

Line breaks are not needed at all in your application. Access tables are used strictly for storage. The fact that they exist visually is to make Access more user-friendly. In reality, you should not view the table as if you would an Excel spreadsheet. They are not the same.

You display your data on forms and on reports, NOT with the datasheet view of a table.

With that taken care of, here is how I would set up your tables.

-tblBands-
BandID (Primary key)
Band Name
other info

-tblSongs-
songID (Primary Key)
SongName
progressPerc (this is how far along a song is, via percent being my thought)
isComplete (a yes/no field that indicates whether a song is complete and may be used for a gig)
other info
bandID (Foreign Key)

-tblVenue-
venueID (primary key)
venueName
other info

-tblSongSet-
songSetID (primary key)
bandID (foreign key)
venueID (foreign key)
songID (foreign key)
introduction

The relationships should be apparent with my designations of primary and foreign keys. If it is not, Google is your friend. Now, I assume you would need linebreaks for something like one band having multiple songs. this is not the case. you would use one record (row) for each combination of band and song, each with its own field, as shown above.

Before you begin this database, I highly recommend that you read up on "relational database management systems" (RDMS) as well as "database normalization". You need to completely rid yourself of the spreadsheet mentality when working with Access. They are NOT the same.

Hope this is helpful. Let us know if you need any help. Also, if others would like to change the table layout, feel free!

[Dovidle]

Your answer was most useful. Unfortunately in the interests of brevity I did not give you enough information about the application, and in realising this, it has caused me to think more carefully about what outputs I want from the system, and therefore what tools I should use to achieve them.
Two typical outputs (among others) would be:
· A list of the song titles for a gig and the appropriate announcements to go with them. There may be several announcements to choose from for each song, depending on the occasion/time of year.
· A list of the song titles for a rehearsal and the appropriate detailed comments of the bits of the orchestration which need practicing.
Both the announcements and the rehearsal details are quite a lot of text which requires usual punctuation, including line breaks. (Some of the announcements are in verse!)
So taking a fresh view of this it seems that it is a text manipulation problem, and not a relational database application. I think I could do better by using a large table in Word, and selecting the rows and columns I want for each time I need the outputs. This will be a manual job because it involves operator decisions, but it could be made easier by creating macros.
I think that I do not need the vast power of the Access software which was really designed for a different kind of application. Your comment that Access tables are not meant to be viewed raw, but through another program such as Forms or Queries, made me realise that I need something much more WYSIWIG.
So that is the route I shall take and I am really grateful to you for changing my mindset and opening my eyes. I shall continue to learn Access (I have already done a lot of the online tutorials) so that I am ready when an opportunity arises. It is a lot of fun to use once you get the hang of it.
Thanks again and best wishes.