building a parts database and need to make 2 different queries

[immaxx]

i just finished cataloguing a parts database at an airplane repair shop that
is split into different tables based on plane model or utility(ex. "cessna" ,
"engine", "electrical").

the tables are identical in terms of the types of fields they have. i want
to make 2 different queries. one that searches based on the items' Part #,
which is the primary key throughout my database. The other i want to search
through Part Description, so that it will give me back all the gaskets in
each table, for example.

Also, I'd like the Part# query to be able to search with just a partial part
#. So, if someone puts in the letters, "ms2" it comes back with all the
ms200121 parts and all the P440ms233 parts.

i've understood access enough so far enough to catalog all the parts i've
recorded but am having trouble understanding relationships and queries to the
point that i can build or use them.

help?

 

[Tim Ferguson]

split into different tables based on plane model or utility(ex.
"cessna" , "engine", "electrical").

the tables are identical in terms of the types of fields they have.

This sounds like a Major Design Problem -- you are storing information in
table names, when you should be storing it inside fields in records.

Your tables should reflect real-world things that you want to model (e.g.
AircraftType, PartType, PartsInStock) and how they relate to each other.
For example, if a JU45b/009 Carburettor Top is used for a Cessa N885,
then you'd have a record in the BelongsTo table that looks like

AircraftCode PartCode NumRequired
CSN885 JU45b/009 4


and so on. If you need to document sub-assemblies, then it's a trifle
more sophisticated, but there are heaps of examples about and plenty of
discussions here from time to time on modelling that.

Hope that helps


Tim F

 

[Larry Daugherty]

Your table design must be corrected before you try to build other
objects and functions on it: All of the instances of an entity in
play in your application must be recorded in a single table.

Creating different tables for different plane models is pretty much
the same as creating different tables for planes of different colors.
Concepts like make, model, utility, etc. are simply attributes of the
part and would be listed in a separate table.

Your parts catalogue may be complete but it probably isn't properly
organized. In your example of looking at Part Descriptions to find
out what gaskets are used in that thing with that Part Number, the
implication is that the referenced gaskets should be in your database
with their own part numbers. The test of that assumption would be:
are any of the items listed in Part Descriptions available for
acquisition by other means than ordering the Part Number in which
they're described? If they can be ordered separately then they
should/must be listed in the parts table. Even if they can't be
ordered separately, I would list them separately. If the same low
level part is used in more than one higher order part number (as is
probably the case) then you'll need to create a many-to-many
relationship in order to list every instance of the low level part's
use.

Your new table design will give you a single Parts table that lists
all of the parts. It might seem huge but at the first level of
conversion the new table will take about the same disk/memory space as
the several tables did. There is no limit on table size. The size of
an Access application is limited to 2 gig. You'll probably never
approach that size with your data.

Correct data design is crucial to the usability and maintainability of
your application. The query you want could be readily generated. Not
knowing your business rules, I can't say if you should have such a
query.

Post back as new issues arise. If it's your intent is to learn to
develop applications in Access then I recommend that you visit
www.mvps.org/access It's full of Access lore and has links to more.

Also, continue to lurk this newsgroup and
microsoft.public.access
microsoft.public.access.gettingstaarted

If your only motivation is to get an application that works well
without bothering to learn much about Access then I recommend that you
get a consultant to do the heavy lifting. Otherwise, you may spend an
awful lot of time hacking your way to a solution by continually going
back to the earlier design stages to correct things. That's an
expensive activity because changes to the data design have to be
rippled up through everything already built on it.

HTH