Comments/help on database design

[nokamoto]

I work in the art dept. of a wholesale company and have created an Access
database of our products which is used to create a catalog, price list and
other marketing materials; traffic the print packaging for each item; and
record quality control issues for each item's packaging. I've been using the
database for several years, however I was never quite pleased with its design
(due to my lack of expertise with Access) and would welcome any suggestions
on how to reconfigure and streamline it.

There are two main tables, one for product info and the other for packaging
info, linked by item no. The way my database is set up, the two tables and
their corresponding forms are not well integrated. When I make a new record
for an item in my products table, I'd like to be able start entering package
tracking information for the item as well. Right now I'm doing an append
query to the Pkg Tracking table, to create a corresponding record which seems
wrong. When I first created the database, I didn't want to have all 80 or so
fields stored on one table, thinking that many would be unwieldly, but maybe
there's no way around it.

Specifications for the Products Database

Table of Products
- basic fields: record no (key), item no, name of item, description,
manufacturer, country of origin, UPC, pricing, packing info, packaging type,
new/phaseout/discontinued

Table of Packaging Tracking
- basic fields: item no (key), packaging type, dates for box/insert/stamping
art sent and approved, problems with packaging, dates for problems noted and
resolved

Other related tables: Manufacturers, Country of Origin, Packaging Type,
Retail Category

 

[Jeff Boyce]

To start with, your 'packaging' table appears to have repeating fields. The
[dates for ...] field(s?) and the [problems...] field(s) sound like there
are multiple instances. If so, this is pretty much how you'd have to do
this using a spreadsheet.

Are you familiar with "normalization" and "relational database design"? If
not, they present a bit of a steep learning curve, but are essential to
getting the best out of Access. Access' features/functions are optimized
for well-normalized data, so when you feed it 'sheet data, both you and
Access have to work overtime to overcome the inefficient data structure.
It's a bit of a "pay now or pay later" situation, as you are finding out!

Good luck!


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bhicks11 via AccessMonster.com]

Hi nokamoto,

If you have a form with the Products table as the datasource and add a
subform with the Packaging table (just drag the Packaging table to the form
and you will be prompted for the link field) you can add packaging data each
time you add product data.

Make a back up of your database and play with it.

Bonnie
http://www.dataplus-svc.com

 

[nokamoto via AccessMonster.com]

Thanks Bonnie, that's a really great tip. I'm going to make a copy right now
and start playing around!

Niko
"I'm a graphics designer, not a database designer"

Hi nokamoto,

If you have a form with the Products table as the datasource and add a
subform with the Packaging table (just drag the Packaging table to the form
and you will be prompted for the link field) you can add packaging data each
time you add product data.

Make a back up of your database and play with it.

Bonnie
http://www.dataplus-svc.com

I work in the art dept. of a wholesale company and have created an Access
database of our products which is used to create a catalog, price list and

[quoted text clipped - 28 lines]

Other related tables: Manufacturers, Country of Origin, Packaging Type,
Retail Category

 

[bhicks11 via AccessMonster.com]

Your welcome Niko!

Bonnie
http://www.dataplus-svc.com

Thanks Bonnie, that's a really great tip. I'm going to make a copy right now
and start playing around!

Niko
"I'm a graphics designer, not a database designer"

Hi nokamoto,

[quoted text clipped - 13 lines]

 

[nokamoto via AccessMonster.com]

Jeff, those terms are unfamiliar to me, sorry! Creating and managing a
database is not my primary job, there was simply a need for one -- initially
mostly by me, for importing data into InDesign for our print pieces, but the
uses gradually expanded and the database is now showing its weak points.

Actually, the only redundant field I have between the two tables is the item
no. I set up the Packaging Tracking table with a field called "item #", so I
could run a comparison with the Products table ("item no.") and then append a
new record if one did not exist. Yes, I know this was a lame workaround that
I was eventually going to have to fix. As Bonnie suggested below, I think her
idea to add the Packaging Tracking form as a subform makes sense, as it needs
to display some fields (the ones you noted as possible repeaters) from the
Product table anyway.

To start with, your 'packaging' table appears to have repeating fields. The
[dates for ...] field(s?) and the [problems...] field(s) sound like there
are multiple instances. If so, this is pretty much how you'd have to do
this using a spreadsheet.

Are you familiar with "normalization" and "relational database design"? If
not, they present a bit of a steep learning curve, but are essential to
getting the best out of Access. Access' features/functions are optimized
for well-normalized data, so when you feed it 'sheet data, both you and
Access have to work overtime to overcome the inefficient data structure.
It's a bit of a "pay now or pay later" situation, as you are finding out!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I work in the art dept. of a wholesale company and have created an Access
database of our products which is used to create a catalog, price list and

[quoted text clipped - 40 lines]

Other related tables: Manufacturers, Country of Origin, Packaging Type,
Retail Category