How to go from Excel to Access - pointers / methods

[Fred Marshall]

I need a hint regarding how to proceed.

I have an existing flat "database" of water customers in the form of an
Excel spreadsheet.
I need to move it into Access.
I know how to do it simply - so that's not the question.

There are two sources of data:

The Excel spreadsheet of customer names, date, water use, charges for the
period.

A table of customer names and charging codes.

Here is what I've done so far:

Reduce the spreadsheet down to a subset with a single date.
This has one entry per customer.
Relate the charge codes to the customers.
Generate the same charges in Access using a formula - compare to the
reported charges to test the formula.

Here's what I need to do and I'm having trouble figuring out how to do it:

Generate charges for each customer for each year - a subset of the months
available in the data.

I anticipate that there would be a field for each month's use and for each
month's charge as well as the total of all charges. Is this wrong thinking?

Where might I find tutorial information regarding converting Excel flat
sheets to an Access database that I might use as a tool?
(This isn't going to be an interactive database - just an analysis tool).
My books on Access are more about database design and features but not so
much at all about how to deal with *existing* data as I have here.

Thanks,

Fred

 

[Jeff Boyce]

Fred

If you are considering using one field per month, you are still in
Excel-mode. As a relational database, Access's functions and features work
best when you've taken the time to normalize your data. If this term is
meaningless, you have a bit of study before you can get Access to (easily)
do what you want.

Consider using paper/pencil to map out all of the entities of interest
(e.g., customers, charge codes, usage, ...) and their relationships. Then
you're ready to build the tables that will hold the data. And finally, you
can import the data from Excel into "temporary" tables, and use queries to
"parse" the Excel-structured data into your more-permanent data structures.

If you want to see a month's usage, use a query against the usage table.

If you want to see usage per month, use a crosstab query.

For more formatting features, first use a query to get the data, then base a
report on that query for printing out your results.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[Fred Marshall]

Jeff,

Thanks for the comments!

It may be that I could do all this with Exel but it seemed more direct to do
some of the things I need with Access.

If I said "one field per month" then that was probably a misnomer. In fact,
there is one record per month per customer. What I probably want is a
report with one *column* per month and a total year column. I hope that's
clearer.

The data comes from an ancient accounting system that provides ASCII reports
in not a very friendly format. (It's not in a database that I can grab with
Access). From there I have to manipulate the data into usable form -
generally Excel spreadsheets. Thus the form of the tables - generated from
reports that are available:

I have a flat table with customer, date, use and billed amount.
And, another flat table that provides customers, rate codes.
And, another flat table with rate codes and rates.

If I reduce the first table into one that contains a single billing date,
and combine the three tables with a query, I can generate the billed amounts
for a single month.
(This is a model for studies that will change the rate structure).
That seemed a rather major accomplishment!

In order to do the study I'll need to combine the billed amounts generated
into a year's worth of bills.

It's that latter step I'm working on and my limited experience with Access
causes me to ask questions. And, suggestions are greatly appreciated.

Fred

 

[John Vinson]

Jeff,

Thanks for the comments!

It may be that I could do all this with Exel but it seemed more direct to do
some of the things I need with Access.

If I said "one field per month" then that was probably a misnomer. In fact,
there is one record per month per customer.

That's better.

What I probably want is a
report with one *column* per month and a total year column. I hope that's
clearer.

A Crosstab report based on a normalized tall-skinny table will do that
for you.


John W. Vinson[MVP]