order history database

[shadowsong]

we've been keeping track of up to 7 years of order history for about
6000 items in an excel spreadsheet, which as you can imagine is a
little clunky. the company finally sprang for microsoft access (don't
know why they didn't have it before) and now i need to figure out how
to import the excel spreadsheet into an access database in a way that
will be more useable.

currently the columns are something like this:
part #, alternate #, order1, qty1, date1, order2, qty2, date2, etc, up
to date38, with one row per part number.

what i would like is a separate row for each order. column headers
would be simply part #, alternate #, order, qty, date, but the data in
each row would be:
part1, alternate1, order1, qty1, date1
part1, alternate1, order2, qty2, date2
part1, alternate1, order3, qty3, date3
part1, alternate1, order4, qty4, date4
part2, alternate2, order1, qty1, date1
part2, alternate2, order2, qty2, date2
part3, alternate3, order1, qty1, date1

however, not all rows have all fields filled. in the new spreadsheet,
it should only create a row when the values that will go in order, qty,
and date aren't null.

any hints on how to do this?

this may be a stupid question, but this is the first time i've tried to
do anything in access, so i'm kind of at a loss.

 

[Ken Sheridan]

Don't concern yourself with the Nulls until you've imported the data into
Access. It’s a very simple task to remove the unwanted rows en bloc in
Access using a DELETE query. Copy the data in Excel for each order per part
into a temporary Excel worksheet. So for the first order you'd copy columns
1 to 5 into the temporary worksheet. Then you'd copy columns 1 and 2 and
columns 6 to 8 and paste these below the rows you've pasted in the first
step, and so on. You thus end up with a tall skinny worksheet rather than a
shorter fat one. Import the temporary worksheet into Access and delete the
unwanted rows with a DELETE query, using IS NULL as the criterion for the
relevant columns.

One you have the data imported and the unwanted rows deleted, the next step
is to split thins up into normalized tables. Normalization is the process by
which redundancy is eliminated. At present, with your sample data, we are
told that order 1 was on date 1 three times, and that the alternate # value
for part 1 is alternate 1 three times and so on. What you want to end up
with is a set of related tables which eliminate this redundancy so that each
'fact' is stated only once in the database. The elimination of redundancy is
very important for maintaining the integrity of the database as redundancy
leaves the door open to update anomalies, e.g. with a single table it would
be perfectly possible for order 1 to have different order dates in different
rows.

You can use Append queries to insert the data from the single table into a
set of normalized tables. Tables represent entity types or relationship
types between entity types. In your case you have entity types Parts and
Orders. Columns in tables represent attributes of the entity type, so the
Parts table would have columns PartNumber and AlternateNumber (I'd recommend
the avoidance of special characters like the # sign or spaces in table or
column names). The Orders table would have columns OrderNumber and OrderDate
(Don't use date as a column name; it’s the name of a built in function in
Access). Each table has one row per part r order, with PartNumber and
OrderNumber being the unique primary keys of the tables.

The relationship between Orders and Parts is many-to-many as each order can
be for one or more parts and each part can be in one or more orders. This
type of relationship is modelled by another table, PartsOrdered say. This
has two foreign key columns PartNumber OrderNumber which reference the
primary keys of Parts and Orders, thus establishing the relationship. A
relationship type is in fact a special kind of entity type and can thus have
attributes of its own. In this case the attribute is Qty, so the
PartsOrdered table would have a column for this.

Creating the queries to append data into these normalized tables is fairly
straightforward, but I'd recommend you take it step by step and concentrate
on getting your data into the single tall skinny table and removing the
unwanted rows from it first. We can then come back to the question of
normalizing it.

Ken Sheridan
Stafford, England

 

[shadowsong]

thank you, that looks like exactly the information i need. i'll have to
learn a lot more about access before i can actually understand your
whole answer (i understand the concept of tables within a database, but
when it comes to actually populating and using them, i get stuck), but
i can at least start on getting the data into access correctly.

in the mean time, any recommendations on "how to use access" books?

thanks for your help!

 

[Ken Sheridan]

There's a wide range of general primers available, so I'll confine myself to
one I know from personal experience as it was the first book I bought when I
started using Access. Its titled Running Microsoft Access and its by John L.
Viescas. Its published by Microsoft Press. It seems to be available for
versions up to Access 2000, but there is also one by the same author called
Microsoft Office Access 2003 Inside Out, which sounds from the description to
be similar. Amazon.com currently lists it at 31.49 USD. You'll find links
on the Amazon page for this book to other similar ones.

BTW I have produced a little demo which shows how data can be imported from
Excel into Access and then normalized in Access. I haven't put in on line as
yet, but if you'd like a copy mail me at
ken<dot>sheridan<at>dsl<dot>pipex<dot>com.

Ken Sheridan
Stafford, England