Thank you for responding. I will try and explain what I am designing.... We
have a retail appliance business and right now we are using Sales
tickets/invoices that are filled out by hand. They require a large amount of
data (i.e.): sold to and deliver to customer name/addr and 4 phone numbers,
7 fields for model number, color, brand, etc. (ten lines of this type in case
10 products are purchased), we also have various fields for notes about the
purchase/delivery info, ticket status, dates, etc.
Right now in designing this database to mimic our form,
That's your first mistake.
Designing table structures to fit a paper form is GUARANTEED to
produce a non-normalized, faulty design, and that's what you're
seeing.
The table structures should be based on a logical analysis of the
information to be stored: for example, a one to many relationship
between Customers and Phone Numbers, ESPECIALLY one to many
relationships between Customers and Orders, and between Orders and
OrderDetails.
You're using a relational database. Use it relationally.
essentially, we are
trying to just have a computerized copy of our tickets. I already have over
130 fields and I am not done yet. We DO NOT want a customer database, as we
need the sales ticket to say exactly how it was entered in, so if it was tied
to a customer database and their address is changed, it would change our
ticket.
Not if you store the address in the Orders table (copying it from the
Customer table). It all depends on how you decide what Attributes each
table has - if the Address attribute is "the customer's address at the
time of the sale, never mind if they changed it afterward" then it's
an attribute of the Order entity, and needs to be stored in the Order
table.
On the table "Orders" in my database, I have listed 10 lines for product,
each having their own fields (i.e.) Qty1, ModelNumber1, Color1.......Qty2,
ModelNumber2, Color2, etc. Some of these fields are a lookup field.
So you're saying that if a customer purchases eleven items, you must
store their name, address, phone numbers, etc. etc. redundantly in a
second record?
This is simply WRONG. You have a one (order) to many (items)
relationship. Access can do this very easily if you model it as a one
to many relationship; if you MISUSE Access by jamming the entire one
to many relationship into one record, you will have trouble (as you
are in fact having).
There
are various prices, some with tax and someout with tax, so the unit price and
extended price are not a calculated field, the salesman will input these
himself. Our staff is not very computer literate and the easier I can make
it for them, the better. Also, I have experience in databases, but am
learning Access as I go along. I think I have a pretty good handle on it.
Ummm... you might want to do some reading about normalization, because
I fear that you are on the wrong track.
I know this is not the normal way of designing a database and now after
learning of the limitations with only 2000 characters and 2GB of storage for
all, I am getting scared. We do a great deal of business and would like to
store all the records indefinitely.
If 2 GB is limiting, consider using SQL/Server or another
client/server database (Access makes an excellent frontend)... but use
it relationally. If you insist on your current design, you would
perhaps be just as well off to store each ticket in a Word document
with a ten-line Word table for the detail items - what you're creating
is NOT a relational database!
We would like to start using this database as soon as possible, I would
greatly appreciate any suggestions that you might have for me. Thanks in
advance.
Read (or reread) some of the guidelines about relational databases
listed at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
and seriously consider using a normalized database structure... or
abandoning relational databases altogether, if you don't want to use a
normalized structure.
John W. Vinson[MVP]
