Danny-
What you have is a BOMP (Bill of Materials Processing) problem. You need one
more table:
T_Assemblies:
AssemblyID
AssetID
Asset_Qty
The Primary Key will be the combination of the first two fields. AssemblyID
will contain the AssetID of the "assembly" - a pileline section, for example.
AssetID will contain the AssetID of one of the component parts, and Asset_Qty
will contain the number of parts indicated by AssetID that are contained in the
asset that's in AssemblyID. When this table is filled in properly, you'll have
a Bill of Material - the main asset and all the component assets and their
components, and so on.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Waco, Texas <yee haw>)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dannyb
Sent: Thursday, December 22, 2011 8:21 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Table relation to it's self
I have a problem with the DB I am building for our terminal, a couple of things
first
I am a novice
I am using Access 2007
I thought about using a BOM method but I'll see what you all think
My tables
I have several such as
T_Category:
CategoryID
Category_Name
- give a very general category such as Electrical, Pipeline, Tank, Valve ...
with this table I can divide the assets up.
T_Category_Sub:
Category_SubID
CategoryID
Sub_Name
IS_Assemby (yes/no)
-this is a secondary breakdown of the category - breaker box, breaker, air
valve, product valve, steam pipeline, product pipeline, additive tank...
some of these are an assembly of items such as a breaker box will have numerous
breakers, a pipeline will have numerous valves and that is why I put IS_Assembly
(Yes/No) in this table.
T_Assets
AssetID
Category_SubID
Corp_Number (txt type)
Manuf_Number (txt type)
-All assets have a number or name even our pipeline section (return line,
reciept line, green line, yellow line). A pipeline will break into different
sections so each section has a name. A pipeline section will have numerous
valves and the valves have pressure relief valves around them (the pressure
relief valves are normally 5 in number and each group is named)
I have no problem as far as naming everything but how do I get the item
together.
yellow line has 9 valves and the yellow line is part of tank T58-125 reciept
line (the receipt line is named RT58-125).
Will I have to have numerous tables to do this.
Thanks
Danny
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar