(Msg. 1) Posted: Wed Oct 08, 2008 4:43 pm
Post subject: Grouping components and updating locations Archived from groups: microsoft>public>access>tablesdbdesign (more info?)
I am working on a aircraft Parts management database. I am trying to find a
way to relate or link Major assembly components, Sub assembly components, and
components. For example all parts, assemblies, and sub assemblies are listed
in the tbl_partnumbers they have corresponding records in tbl_serialnumbers
that also refer to a record in tbl_locations I want to be able to view a
major-assembly record that will also list all sub-assembies and the parts
those sub-assemblies contain. I also want to be able to move a sub-assembly
from one Major-Assembly to another and have all of its parts Follow it. Or
move a major-assembly between aircraft and have all sub-assemblies and parts
follow. maybe this can be done by somehow associating the locations, but i am
stumped for now..
(Msg. 2) Posted: Thu Oct 09, 2008 3:00 am
Post subject: Re: Grouping components and updating locations [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
One approach is to place everything in the one table, e.g.:
PartID AutoNumber primary key
PartName Text
ParentPartID Number
You put the major assemblies in the table, leaving ParentPartID blank as
they are not part of anything. Next you enter he sub-assemblies, and in the
ParentPartID you put the PartID of the assembly it belongs to. Next you
enter each component, and in ParentPartID you enter the sub-assembly it
belongs to. And so on.
This structure (a self-join), copes with any level of embeddedness. It's
really easy to design, really flexible. For example, you can create a
related table to record sales, and its PartID field can relate back to any
kind of level (that is, you can sell a component, a sub-assembly, a major
assembly, whatever.)
The disadvantage of this approach comes when you try to do things like
building a complete family tree of the parts you offer. You can create a
monster query that does this to about 4 generations, e.g.:
http://allenbrowne.com/ser-06.html
But the problem of infinite recursion arises, e.g. where are part is wrongly
entered as its own grandparent.
BTW, I hear a rumour that the latest SQL Sever (Express?) supports resolving
this kind of structure better than JET does, but I haven't personally tested
it.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Barry A&P" <Barry A&P.RemoveThis@discussions.microsoft.com> wrote in message
news:64EB2884-BE63-41AF-BC73-98E6601808D1@microsoft.com...
>I am working on a aircraft Parts management database. I am trying to find a
> way to relate or link Major assembly components, Sub assembly components,
> and
> components. For example all parts, assemblies, and sub assemblies are
> listed
> in the tbl_partnumbers they have corresponding records in
> tbl_serialnumbers
> that also refer to a record in tbl_locations I want to be able to view a
> major-assembly record that will also list all sub-assembies and the parts
> those sub-assemblies contain. I also want to be able to move a
> sub-assembly
> from one Major-Assembly to another and have all of its parts Follow it. Or
> move a major-assembly between aircraft and have all sub-assemblies and
> parts
> follow. maybe this can be done by somehow associating the locations, but i
> am
> stumped for now..
(Msg. 3) Posted: Thu Oct 09, 2008 1:34 pm
Post subject: Re: Grouping components and updating locations [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Allen
Thank you so much for your help, I was so excited by the simplicity of your
answer, however i could not get the query to work or the self-join. my
database is as follows (simplified)
Tbl_PartNumbers
PartID Auto# PK
PartName
Life
Cost
Tbl_SerialNumbers
SerNoID Auto# PK
PartID (Lookup to Tbl_PartNumbers)
SerNoparentID
Location
Here is where My Query ended up
SELECT PartNumbers.[Part Number], PartNumbers.Description,
SerialNumbers.[Serial Number], Sub.PartNumberID, Sub.[Serial Number], [Sub
Sub].PartNumberID, [Sub Sub].[Serial Number], [Sub Sub Sub].PartNumberID,
[Sub Sub Sub].[Serial Number]
FROM PartNumbers INNER JOIN (SerialNumbers AS [Sub Sub Sub] INNER JOIN
(SerialNumbers AS [Sub Sub] INNER JOIN (SerialNumbers AS Sub INNER JOIN
SerialNumbers ON Sub.SNParentID = SerialNumbers.SerialNumberID) ON [Sub
Sub].SNParentID = Sub.SerialNumberID) ON [Sub Sub Sub].SNParentID = [Sub
Sub].SerialNumberID) ON PartNumbers.PartNumberID = SerialNumbers.PartNumberID;
It has no values when i opened it
am i way off base?
The SireSireDameSire Query in your horses example was too hard for me to
reverse engineer. i even tried to setup tables with horses names Ect. i was
able to get the Self-join (two tables with two one-to-manys) but the query
asked for values when i ran it.. and didnt work.
Thanks Again
Barry
"Allen Browne" wrote:
> One approach is to place everything in the one table, e.g.:
> PartID AutoNumber primary key
> PartName Text
> ParentPartID Number
>
> You put the major assemblies in the table, leaving ParentPartID blank as
> they are not part of anything. Next you enter he sub-assemblies, and in the
> ParentPartID you put the PartID of the assembly it belongs to. Next you
> enter each component, and in ParentPartID you enter the sub-assembly it
> belongs to. And so on.
>
> This structure (a self-join), copes with any level of embeddedness. It's
> really easy to design, really flexible. For example, you can create a
> related table to record sales, and its PartID field can relate back to any
> kind of level (that is, you can sell a component, a sub-assembly, a major
> assembly, whatever.)
>
> The disadvantage of this approach comes when you try to do things like
> building a complete family tree of the parts you offer. You can create a
> monster query that does this to about 4 generations, e.g.:
> http://allenbrowne.com/ser-06.html > But the problem of infinite recursion arises, e.g. where are part is wrongly
> entered as its own grandparent.
>
> Here's a sample database that illustrates the technique:
> http://www.mvps.org/access/modules/mdl0027.htm >
> Here's some more involved SQL approaches from Joe Celko:
> http://www.intelligententerprise.com/001020/celko.shtml > http://www.dbmsmag.com/9603d06.html > http://www.dbmsmag.com/9604d06.html > http://www.dbmsmag.com/9605d06.html > http://www.dbmsmag.com/9606d06.html >
> BTW, I hear a rumour that the latest SQL Sever (Express?) supports resolving
> this kind of structure better than JET does, but I haven't personally tested
> it.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org.
>
> "Barry A&P" <Barry A&P.DeleteThis@discussions.microsoft.com> wrote in message
> news:64EB2884-BE63-41AF-BC73-98E6601808D1@microsoft.com...
> >I am working on a aircraft Parts management database. I am trying to find a
> > way to relate or link Major assembly components, Sub assembly components,
> > and
> > components. For example all parts, assemblies, and sub assemblies are
> > listed
> > in the tbl_partnumbers they have corresponding records in
> > tbl_serialnumbers
> > that also refer to a record in tbl_locations I want to be able to view a
> > major-assembly record that will also list all sub-assembies and the parts
> > those sub-assemblies contain. I also want to be able to move a
> > sub-assembly
> > from one Major-Assembly to another and have all of its parts Follow it. Or
> > move a major-assembly between aircraft and have all sub-assemblies and
> > parts
> > follow. maybe this can be done by somehow associating the locations, but i
> > am
> > stumped for now..
>
>
(Msg. 4) Posted: Fri Oct 10, 2008 3:00 am
Post subject: Re: Grouping components and updating locations [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Hi Barry
You need to use outer joins to get the records to show up.
Double-click the line joining the 2 tables in the upper pane of the query
design window. Access pops up a dialog offering 3 options. You have to
choose either the 2nd or 3rd one (depending which way round your tables
are), not the first one.
The 2nd part of this article has a brief explanation of outer joins:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Barry A&P" <BarryAP.RemoveThis@discussions.microsoft.com> wrote in message
news:EA493983-5EBA-4A84-81EF-529416EF0DDD@microsoft.com...
> Allen
> Thank you so much for your help, I was so excited by the simplicity of
> your
> answer, however i could not get the query to work or the self-join. my
> database is as follows (simplified)
>
> Tbl_PartNumbers
> PartID Auto# PK
> PartName
> Life
> Cost
>
> Tbl_SerialNumbers
> SerNoID Auto# PK
> PartID (Lookup to Tbl_PartNumbers)
> SerNoparentID
> Location
>
> Here is where My Query ended up
> SELECT PartNumbers.[Part Number], PartNumbers.Description,
> SerialNumbers.[Serial Number], Sub.PartNumberID, Sub.[Serial Number], [Sub
> Sub].PartNumberID, [Sub Sub].[Serial Number], [Sub Sub Sub].PartNumberID,
> [Sub Sub Sub].[Serial Number]
> FROM PartNumbers INNER JOIN (SerialNumbers AS [Sub Sub Sub] INNER JOIN
> (SerialNumbers AS [Sub Sub] INNER JOIN (SerialNumbers AS Sub INNER JOIN
> SerialNumbers ON Sub.SNParentID = SerialNumbers.SerialNumberID) ON [Sub
> Sub].SNParentID = Sub.SerialNumberID) ON [Sub Sub Sub].SNParentID = [Sub
> Sub].SerialNumberID) ON PartNumbers.PartNumberID =
> SerialNumbers.PartNumberID;
>
> It has no values when i opened it
> am i way off base?
>
> The SireSireDameSire Query in your horses example was too hard for me to
> reverse engineer. i even tried to setup tables with horses names Ect. i
> was
> able to get the Self-join (two tables with two one-to-manys) but the
> query
> asked for values when i ran it.. and didnt work.
>
> Thanks Again
> Barry
>
> "Allen Browne" wrote:
>
>> One approach is to place everything in the one table, e.g.:
>> PartID AutoNumber primary key
>> PartName Text
>> ParentPartID Number
>>
>> You put the major assemblies in the table, leaving ParentPartID blank as
>> they are not part of anything. Next you enter he sub-assemblies, and in
>> the
>> ParentPartID you put the PartID of the assembly it belongs to. Next you
>> enter each component, and in ParentPartID you enter the sub-assembly it
>> belongs to. And so on.
>>
>> This structure (a self-join), copes with any level of embeddedness. It's
>> really easy to design, really flexible. For example, you can create a
>> related table to record sales, and its PartID field can relate back to
>> any
>> kind of level (that is, you can sell a component, a sub-assembly, a major
>> assembly, whatever.)
>>
>> The disadvantage of this approach comes when you try to do things like
>> building a complete family tree of the parts you offer. You can create a
>> monster query that does this to about 4 generations, e.g.:
>> http://allenbrowne.com/ser-06.html >> But the problem of infinite recursion arises, e.g. where are part is
>> wrongly
>> entered as its own grandparent.
>>
>> Here's a sample database that illustrates the technique:
>> http://www.mvps.org/access/modules/mdl0027.htm >>
>> Here's some more involved SQL approaches from Joe Celko:
>> http://www.intelligententerprise.com/001020/celko.shtml >> http://www.dbmsmag.com/9603d06.html >> http://www.dbmsmag.com/9604d06.html >> http://www.dbmsmag.com/9605d06.html >> http://www.dbmsmag.com/9606d06.html >>
>> BTW, I hear a rumour that the latest SQL Sever (Express?) supports
>> resolving
>> this kind of structure better than JET does, but I haven't personally
>> tested
>> it.
>>
>> "Barry A&P" <Barry A&P.RemoveThis@discussions.microsoft.com> wrote in message
>> news:64EB2884-BE63-41AF-BC73-98E6601808D1@microsoft.com...
>> >I am working on a aircraft Parts management database. I am trying to
>> >find a
>> > way to relate or link Major assembly components, Sub assembly
>> > components,
>> > and
>> > components. For example all parts, assemblies, and sub assemblies are
>> > listed
>> > in the tbl_partnumbers they have corresponding records in
>> > tbl_serialnumbers
>> > that also refer to a record in tbl_locations I want to be able to view
>> > a
>> > major-assembly record that will also list all sub-assembies and the
>> > parts
>> > those sub-assemblies contain. I also want to be able to move a
>> > sub-assembly
>> > from one Major-Assembly to another and have all of its parts Follow it.
>> > Or
>> > move a major-assembly between aircraft and have all sub-assemblies and
>> > parts
>> > follow. maybe this can be done by somehow associating the locations,
>> > but i
>> > am
>> > stumped for now.
(Msg. 5) Posted: Fri Oct 10, 2008 8:35 am
Post subject: Re: Grouping components and updating locations [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Allen
Now i just have to figure out how to use the tree data in the query,
Thank you for getting me on the right track..
"Allen Browne" wrote:
> Hi Barry
>
> You need to use outer joins to get the records to show up.
>
> Double-click the line joining the 2 tables in the upper pane of the query
> design window. Access pops up a dialog offering 3 options. You have to
> choose either the 2nd or 3rd one (depending which way round your tables
> are), not the first one.
>
> The 2nd part of this article has a brief explanation of outer joins:
> The Query Lost My Records!
> at:
> http://allenbrowne.com/casu-02.html > --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html > Reply to group, rather than allenbrowne at mvps dot org.
>
> "Barry A&P" <BarryAP DeleteThis @discussions.microsoft.com> wrote in message
> news:EA493983-5EBA-4A84-81EF-529416EF0DDD@microsoft.com...
> > Allen
> > Thank you so much for your help, I was so excited by the simplicity of
> > your
> > answer, however i could not get the query to work or the self-join. my
> > database is as follows (simplified)
> >
> > Tbl_PartNumbers
> > PartID Auto# PK
> > PartName
> > Life
> > Cost
> >
> > Tbl_SerialNumbers
> > SerNoID Auto# PK
> > PartID (Lookup to Tbl_PartNumbers)
> > SerNoparentID
> > Location
> >
> > Here is where My Query ended up
> > SELECT PartNumbers.[Part Number], PartNumbers.Description,
> > SerialNumbers.[Serial Number], Sub.PartNumberID, Sub.[Serial Number], [Sub
> > Sub].PartNumberID, [Sub Sub].[Serial Number], [Sub Sub Sub].PartNumberID,
> > [Sub Sub Sub].[Serial Number]
> > FROM PartNumbers INNER JOIN (SerialNumbers AS [Sub Sub Sub] INNER JOIN
> > (SerialNumbers AS [Sub Sub] INNER JOIN (SerialNumbers AS Sub INNER JOIN
> > SerialNumbers ON Sub.SNParentID = SerialNumbers.SerialNumberID) ON [Sub
> > Sub].SNParentID = Sub.SerialNumberID) ON [Sub Sub Sub].SNParentID = [Sub
> > Sub].SerialNumberID) ON PartNumbers.PartNumberID =
> > SerialNumbers.PartNumberID;
> >
> > It has no values when i opened it
> > am i way off base?
> >
> > The SireSireDameSire Query in your horses example was too hard for me to
> > reverse engineer. i even tried to setup tables with horses names Ect. i
> > was
> > able to get the Self-join (two tables with two one-to-manys) but the
> > query
> > asked for values when i ran it.. and didnt work.
> >
> > Thanks Again
> > Barry
> >
> > "Allen Browne" wrote:
> >
> >> One approach is to place everything in the one table, e.g.:
> >> PartID AutoNumber primary key
> >> PartName Text
> >> ParentPartID Number
> >>
> >> You put the major assemblies in the table, leaving ParentPartID blank as
> >> they are not part of anything. Next you enter he sub-assemblies, and in
> >> the
> >> ParentPartID you put the PartID of the assembly it belongs to. Next you
> >> enter each component, and in ParentPartID you enter the sub-assembly it
> >> belongs to. And so on.
> >>
> >> This structure (a self-join), copes with any level of embeddedness. It's
> >> really easy to design, really flexible. For example, you can create a
> >> related table to record sales, and its PartID field can relate back to
> >> any
> >> kind of level (that is, you can sell a component, a sub-assembly, a major
> >> assembly, whatever.)
> >>
> >> The disadvantage of this approach comes when you try to do things like
> >> building a complete family tree of the parts you offer. You can create a
> >> monster query that does this to about 4 generations, e.g.:
> >> http://allenbrowne.com/ser-06.html > >> But the problem of infinite recursion arises, e.g. where are part is
> >> wrongly
> >> entered as its own grandparent.
> >>
> >> Here's a sample database that illustrates the technique:
> >> http://www.mvps.org/access/modules/mdl0027.htm > >>
> >> Here's some more involved SQL approaches from Joe Celko:
> >> http://www.intelligententerprise.com/001020/celko.shtml > >> http://www.dbmsmag.com/9603d06.html > >> http://www.dbmsmag.com/9604d06.html > >> http://www.dbmsmag.com/9605d06.html > >> http://www.dbmsmag.com/9606d06.html > >>
> >> BTW, I hear a rumour that the latest SQL Sever (Express?) supports
> >> resolving
> >> this kind of structure better than JET does, but I haven't personally
> >> tested
> >> it.
> >>
> >> "Barry A&P" <Barry A&P DeleteThis @discussions.microsoft.com> wrote in message
> >> news:64EB2884-BE63-41AF-BC73-98E6601808D1@microsoft.com...
> >> >I am working on a aircraft Parts management database. I am trying to
> >> >find a
> >> > way to relate or link Major assembly components, Sub assembly
> >> > components,
> >> > and
> >> > components. For example all parts, assemblies, and sub assemblies are
> >> > listed
> >> > in the tbl_partnumbers they have corresponding records in
> >> > tbl_serialnumbers
> >> > that also refer to a record in tbl_locations I want to be able to view
> >> > a
> >> > major-assembly record that will also list all sub-assembies and the
> >> > parts
> >> > those sub-assemblies contain. I also want to be able to move a
> >> > sub-assembly
> >> > from one Major-Assembly to another and have all of its parts Follow it.
> >> > Or
> >> > move a major-assembly between aircraft and have all sub-assemblies and
> >> > parts
> >> > follow. maybe this can be done by somehow associating the locations,
> >> > but i
> >> > am
> >> > stumped for now.
>
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other