New Database

[Avery]

I am new to Access and my project is this...having trouble with designing my
tables.

My contacts are both my colleagues and customers. Both have a facility
affiliation with its own address, and the contact itself has its own address
and contact info.

Ex.
Company XYZ (Either a customer or a company facility-one of our five
manufacturing plants...if this company is a customer, their are add. fields
needed on form, do I just put them on this table and just only select the
fields needed in my form?)

Street
City
State(lookup table)
Phone
Fax
Website

Contact (Either a colleague-may reside at the plant. ie (sales manager,
engineering manager, etc.--only outside sales need separate address; Customer
contact only needs separate phone numbers..)
Street
City
State(lookup table)
Phone
Cell Phone
Fax
Email
(other contact info)

I will need to create a Customer Contact Report, Colleague Contact Report,
Quote Report & Sold Jobs Report(Totaled and by customer, by mfg. facility,
simple count of all quoted and sold).

This is a one user database to help our sales people keep up with customers,
quotes, jobs sold, commissions, follow-ups including competitors pricing,
etc.)

 

[Jeff Boyce]

Avery

Unlike Word or Excel, what Access works best with is not something folks use
everyday. With Word, you play with, well, words. With Excel, you play with
numbers. With Access, you ...?!

Access is a relational database, and is more a tool for building
applications that a bookcase/application (like Word/Excel) in its own right.
If you aren't familiar with "normalization" or "relational", plan to spend
some time coming up to speed on them before you can expect to get the best
use of Access' relationally-oriented features/functions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Accesss MVP

 

[Avery]

Jeff, I bought the Access 2003 Bible by Cary N. Prague, and I have a degree
in Computer Design Systems. I am just flustered about getting it to the 3rd
Normal Form. Two Week Brain Cramp. I have tried it several ways, and each
time I get to the query and form portion, I am unable to pull the info from
my tables to get the form to display correctly.

I only have two contact types

Colleague or Customer, but within that I have several contacts, some need
addresses, others do not. What tables do I need?

 

[Steve]

I suggest two tables; one for colleagues and one for customers. In both
tables, keep the common fields with the same name. When you need to pull
data from both tables simultaneously, use an Union query. For the table with
less fields, use dummy fields. An Union query requires the same number of
fields from both tables.

Steve

 

[Jeff Boyce]

Avery

That will really depend on your situation. Based on the information you
originally posted, it sounds like you are recording much the same
information about both Colleagues and Customers. Let me ask, can one of
your Colleagues ever also be a Customer (either simultaneously or at two
different times)?

And more ... are you interested in or trying to maintain a "history" of
information (like when someone was a customer and when s/he was a
colleague)?

There's no exact correct answer -- normalizing is both art and science, and
depends on your situation.

To help you nail down a table structure, I think I need a bit more
description. Perhaps one of the other newsgroup readers has already come up
with a table structure for your exact situation?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Accesss MVP

 

[Fred]

As a developer, I'm only at about 5% of Jeff's level, but I've been creating
apps just like you discuss and implementing them at small comapnies I run for
a long time.

My "general case" answer would be:

1. Define your data storage mission and your functionality mission

2. Define the content, nature, organization and real world relationships of
the data to be stored

3. Define a table structure which will support accomplishment of your
missions.

4. Create other items (queries, forms, reports, code etc.) to accomplish
the required functionality.


You probably need to spend a little more time on #1 - #3 vs. what appears to
be your current focus on #4.

The best structure will be somewhat customized to your needs. In these
types of applications, you need to design a structure that does OK with 100%
of your potential scenerios and well on 95% of your scenerios. If you try
to get that last 95% up to 100% you will probably end with a system that is
so complicated that it would die under it's own weight.

While I don't know the answers to #1 and #2 will enough for your situation,
my guess is that the following might be a good structure for you:

(of course, shorten the field and table names)

A table of Companies / organizations at a particular location. PK =
CompOrgIDNumber. Each instance of a company/org at location gets a record.


A table of people, FK = CompOrgIDNumber, linked to that field in the
companies / orgs table.

ALL companies, orgs and people go into the above tables.


A table of action items and events with FK = CompOrgIDNumber, linked to that
field in the companies / orgs table.

One or 2 more main tables with items related to the companies/ organizations
that you want to record / track. E.G. quotes, major orders projects etc.

Again, I don't know your specifics on #1 and #2 and so this is just a guess
at somehting that will provide a goo foundation for accomplishing what you
want to do.

Fred

 

[Jeff Boyce]

Thanks for the kind words, Fred (way too generous, but kind <g> -- I suspect
you're doing quite well...)

To your general case response, I'll add the reminders/cautions I offer folks
who want to "use Access to build a database". These, too, are generic, and
are pointed at ending up with an application that folks actually use...

1. Learn about "normalization" and "relational database design".
Effective use of Access depends on this.
2. Learn how Access features/functions work, and the tricks & tips.
3. Learn how people and programs interact -- a good graphical user
interface design is critical.
4. Learn how to develop applications.

?If this seems like a lot of "learning", it is!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP