New To Access

[Templar]

Can't say I'm wild about access over excel.........yet.

I have a spread sheet that has 24 columns of different information.
I'd like to do side by side comparisons of 2 or 3 columns at a time.
Is there any way I can "rip out" specific columns and put them in a separate
file for comparison purposes?

Also, is there any easy way I can move a column on the 24 column spreadsheet?

Thank you.

 

[Arvin Meyer [MVP]]

Good news! it's called a query. And better yet, you can either save or
discard them without any danger of messing with your table.

 

[Pete D.]

Your message is confusing, this is a newsgroup for MS Access the database
program. You go on to talk about columns of information and spreadsheets.
Are you working in Access or Excel? If excel you would be better off asking
the questions in an excel group. In excel you can select columns and drag
them as needed to move them around or copy and paste into a new sheet as
needed. If your in Access although you can move the Fields around you
really don't sound like this is the program you need for your task.
Spreadsheets are in excel, tables are in access. Two very different animals
that only on the surface look similar.

 

[John W. Vinson]

Can't say I'm wild about access over excel.........yet.

That's a bit like someone who's owned a sedan trying out a delivery truck and
saying they aren't wild about it. They are VERY different programs and are
designed to do different things, and use different techniques. Access is *not*
"Excel on steroids".

I have a spread sheet that has 24 columns of different information.

In Excel you do; in Access you should have one or more Tables, properly
normalized and structured.

I'd like to do side by side comparisons of 2 or 3 columns at a time.
Is there any way I can "rip out" specific columns and put them in a separate
file for comparison purposes?

You can create a Query based on your table selecting any combination of fields
(there are no columns in a Table, other than visually).

Also, is there any easy way I can move a column on the 24 column spreadsheet?

Table datasheets are NOT SPREADSHEETS and should generally only be viewed for
debugging or design. They're not how you interact with data in your database;
instead, arrange and select the data using Queries and view and edit it using
a Form based on the query.

 

[Templar]

Thank you John, Arvin and Pete. I tried the Query thing and I wasn't getting
anywhere with it. I'll follow John's advisory and see what happens. Thanks
again.

 

[John W. Vinson]

Thank you John, Arvin and Pete. I tried the Query thing and I wasn't getting
anywhere with it. I'll follow John's advisory and see what happens. Thanks
again.

There's some good introductory information available, including some tutorials
on how to migrate from Excel to Access. Browse around:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Templar]

It appears as though when you sort a "column" on the main docment, other
columns do not sort along with it. This scews the data to the point of
worthlessness. Is there any way of making the whole document sort with the
column being sorted.

Thanks.

 

[Templar]

By the way, WORD has a feature in it that permits changing the case, i.e.
from upper to lower or to just the first letter being capitalized. Does
Access
provide such a feature? I'd like to change the all capitalized words in a
column
to lower case with a capital first letter.
Thanks

 

[John Spencer]

Is this an Access question or an Excel question? From your description I
would guess this is an Excel question.

Select the entire spreadsheet (or select all the data (all columns) you want
to sort)
SELECT Tools: Data Sort from the menu
Follow the prompts.

Access always sort the whole row.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Yes, Access uses the VBA function StrConv. In a query you would have
something like
strConv([FieldName],3)
which would change THE LITTLE RED ENGINE to The Little Red Engine

If you want to do this permanently then you would use an update query. If you
wanted to do it for just certain reports and forms then you would use the
expression.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Templar]

No. I regret to say that it is an ACCESS QUESTION. I'm working with it for
the first time and trying to get a grip on it.

 

[John W. Vinson]

It appears as though when you sort a "column" on the main docment, other
columns do not sort along with it. This scews the data to the point of
worthlessness. Is there any way of making the whole document sort with the
column being sorted.

Thanks.

Again: STOP.

You're thinking of Access as if it were a spreadsheet, or a document. IT
ISN'T.

You can drive nails with a crescent wrench, but that doesn't make it a hammer!

Step back, go over the tutorials I posted, and at least for the time being put
your expertise in Excel back on the shelf. It's doing you more harm than good!

An Access database *is not a document*. It's a container for multiple tables,
forms, queries, reports, etc.

An Access table *is not a spreadsheet*.

I have no idea how you're "sorting a column". You can create a Query based on
a table, and choose up to ten fields for sorting (in left to right order); the
records in the table will be sorted in accordance with that specification.
This does not affect what's in the table; the table can be sorted for display
purposes using the user interface tools, but that actually just creates a
hidden query; it does not affect what's stored in the table. The Table should
be viewed as an unordered "sack" full of records; your best bet is to use
Queries to select and sort the data in the table, and Forms to display and
work with it, and not use table datasheets for much of anything.

 

[John W. Vinson]

By the way, WORD has a feature in it that permits changing the case, i.e.
from upper to lower or to just the first letter being capitalized. Does
Access
provide such a feature? I'd like to change the all capitalized words in a
column
to lower case with a capital first letter.
Thanks

There's a builtin function to do so, though it's not exposed well in the user
interface.

Create a Query based on your table. Change it to an Update Query using the
Query menu item.

On the Update To line underneath the field that you want capitalized (let's
call it LastName) put:

StrConv([LastName], 3)

3 means "convert to Proper Case" - 1 is lower case, 2 is upper case, 4 and up
are various other language operations, see the online help for StrConv.

Run the query by clicking the ! icon.

Note that this will convert "McDonald" to "Mcdonald" and "de la Torre" to "De
La Torre", so you may need to do some cleanup.