Advice on tables 1 vs multiple

[Iwilfix]

I have a datatbase containing e few tables( mailing list, client list
etc) and i have to start a new one.
This table would have to contain customer info, contract info(such
as exp date, type of contract, invoice # etc) and equipment info
( make, model #, serial #, etc)
In all aprox 80 pieces of info per record.
Is it better to create one table with 80 fields?
or would it be better to separate them into 2 or 3 tables.
Mind you I am such a newbe, and your answers will
probably cause a flood of questions on design.
And would start with how to connect 1 table to another.

as always, thank you, Jeff V.

 

[Tim Ferguson]

This table would have to contain customer info, contract info(such
as exp date, type of contract, invoice # etc) and equipment info
( make, model #, serial #, etc)

Well, you have already mentioned a number of different entities that need
their own tables: Customers, Contracts, Equipment; and probably
ContractTypes, EquipmentManufacturers, Invoices and so on.

In all aprox 80 pieces of info per record.
Is it better to create one table with 80 fields?

A hint: a really wide table would get up to twenty or so fields. Eighty
fields is in desperate need of normalisation.


I have to wonder at postings like this, whether there is a reason for
creating your own business database rather than using one of the commercial
ones on the market. Is this your own business you are going to trust to it,
or someone else's?

Best wishes



Tim F

 

[John Vinson]

I have a datatbase containing e few tables( mailing list, client list
etc) and i have to start a new one.
This table would have to contain customer info, contract info(such
as exp date, type of contract, invoice # etc) and equipment info
( make, model #, serial #, etc)
In all aprox 80 pieces of info per record.
Is it better to create one table with 80 fields?
or would it be better to separate them into 2 or 3 tables.
Mind you I am such a newbe, and your answers will
probably cause a flood of questions on design.
And would start with how to connect 1 table to another.

as always, thank you, Jeff V.

Jeff... I REALLY suggest that you should take a course in Access, or
get - and read carefully - a good book on introductory relational
databases. John Viescas' _Running Microsoft Access_, _The Access
Bible_, Que Press _Using Microsoft Access_ or others.

You've been posting a number of questions here which such a book or
course would answer, in an integrated and organized way rather than
piecemeal.

To answer your question... NO. A table with 80 fields might need 83
fields tomorrow. "Fields are expensive, records are cheap".

You would instead have a relationship from your contract table, one to
many, to an Equipment table, by putting the ContractID into the
equipment table. If a contract involves twenty pieces of equipment
you'ld add 20 rows to this table, probably using a subform.

 

[Iwilfix]

Tim

I understand what you said about limiting the fields.
Each bit of info is important, but wanted to know what
is the best way to organize it. This is my own business,
And many people have told me that Microsoft Office would
have everything i need to do so. I use Outlook, Word, And Publisher
reguarly. I use a very old version of PCFile for my mailling list and
customer info, and was hoping to incorperate Access for this purpose.
I have learned quite a few features that I like. And some that I have
requested help on in this and other newsgroups.
I would hate to have to purchase yet another program not knowing
what it can or cannot do. I know what I want to do. Just not always
how to get these results without help.
John Vinson has sugested to purchase guide books or take a course.
I will probably end up doing this. but for me it always seem to work
better hands on and experiment, so I'll trudge on a bit more.
Hope you don't mind if I pick your brains more in the future.
I now understand that most of you here are volunteers
Well Kudo's to you, and I for one certainly appreciate all the info
you guy's have given me already.

Thanks very much and al the best.

Jeff Vandenberg.

 

[Iwilfix]

John

Your input not withstanding, but i have never been
good in comprehending theory to practice.
I work well from visual examples. Show me once,
I remember forever. I know this is unorthodox to so
many people. I will probably try to take a course in
the future. But for now need to work with what I have
I have tried to get info from Access Bible, and the for dummies
book. but always seem to get stuck at some point or another.
Anyway, thanks for all the help you have already given me in the
past. It has accomplished quite a few things I needed it to do and
am already much further ahead than I was able to do with my older
program.

Best wishes. Jeff Vandenberg

 

[Tim Ferguson]

I understand what you said about limiting the fields.
Each bit of info is important, but wanted to know what
is the best way to organize it.

Well, yes -- and that is the way I was pointing you.

This is my own business,

And are you really ready to bet it on your beginning knowledge of
Relational Theory, systems analysis, data flow and document flow
management, and so on?

And many people have told me that Microsoft Office would
have everything i need to do so.

Again, yes but. A pen and some paper is apparently everything you need to
write a blockbusting film script -- but actually you need a bunch of
technical knowledge, literary and language skills, experience and some
other way of earning a living while you are learning.

I use Outlook, Word, And Publisher
reguarly.

But Access is very different from the other products, which are very much
pick-them-up-and-start-typing. There really is a chunk of technical
knowledge and analytical skills that you need in order to achieve anything
useful with it -- you can come by these in books and on courses, and
possible even some free www page tutorials. But it won't come by accident,
and you won't be able to catch up forty years of maths research and theory
in a couple of weeks trial and error.

I would hate to have to purchase yet another program not knowing
what it can or cannot do.

And I would never expect you to do so, although I suspect that that is
already the case with your Access licence. In your place, I would be
researching all the standard contact manager/ inventory management software
already there, with the usual business case/ requirements analysis/ best
fit/ value for money/ etc process. A self-build might be in the list, but
usually won't score very well on reliability or supplier support (remember
if the client goes bust, so does the supplier!). There's that dictum about
lawyers: "anyone who represents himself in court has a fool for a client".

I know what I want to do.

With respect, and having re-read your original post, I don't agree.

Hope you don't mind if I pick your brains more in the future.

Absolutely not: that is why we hang around here. Do post back, but bear in
mind that the most successful answers are given to the most precise
questions. Posts like, "how do I build a database" usually get very general
answers like this one or John's. Sorry it's not what you want to hear, but
it's well meant nevertheless.

Best wishes



Tim F

 

[Iwilfix]

You've just scared the hell out of me.
I have a small service business, and was just looking
a way to keep my customers organized by keeping tabs on
their equipment, service records and renewal scheduals.
As I stated before, I have certain ways that I would like this
info to be accessed and displayed. And again was assured
that Microsoft Office would have everything i needed.
Thats like you buying a Fridgidaire washer because you
were assured by the dealer it is exactly what you need.
2 years later you call me for service, And I tell you
You should have bought a Maytag and then scoff to boot.
I do not have those skills you mentioned. Nor the time
to learn them. And If I did, Why would earn a living
repairing appliances, I would be writing codes and setting
up databases for other businesses.
So access is overkill and too complicated for my needs.
And many other businesses as I now understand. And
I now feel totaly taken advantage of.
All fairness though. In the 10 questions I have posted to this
group, and the solutions I was given to them. I have been
able to do at least twice as much with Access then with my
old database program. Perhaps it is because you guys are here
anwering the very hard( for me anyway ) questions. Whereas
That other vendor is no longer here to help me. I guess that
is the true meaning of support.
As always, thanks for your help. Jeff

P.S. By the way, do you still know anyone who writes a blockbuster
script with a pen and paper, or even a typewriter for that matter.
They are probably using a laptop with spell an grammar check,
besides their language , literary skills. And have to learn how to
Save and store their work, and retrieve it when needed.
How can they learn that without asking? Even if those
questions seem very insignificant to a person with computer skills.

 

[Tim Ferguson]

You've just scared the hell out of me.

I meant to a bit...

Thats like you buying a Fridgidaire washer because you
were assured by the dealer it is exactly what you need.

Actually it's more like being introduced to a warehouse full of timers,
motors, hinges and doors and being told there is everything there you need
to build a washing machine. And get on with it.

Office -- or Access in particular -- is a very powerful, hugely versatile
system that can be used to acheive an enormous variety of tasks in many
many situations. That's not quite the same as saying that any one person
could do all of them, nor is to say that all those tasks are easy.

I do not have those skills you mentioned. Nor the time
to learn them. And If I did, Why would earn a living
repairing appliances, I would be writing codes and setting
up databases for other businesses.

I hope I didn't oversell this: I am a "hobby user" and don't make any money
from Access. Although I have postgraduate qualifications in informatics and
have used Access since ver 1.1 ten years ago (and dBase before that,
etc...) you certainly can get useful results from Access in a much shorter
time than that. The difference here is between running your business and
your video collection or the mailing list for the bowling team. How high
are the stakes if the thing goes wrong? If it were my job on the line, I
would want a professionally-written, professionally-supported application,
even with the experience that I already have.

So access is overkill and too complicated for my needs.

No:- probably underkill and not set up for your needs. Remember, if you do
buy in a solution, it may well be based on Access anyway!

All fairness though. In the 10 questions I have posted to this
group, and the solutions I was given to them. I have been
able to do at least twice as much with Access then with my
old database program.

Absolutely: I don't think that Access is the problem here as much as the
expectations raised by the other members of the Office bundle. You don't
need very much back-knowledge to pick up and start typing into Outlook or
Word; the number of really crappy presentations we all have to sit through
is a reminder that you do need extra skills to use Powerpoint properly. At
the top end, Excel really works best for people who have some understanding
of maths, and Access is way beyond even that.

All the best


Tim F

 

[Iwilfix]

I think I now understand which way you are steering me.
I certainly would not have even attempted to run my
bookkeeping and finances through Access and I guess
thats where you thought I might have been going. I do
use another application for that. A dummy proof one.
As I said, I just needed a place to store Info on my customers
and their equipment. So when one of them calls me for service,
I can find out exactly what type of equipment I'm dealing with,
with just a few key strokes. Rather than relying on their input
Often when I ask them what kind of Refrigerator they have,
the response is that they don't know, or that is a white one
( ask them what kind of car though, and they'll give me make,model,
when it was built and what plant. they know the millage and VIN by
heart)
Anyway, at this point these are my only intension for Access.
Hope that still qualifies me to be able to ask questions here.
Because my only "writing" skills go back to1977 in high school
I learned a little basic, fortran, and cobol. which I now have
forgotten.

As always, thanks for your help and patience.
Jeff