Maing a Imported spreadsheet to an Access table look professional

[Lou_Reed]

After I import an Excel spreadsheet into Access, there are various cleanup activities that I must do. One is shown in the graphic attached to this post.

The titles of the fields are messed up. It shows for example :

Course Nam w

when it should show

Course Name.

There should be no leaving off or cutting off the name's letter or letters in a column name. I can fix this by hand, but is there a command that I can give that shows the titles in
all the tables with no cut off?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed



I know the first word in the title should be Making not Maing. I cannot change it. Which brings up a second question, how do you edit and correct titles on a Post?

[ranman256]

does the XL file have fieldnames in Row 1 for EVERY field?

[Lou_Reed]

I am not sure what you are asking. Please elaborate, assume. I know little about MS Access table formatting.

I am guessing at your questions and the answer is yes. They do.

In Row 1 of the Access Table each filed has a name. What I do not like is
cutting off the ends of the names. It just looks better if that does not happen.
Sure any intelligent person can make out what the name is, I just want it to not happen.
How can I fix?

Respectfully,

Lou Reed

[June7]

This should be irrelevant as users should not be viewing tables and queries, only forms and reports.

We have already pointed out that your data is incomplete and not normalized. San Diego shown in the second record has no association with Electrical Cont record above it.


Should be able to edit post content and title with the Edit Post button. At least for a time. The button will eventually deactivate for a post.

[Lou_Reed]

I know what you are saying. But what can I do. You are the expert not me. I am merely a hired hand. I am trying to do the best I can. I can only hope that
the end user will listen to reason.

I had several database course in college, I have never seen anything like this.

You are right on all accounts, but what can I do. This is the way they want it.

Respectfully,

Lou Reed

[June7]

Are you an employee or a contractor of the 'client'? If I were a contractor, think I would not bid.

And I am no expert. Never formally trained nor certified. Just a few computer tech classes and learned Access on the job.

I am not aware of any code that can set the widths of fields in table so the row headers will fully display. If there is, likely involves TableDefs.

If you are talking about a form in Datasheet view, maybe.

Width of column is measured and set in TWIPS. 1 inch = 1440 TWIPS. Converting the number of characters in a string (the column header) to width in TWIPS is tricky, virtually impossible if the font is proportional.

The following code will set column width to the length of column header or data, whichever is greater:

Me.fieldname.ColumnWidth = -2

How your clients expect unprofessional construct they demand to look professional is beyond me.

[Lou_Reed]

I am an employee of the United States government. I got this project, I guess, because no one else wanted it or else I could run fast enough.

I am trying to do a good job, but my supervisor (who I am sure knows nothing about databases) wants it this way. They are the client. I will not suffer because it is a poorly conceived
idea; at least I hope not.

I am really trying to work under less than an ideal situation, but who knows this may be what they want. I keep telling them again and again it is not really a database.

I know in the "real world" so to speak one is always making compromises. Nothing is as clean cut as it is in college textbooks.

I am thinking of just turning this over to them and writing a short paper on why I think it is wrong.

I am really out a of ideas.

This may be the only choice that I have (writing a paper short but sweet that says why this is not a real database).

I do have one shop question, however. How do I unhide hidden columns in an Excel sheet? I just want to do it on the whole spreadsheet.

Please let me know if there is a way.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[CJ_London]

resizing the columns of a table, query or a datasheet form is much the same as excel. select the column, using the mouse, hover of the right side of the columns header (see the mouse pointer change to west/east arrows), mousedown and drag for manual resizing or double click to autoresize. To select and resize all columns click on the square carat top left on the header then as before, hover over any right side of any selected column and double click.

In a datasheet form, you can also use vba code to resize. reference the control (but not for labels) columnwidth property and set to -2. Something like

dim ctrl as control
for each ctrl in me.controls
    if ctrl.controltype=actextbox then ctrl.columnwidth=-2
next ctrl

note that like all datasheet properties, the columnwidth property is not shown in the controls property sheet

see this link for more information
https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

[June7]

To unhide all hidden columns, select the entire worksheet by click on the upper left corner of the grid, then right click over any header > Unhide

[John_G]

Can I ask WHY they want this spreadsheet put into Access? What exactly do they expect to be able to do with the data once it is in there? Do they not understand that Access is not Excel, does not work like Excel, and does not look like Excel (most of the time). Just importing this spreadsheet into Access and expecting to be able to do anything useful with it is an exercise in futility - it's not going to work. Once your sponsors are able to detail exactly what the database is to do (and that is their job, not yours), only then can you begin to analyze the data and determine what the Access structure should be (never mind forms and reports - they come later). A quick look at the spreadsheet indicates to me that you will need at least six tables - probably more.

You and the sponsors are going to have to meet to sort all this out, otherwise you will continue to be frustrated. Although from the sounds of it this might be impossible, you must try to make them understand that although they tell you what they need; how it gets done is your job.

[ArviLaanemets]

So this is a datasheet view in Access? Never used one, so at first I thought the picture is from Excel

Field names F49, F50 etc. indicate, that in Excel those columns are without names in header row.

My advice is, never let users near tables in Access - they must be hidden from users. Users see forms, and can view or print reports.

And when thy want to work like in Excel, they have to use Excel!