Danny-
Looks good, but you also need a relationship from T_Asset.AssetID to
T_Assembly.Asset_AssemblyID. Asset_AssemblyID should NOT be AutoNumber.
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 10:39 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Table relation to it's self
John,
Thanks for the reply, I have uploaded a screenshot of how my relationship looks;
it is in 2_NeedAssit named ScreenShot
By the way I have your book but did not think to look and see if it possibly has
something on this.
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar