Exporting Data to Access

[Ian]

I have a client that sends data with coloumn headers in
excel and I'd like to store it in Access (for more
powerful search/sort, etc...). I can't find an easy way
to move the data from excel into Access. Any suggestions?

 

[JulieD]

Hi Ian

not sure that i would consider Access's search / sort functionality any more
powerful than excel's however, to store it in access you can import an excel
worksheet (open access, in a new or existing datbase choose file / get
external data / import).

You can then nominate to put this in a new table or append it to an existing
table and then you can work with it. However, i would consider this (under
normal circumstances) not the best thing as the resulting "table" in access
is equivalent to a database in excel and to use Access effectively you
really need a "relational" structure.

So if you'ld like to explain a bit more about what you want to do with the
data, i think we might be able to come up with excel functionality that can
assist you.

Cheers
JulieD

 

[Ian]

The purpose of the excel worksheet is to create a series
of pivottables based on individual transactions for
sales. The client sends me the last quarters sales info
in excel. I add it to the existing data then do a trend
analysis. Two main problems made me think it would be
better to first manipulate the data in Access then import
it into excel.

1. I use a bunch of categories (that the user defines)
to summarize the data (e.g. the number of sales based on
a client type in a specified period of time) but my
client never enters the info in a standardized way. So
categories they intend to be the same end up being
spelled 10 different ways. I could recreate the excel
spreadsheet for them with a vlookup or validation list
but I thought it would be easier to import it to Access
and use an append query based on common threads in given
ranges. I didn't think excel could do this. As an
example show on the "shirt" sales but have shirt spelled
10 different way and try to get it into a pivottable
without having to go thru each time and manually group
all the misspells.

2. Although I haven't had it happen yet I'm worried some
clients will have more than 66,000 transactions and I'll
have to partially summarize it in Access before importing
it to excel. For example trends in sales data by
quarter. If 3 years worth of transactions is greater
than 66,000 it won't (I don't think) fit on one
worksheet. If it doesn't fit on one worksheet I can't
make a pivot table of it to summarize the data by year
and quarter. On the other hand, Access can easiy
summarize it by month in a query which I can then import
to excel.

Thos were my two main concerns. Thanks Ian.

 

[JulieD]

Hi Ian

mmm, okay i've not played with pivot tables enough in access to say whether
or not they are easier / better or equal to the excel ones ...

so if i'm understanding you correctly you'ld like to set up an access
database that you can import the client's data into every three months and
use the structure that you create there to manipulate the data?

in this case i guess you can go 1 of 2 ways:
1. create a table (ie called tbl_clientdata) which when you import (as
per the method outlined in my post) you can append the new records onto the
bottom of this table and build up the records over time - this requires
that the column headings don't change between imports
or
2. have the tbl_clientdata table where all your records are but import to
a new table and then use an append query to get these new records into
tbl_clientdata. This method does not rely on the column headings being the
same between imports (and given your clients lack of consistency might be
the way to go).

as for the inconsistency in data i would be tempted to build up another
table (ztbl_categorycomparison) over time with the "wrong categories" and
"right categories" listed so that when the new data comes in you can
populate an additional field called "right categories" using an update
query, for example

client data right category
shirt shirt
shirts shirt
shirst shirt

this ztbl_categorycomparison table can be initially created by doing a
make-table unique records query on the first lot of data you bring in (this
will give you all the categories used by the client), then you need to
manually enter a right category label for each - (time consuming i know) but
in the future you can just run an update query to populate the new field (in
the tbl_clientdata) and then do an append query (to ztbl_categorycomparison)
where right category is blank (in tbl_clientdata) to ztbl_categorycomparison
table - assign the right categories and then run the update query again ...
i think this will save you time in the long run (if, of course, you can make
sense of what i've just said!).

Hope this helps
Cheers
JulieD

 

[Ian]

Thanks Julie,

The snag a ran into was trying to put the data into an
exisitng table directly from excel to Access with
inconsistant table headings. Creating a new table and
running an append query is a lot easier.

I'm going to have to work thru the append query
methodology to make consistant categories. One question -
how do you run a make-table unique records query? (I've
created queries to sort out unique records but it wasn't
simple and I had to type out the methodology so I
wouldn't forget! Your's sounds like a one-button
solution.

Also if I'm reading you method correctly once my correct
category table is populated each time I'll need to run
two append queries. 1. to clear out the wrong names 2. to
repopulate the blanks with the correct names. Is that
right?

 

[onedaywhen]

create a table (ie called tbl_clientdata)

There are international standards that cover such things (ISO/IEC
11179 Specification and Standardization of Data Elements) and of
course you are free to choose to ignore them. But don't you think
'tbl_' as a prefix and 'data' as a suffix are both redundant?

Aside: ie means 'that is', I think you wanted eg meaning 'for
example'.

not sure that i would consider Access's search / sort
functionality any more powerful than excel's

No, neither would I. But then I don't see any reason to involve the MS
Access application. Rather, the OP only needs to use native MS Jet
tables. And there is no question that Jet's search / sort
functionality (via SELECT queries) is more powerful than Excel's.

Other than that, you've pretty much lost me. I don't know about the OP
but 'a make-table unique records query' means nothing to me! I think
you may have misread the post's title as 'Import Data In MS Access'.

--

 

[Ian]

Hey Julie,

I've walked thru all the steps with a test 10,000 unique
records seperated into 2 sets of 5,000. The test was to
import the first 5,000 and make 10 pivot tables and pivot
charts for analysis then add the next 5,000 and update
the pivot tables & charts with 1 button. Here's how I
did it:

Import the excel data to the db (File>>Import) and save
it as "table_1")
Copy this as "master_table"
Go to excel and create pivot tables and charts using the
db as an external data source
To add the next set of data
Import the excel data to the db and save it as "table_2"
Turn table_2 into a query then modify it to an append
query to add to the master_table (this adds the data and
lets me specify the rows - just like you said)
Now my master_table is updated -- go back to excel and
hit update table (!) -- Done!

As an aside, rather than creating an update query to
change all the data I found it easier to create queries
based on the specific info the client needed (in
yesterdays example, rather than modifying all the
misspellings of shirts I created a query that looked for
the string "shir*" which should catch most of) then
created my pivot tables from these queries rather than
the master lists.

Thank you very much for the help. Rather than hours of
updating data ranges for pivot tables this is a one
button alternative.

Ian.

 

[JulieD]

i'm sorry - you are?????
and what practical advice did you provide exactly?

 

[JulieD]

Hi Ian

glad it worked for you
just for future reference -
to use a query to build a new table of unique values
you add the source table to the query window
double click on the field that you want in the new table (so that it goes
into the bottom of the query window)
change the query type to "make table" and give the table a name
right mouse click next to the source table in the top of the query grid
change unique values to "yes"
and run the query
- this extracts the unique values to a new table for you.

Cheers
JulieD

 

[onedaywhen]

i'm sorry - you are?????

I'm an Excel user. But you could have found this out for yourself.
Follow this link:

http://groups.google.com/groups?q=author:[email protected]+group:microsoft.public.excel.*

for confirmation.

and what practical advice did you provide exactly?

Lots, but often I'm too subtle. For this *I* apologize. Let me be more
direct:

There are established naming conventions. I assumed you had not read
ISO/IEC 11179 Specification and Standardization of Data Elements so I
name checked it. Find it via the internet and read it. Then you will
then be better equipped to suggest table names.

A table is a table and it contains data. This implicit, there's no
need to use the prefix nor the suffix you employed. You don't sign
yourself person_JulieHuman, do you?

You incorrectly used 'ie' in place of 'eg'. When it comes to choosing
a name, make suggestions (using eg) rather than prescriptions (using
ie).

The OP asked about exporting in Excel and your reply addressed
importing in MS Access (not entirely your fault because the OP has
mistaken Jet for MS Access). Assuming you can tell the difference, try
supplying a solution that uses Excel not MS Access.

MS Access speak has no place in an Excel forum (like me the OP didn't
seem to know what a 'make-table unique records query' means). Express
this phrase using product-independent syntax i.e. ANSI standard SQL.

--

 

[onedaywhen]

There are established naming conventions. I assumed you had not read
ISO/IEC 11179 Specification and Standardization of Data Elements so I
name checked it. Find it via the internet and read it. Then you will
then be better equipped to suggest table names.

I've just found out the cost of these documents - USD 450! So this
*wasn't* such practical advice after all. Instead, take a look at this
link which includes a good (opinionated) summary and FTP links to more
in depth (non-opinionated) summaries:

http://groups.google.com/groups?c2coff=1&[email protected]

--