New to Access

[brink]

I work in the parts department of a new car dealership. We ar
frequently asked by customers for fluid capacities and types of thei
vehicles. Unfortunately this information is not in our parts catalog
so we must obtain the information from service manuals in the servic
department. I had the bright idea to gather this information and pu
it in a database, so I purchased Access. I have studied som
information regarding relational databases and I have a
understanding on relationships, however I just don't know how to pu
it to practice. For example, a 2005 Explorer has more than on
engine choice, transmission choice, front and rear axle choice etc.
Also these different engine, transmissions, etc. may fit differen
years and models. Would this constitute a many to many relationship
What key field would I use to gather all the related informatio
together? I like to create a form that would gather the year, mak
and model from a user and display all the fluid capacity and type
for that vehicle. Any help on getting me started in the righ
direction would be greatly appreciated

[Strasser]

brink wrote:

I work in the parts department of a new car dealership. We are
frequently asked by customers for fluid capacities and types of their
vehicles. Unfortunately this information is not in our parts catalog, so we must obtain the information from service manuals in the service
department. I had the bright idea to gather this information and put
it in a database, so I purchased Access. I have studied some
information regarding relational databases and I have an
understanding on relationships, however I just don't know how to put
it to practice. For example, a 2005 Explorer has more than one
engine choice, transmission choice, front and rear axle choice etc.
Also these different engine, transmissions, etc. may fit different
years and models. Would this constitute a many to many relationship?
What key field would I use to gather all the related information
together? I like to create a form that would gather the year, make
and model from a user and display all the fluid capacity and types
for that vehicle. Any help on getting me started in the right
direction would be greatly appreciated.

Strasser replies:
This will be a good "thread" for anyone NEW to relational database
design who wants to use Access to create the database FROM SCRATCH.
I am a teacher so the discussion will differ from the normal short
answer format.
Brink, you have stated as the database objective that you want to be
able to input automobile:
1) year
2) make
3) model of the car
and display all the
4) fluid capacities for different types of fluids

QUESTION:
By fluid "types" do you mean:
oil
brake
automatic transmission (or manual transmission)
coolant capacity
These kinds of "types"?
Please answer this question.

First step in design of a new database
It is important to know what you want OUT of the database.
Is the above accurate?
What format do you want the information in?
(sounds like a screen display of a form showing fluid capacities
of the different fluid types, so you can answer the customer when they
ask for types and fluid capacities for their cars)

Please answer my question about "types".
If anyone else is going to follow this general database design
discussion, please "reply" so we know others are following along the
discussion.

Brink, I'll look for your answer. Strasser

[Alan Webb]

Brink,
Parts made up of parts, is that pretty much what you need to remember? What
parts made up which other part? If a customer bought a 2004 Ford Explorer
with the 4.6 Litre modular V-8 it gets 7 quarts of 20W-50 because that
engine with the trailer package came with a larger oil cooler, for example?
Pretty much the basic design is a bill of materials. You only need a simple
bill of materials so probably it's one finished vehicle has some parts that
went into making it--or a one-to-many relationship. Fluids would be treated
as parts in this example. So, you only need one table listing all the
relevent parts. In one of the columns of that table will be a PART_NO that
uniquely identifies that part. In a second column will be BELONGS_TO which
contains the part number of the part that this part belongs to. The rest is
whatever attributes of a part you need for your purpose. Unit of measure
and units needed are probably two additional columns.
So, for example:
PART_MASTER_TBL
PART_NO Text(15) Primary Key
NAME Text(50)
DESC Text(255)
UOM Text(15) Foreign Key UOM_LKP On UOM_LKP.UOM_COD E Text(15)
UNITS_NEEDED Single
COMPONENT_OF Text(15) Foreign Key PART_MASTER_TBL On
PART_MASTER_TBL .PART_NO

The description above should give you an idea. It's not completely legal
ANSI SQL so it won't make a table without some work. But the ideas are
there.

[pietlinden]

That said... read this...

http://www.mvps.org/access/modules/mdl0027.htm

[Alan Webb]

Pietlinden,
Cool.

<pi********@hot mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

That said... read this...

http://www.mvps.org/access/modules/mdl0027.htm

[ng]

1) You bought the right tool to do the job.
2) It doesn't sound like you have the technical knowledge of relational
databases to do this project and make it run like the fererri It should be.

-BrianDP

"brink" <br***@mygalaxy express-dot-com.no-spam.invalid> wrote in message
news:2a******** ************@gi ganews.com...

I work in the parts department of a new car dealership. We are
frequently asked by customers for fluid capacities and types of their
vehicles. Unfortunately this information is not in our parts catalog,
so we must obtain the information from service manuals in the service
department. I had the bright idea to gather this information and put
it in a database, so I purchased Access. I have studied some
information regarding relational databases and I have an
understanding on relationships, however I just don't know how to put
it to practice. For example, a 2005 Explorer has more than one
engine choice, transmission choice, front and rear axle choice etc.
Also these different engine, transmissions, etc. may fit different
years and models. Would this constitute a many to many relationship?
What key field would I use to gather all the related information
together? I like to create a form that would gather the year, make
and model from a user and display all the fluid capacity and types
for that vehicle. Any help on getting me started in the right
direction would be greatly appreciated.

[Alan Webb]

Brian,
Access isn't really for folks that studied databases and programming in
college. On the cheap, PostgreSQL & a decent open source Java IDE like
Eclipse are probably better. If there is money to spend, either something
like Oracle or Ingres or SQL Server and a full version of Microsoft's VB.Net
or C# are also good. Access is exactly the right tool for a user like the
original poster. He can put something together in a relatively short period
of time that will help if he has a decent understanding of Excel. Things
really start to get fun when the project takes on a life of its own and he
finds himself having to invest more of his time in learning programming &
databases so he can build something that lives up to his original dream.

--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS
"ng" <[e-mail]> wrote in message news:4y_1e.3438 6$oa6.8454@trnd dc07...

1) You bought the right tool to do the job.
2) It doesn't sound like you have the technical knowledge of relational
databases to do this project and make it run like the fererri It should
be.

-BrianDP

"brink" <br***@mygalaxy express-dot-com.no-spam.invalid> wrote in message
news:2a******** ************@gi ganews.com...

I work in the parts department of a new car dealership. We are
frequently asked by customers for fluid capacities and types of their
vehicles. Unfortunately this information is not in our parts catalog,
so we must obtain the information from service manuals in the service
department. I had the bright idea to gather this information and put
it in a database, so I purchased Access. I have studied some
information regarding relational databases and I have an
understanding on relationships, however I just don't know how to put
it to practice. For example, a 2005 Explorer has more than one
engine choice, transmission choice, front and rear axle choice etc.
Also these different engine, transmissions, etc. may fit different
years and models. Would this constitute a many to many relationship?
What key field would I use to gather all the related information
together? I like to create a form that would gather the year, make
and model from a user and display all the fluid capacity and types
for that vehicle. Any help on getting me started in the right
direction would be greatly appreciated.

[brink]

Strasser, the types of fluid for instance would be oil weights lik
5W20 or 5W30 for engines. Mercon 3 or Mercon 5 fluid types fo
transmissions. Green, orange, gold colored antifreezes for coolan
types etc..

[Larry Linson]

"Alan Webb" wrote

Access isn't really for folks that
studied databases and programming in
college.
Access is (really) for the professional as well as the end user. (I suspect
I qualify, as I've been earning my living as a professional programmer and
developer since 1958.)
On the cheap, PostgreSQL & a decent
open source Java IDE like Eclipse are
probably better.
Better for _what_? Not better for the clients who hired me to create "normal
business database applications" quickly and inexpensively. Better, perhaps,
for some situations -- in your studies "of databases and programming in
college", did no one ever mention using "appropriat e technologies"? It's
quite possible that some "in the lofty halls of academe" would not because
of their lack of practical experience.
If there is money to spend, either something
like Oracle or Ingres or SQL Server and a
full version of Microsoft's VB.Net or C# are
also good.
Anyone who'd use .NET instead of Access to create the UI for individual
applications or small-to-modest-sized multiuser or client-server
applications, at this point in the .NET product lifecycle, clearly needs
"profession al help" for his/her masochistic tendencies.

Larry Linson
Microsoft Access MVP
Access is exactly the right tool for a user like the original poster. He can put something together in a relatively short period of time that will help if he has a decent understanding of Excel.
Things really start to get fun when the project takes on a life of its own and he finds himself having to invest more of his time in learning programming &
databases so he can build something that lives up to his original dream.

--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS
"ng" <[e-mail]> wrote in message news:4y_1e.3438 6$oa6.8454@trnd dc07...

1) You bought the right tool to do the job.
2) It doesn't sound like you have the technical knowledge of relational
databases to do this project and make it run like the fererri It should
be.

-BrianDP

"brink" <br***@mygalaxy express-dot-com.no-spam.invalid> wrote in message
news:2a******** ************@gi ganews.com...

I work in the parts department of a new car dealership. We are
frequently asked by customers for fluid capacities and types of their
vehicles. Unfortunately this information is not in our parts catalog,
so we must obtain the information from service manuals in the service
department. I had the bright idea to gather this information and put
it in a database, so I purchased Access. I have studied some
information regarding relational databases and I have an
understanding on relationships, however I just don't know how to put
it to practice. For example, a 2005 Explorer has more than one
engine choice, transmission choice, front and rear axle choice etc.
Also these different engine, transmissions, etc. may fit different
years and models. Would this constitute a many to many relationship?
What key field would I use to gather all the related information
together? I like to create a form that would gather the year, make
and model from a user and display all the fluid capacity and types
for that vehicle. Any help on getting me started in the right
direction would be greatly appreciated.

[rkc]

Alan Webb wrote:

Brink,
Parts made up of parts, is that pretty much what you need to remember? What
parts made up which other part? If a customer bought a 2004 Ford Explorer
with the 4.6 Litre modular V-8 it gets 7 quarts of 20W-50 because that
engine with the trailer package came with a larger oil cooler, for example?
Pretty much the basic design is a bill of materials. You only need a simple
bill of materials so probably it's one finished vehicle has some parts that
went into making it--or a one-to-many relationship. Fluids would be treated
as parts in this example. So, you only need one table listing all the
relevent parts. In one of the columns of that table will be a PART_NO that
uniquely identifies that part. In a second column will be BELONGS_TO which
contains the part number of the part that this part belongs to. The rest is
whatever attributes of a part you need for your purpose. Unit of measure
and units needed are probably two additional columns.
So, for example:
PART_MASTER_TBL
PART_NO Text(15) Primary Key
NAME Text(50)
DESC Text(255)
UOM Text(15) Foreign Key UOM_LKP On UOM_LKP.UOM_COD E Text(15)
UNITS_NEEDED Single
COMPONENT_OF Text(15) Foreign Key PART_MASTER_TBL On
PART_MASTER_TBL .PART_NO

How do you relate the 2004 Ford Explorer to the 4.6 Litre modular V-8
with a larger oil cooler using that table structure?

How about the 2003 Ford Explorer?