query design approach suggestions

  • Thread starter Thread starter dchendrickson
  • Start date Start date
D

dchendrickson

I am using Access 2002/XP.

I am creating an application that is tracking circuit
paths on a circuit card. This card can be configured in
several ways by setting jumpers (wire wraps) between pins
(terminals) on various connectors mounted to the card.
Signals come into the card via a connector on the front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit the card
through a connector on the rear edge.

I have a table that contains an entry for each connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin (terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID, PinLabel,
PinConnectsTo. PinConnectsTo is linked to the PinID in
the same table [tblPin].

The pins on the front edge connector are permanently
connected to the pins on a card surface connector and
this connection is defined by the conductor traces etched
in the card. So PinConnectsTo holds the PinID of the pin
in the other connector that the current record is
permanently etched to. A specific pin is always connected
to the same place.

The various ways this card can be configured are called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID, VersionID,
PinMatesTo. The PinID and VersionID make up the Primary
Key and the PinID is the same as in the tblPin table.
PinMatesTo, like PinConnectsTo, refers to the PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a query that
will 'walk' across the circuit path from front connector
to the rear connector and show each connection step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but there could
be more or less.

My first stab at the query grabs all the pins for the J1
(front) connector and then has a series of expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but the right
idea. Care has been take to isolate which version is
being queried and soforth.

This works, but is VERY slow and not suited to a path
that has differing numbers of intermediate connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc
 
Ouch, That is a long posting!
I lost it at
J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]
Click to expand...

Still, I was able to grasp enough (i think) to feel you might benefit from
Joe Celko's fantastic nested sets
http://www.mvps.org/access/queries/qry0023.htm

Configurations: In a sense, this has nothing to do with the design. i.e.
the circuit card does get redesigned when jumpers are set,
so why not look at confiigurations as a separate table
PartNumber, VersionNumber, JumperSettings.
You could use Jumper Settings as a bitMap - i.e. a series of Yes/No Fields
such that
0000 0000 = all off
0100 0100 = 7 and 3 on; rest off
This can be a very efficient and flexible way to store and manipulate your
data
You can use bitwise operators to determine if a jumper is true/False

HS


dchendrickson said:
I am using Access 2002/XP.

I am creating an application that is tracking circuit
paths on a circuit card. This card can be configured in
several ways by setting jumpers (wire wraps) between pins
(terminals) on various connectors mounted to the card.
Signals come into the card via a connector on the front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit the card
through a connector on the rear edge.

I have a table that contains an entry for each connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin (terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID, PinLabel,
PinConnectsTo. PinConnectsTo is linked to the PinID in
the same table [tblPin].

The pins on the front edge connector are permanently
connected to the pins on a card surface connector and
this connection is defined by the conductor traces etched
in the card. So PinConnectsTo holds the PinID of the pin
in the other connector that the current record is
permanently etched to. A specific pin is always connected
to the same place.

The various ways this card can be configured are called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID, VersionID,
PinMatesTo. The PinID and VersionID make up the Primary
Key and the PinID is the same as in the tblPin table.
PinMatesTo, like PinConnectsTo, refers to the PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a query that
will 'walk' across the circuit path from front connector
to the rear connector and show each connection step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but there could
be more or less.

My first stab at the query grabs all the pins for the J1
(front) connector and then has a series of expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but the right
idea. Care has been take to isolate which version is
being queried and soforth.

This works, but is VERY slow and not suited to a path
that has differing numbers of intermediate connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc
Click to expand...
 
You got that far? <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



HSalim said:
Ouch, That is a long posting!
I lost it at
J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]
Click to expand...

Still, I was able to grasp enough (i think) to feel you might benefit from
Joe Celko's fantastic nested sets
http://www.mvps.org/access/queries/qry0023.htm

Configurations: In a sense, this has nothing to do with the design. i.e.
the circuit card does get redesigned when jumpers are set,
so why not look at confiigurations as a separate table
PartNumber, VersionNumber, JumperSettings.
You could use Jumper Settings as a bitMap - i.e. a series of Yes/No Fields
such that
0000 0000 = all off
0100 0100 = 7 and 3 on; rest off
This can be a very efficient and flexible way to store and manipulate your
data
You can use bitwise operators to determine if a jumper is true/False

HS


dchendrickson said:
I am using Access 2002/XP.

I am creating an application that is tracking circuit
paths on a circuit card. This card can be configured in
several ways by setting jumpers (wire wraps) between pins
(terminals) on various connectors mounted to the card.
Signals come into the card via a connector on the front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit the card
through a connector on the rear edge.

I have a table that contains an entry for each connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin (terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID, PinLabel,
PinConnectsTo. PinConnectsTo is linked to the PinID in
the same table [tblPin].

The pins on the front edge connector are permanently
connected to the pins on a card surface connector and
this connection is defined by the conductor traces etched
in the card. So PinConnectsTo holds the PinID of the pin
in the other connector that the current record is
permanently etched to. A specific pin is always connected
to the same place.

The various ways this card can be configured are called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID, VersionID,
PinMatesTo. The PinID and VersionID make up the Primary
Key and the PinID is the same as in the tblPin table.
PinMatesTo, like PinConnectsTo, refers to the PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a query that
will 'walk' across the circuit path from front connector
to the rear connector and show each connection step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but there could
be more or less.

My first stab at the query grabs all the pins for the J1
(front) connector and then has a series of expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but the right
idea. Care has been take to isolate which version is
being queried and soforth.

This works, but is VERY slow and not suited to a path
that has differing numbers of intermediate connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc
Click to expand...
Click to expand...
 
I take the 5th <g>

Lynn Trapp said:
You got that far? <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



HSalim said:
Ouch, That is a long posting!
I lost it at
J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]
Click to expand...

Still, I was able to grasp enough (i think) to feel you might benefit from
Joe Celko's fantastic nested sets
http://www.mvps.org/access/queries/qry0023.htm

Configurations: In a sense, this has nothing to do with the design. i.e.
the circuit card does get redesigned when jumpers are set,
so why not look at confiigurations as a separate table
PartNumber, VersionNumber, JumperSettings.
You could use Jumper Settings as a bitMap - i.e. a series of Yes/No Fields
such that
0000 0000 = all off
0100 0100 = 7 and 3 on; rest off
This can be a very efficient and flexible way to store and manipulate your
data
You can use bitwise operators to determine if a jumper is true/False

HS


dchendrickson said:
I am using Access 2002/XP.

I am creating an application that is tracking circuit
paths on a circuit card. This card can be configured in
several ways by setting jumpers (wire wraps) between pins
(terminals) on various connectors mounted to the card.
Signals come into the card via a connector on the front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit the card
through a connector on the rear edge.

I have a table that contains an entry for each connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin (terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID, PinLabel,
PinConnectsTo. PinConnectsTo is linked to the PinID in
the same table [tblPin].

The pins on the front edge connector are permanently
connected to the pins on a card surface connector and
this connection is defined by the conductor traces etched
in the card. So PinConnectsTo holds the PinID of the pin
in the other connector that the current record is
permanently etched to. A specific pin is always connected
to the same place.

The various ways this card can be configured are called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID, VersionID,
PinMatesTo. The PinID and VersionID make up the Primary
Key and the PinID is the same as in the tblPin table.
PinMatesTo, like PinConnectsTo, refers to the PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a query that
will 'walk' across the circuit path from front connector
to the rear connector and show each connection step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but there could
be more or less.

My first stab at the query grabs all the pins for the J1
(front) connector and then has a series of expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but the right
idea. Care has been take to isolate which version is
being queried and soforth.

This works, but is VERY slow and not suited to a path
that has differing numbers of intermediate connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc
Click to expand...
Click to expand...
Click to expand...
 
HS,

I'll take a look at Joe's nested sets. I have
incorporated your idea of bitwise logic where applicable.
But, these cards have both jumpers and wirewraps - so I
have a handful of multi-pin single-row connectors and any
one of those pins can be wirewrapped to any pin on the
other multi-pen single row connectors. That complexity
won't allow the bitwise approach. Thanks for the input
and I'll take a look at your suggestion.

-dc
-----Original Message-----
Ouch, That is a long posting!
I lost it at
J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]
Click to expand...

Still, I was able to grasp enough (i think) to feel you might benefit from
Joe Celko's fantastic nested sets
http://www.mvps.org/access/queries/qry0023.htm

Configurations: In a sense, this has nothing to do with the design. i.e.
the circuit card does get redesigned when jumpers are set,
so why not look at confiigurations as a separate table
PartNumber, VersionNumber, JumperSettings.
You could use Jumper Settings as a bitMap - i.e. a series of Yes/No Fields
such that
0000 0000 = all off
0100 0100 = 7 and 3 on; rest off
This can be a very efficient and flexible way to store and manipulate your
data
You can use bitwise operators to determine if a jumper is true/False

HS


I am using Access 2002/XP.

I am creating an application that is tracking circuit
paths on a circuit card. This card can be configured in
several ways by setting jumpers (wire wraps) between pins
(terminals) on various connectors mounted to the card.
Signals come into the card via a connector on the front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit the card
through a connector on the rear edge.

I have a table that contains an entry for each connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin (terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID, PinLabel,
PinConnectsTo. PinConnectsTo is linked to the PinID in
the same table [tblPin].

The pins on the front edge connector are permanently
connected to the pins on a card surface connector and
this connection is defined by the conductor traces etched
in the card. So PinConnectsTo holds the PinID of the pin
in the other connector that the current record is
permanently etched to. A specific pin is always connected
to the same place.

The various ways this card can be configured are called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID, VersionID,
PinMatesTo. The PinID and VersionID make up the Primary
Key and the PinID is the same as in the tblPin table.
PinMatesTo, like PinConnectsTo, refers to the PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a query that
will 'walk' across the circuit path from front connector
to the rear connector and show each connection step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but there could
be more or less.

My first stab at the query grabs all the pins for the J1
(front) connector and then has a series of expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup ("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but the right
idea. Care has been take to isolate which version is
being queried and soforth.

This works, but is VERY slow and not suited to a path
that has differing numbers of intermediate connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc
Click to expand...


.
Click to expand...
 
ayeayeaye... clowns everywhere!

Seems my 'expansive'/'robust' expanation has had the
opposite effect...

Thanks for trying.

-dc
-----Original Message-----
I take the 5th <g>

You got that far? <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Ouch, That is a long posting!
I lost it at
J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

Still, I was able to grasp enough (i think) to feel
Click to expand...
Click to expand...
you might benefit
from with the design.
i.e. series of Yes/No
Fields store and manipulate
your
data
You can use bitwise operators to determine if a jumper is true/False

HS


I am using Access 2002/XP.

I am creating an application that is tracking circuit
paths on a circuit card. This card can be configured in
several ways by setting jumpers (wire wraps) between pins
(terminals) on various connectors mounted to the card.
Signals come into the card via a connector on the front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit the card
through a connector on the rear edge.

I have a table that contains an entry for each connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin (terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID, PinLabel,
PinConnectsTo. PinConnectsTo is linked to the PinID in
the same table [tblPin].

The pins on the front edge connector are permanently
connected to the pins on a card surface connector and
this connection is defined by the conductor traces etched
in the card. So PinConnectsTo holds the PinID of the pin
in the other connector that the current record is
permanently etched to. A specific pin is always connected
to the same place.

The various ways this card can be configured are called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID, VersionID,
PinMatesTo. The PinID and VersionID make up the Primary
Key and the PinID is the same as in the tblPin table.
PinMatesTo, like PinConnectsTo, refers to the PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a query that
will 'walk' across the circuit path from front connector
to the rear connector and show each connection step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but there could
be more or less.

My first stab at the query grabs all the pins for the J1
(front) connector and then has a series of expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup ("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup ("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but the right
idea. Care has been take to isolate which version is
being queried and soforth.

This works, but is VERY slow and not suited to a path
that has differing numbers of intermediate connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc
Click to expand...
Click to expand...


.
Click to expand...
 
No, please don't take offence.

Much as you might be inclined to disbelieve me,
most volunteers would rather have a detailed and robust posting like yours
than have vague
rambling posts.

The problem with your post is that it was meant to be answered by people who
are smarter than
me, and recognizing that, I pointed you in the right direction.

You might want to repost this in Comp.Databases.ms-access - Joe Celko is
known to prowl there
or in the SQL server newsgroups

HS

dchendrickson said:
ayeayeaye... clowns everywhere!

Seems my 'expansive'/'robust' expanation has had the
opposite effect...

Thanks for trying.

-dc
-----Original Message-----
I take the 5th <g>

You got that far? <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Ouch, That is a long posting!
I lost it at
J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

Still, I was able to grasp enough (i think) to feel
Click to expand...
you might benefit
from
Joe Celko's fantastic nested sets
http://www.mvps.org/access/queries/qry0023.htm

Configurations: In a sense, this has nothing to do
Click to expand...
with the design.
i.e.
the circuit card does get redesigned when jumpers are set,
so why not look at confiigurations as a separate table
PartNumber, VersionNumber, JumperSettings.
You could use Jumper Settings as a bitMap - i.e. a
Click to expand...
series of Yes/No
Fields
such that
0000 0000 = all off
0100 0100 = 7 and 3 on; rest off
This can be a very efficient and flexible way to
Click to expand...
store and manipulate
your
data
You can use bitwise operators to determine if a jumper is true/False

HS


I am using Access 2002/XP.

I am creating an application that is tracking circuit
paths on a circuit card. This card can be configured in
several ways by setting jumpers (wire wraps) between pins
(terminals) on various connectors mounted to the card.
Signals come into the card via a connector on the front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit the card
through a connector on the rear edge.

I have a table that contains an entry for each connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin (terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID, PinLabel,
PinConnectsTo. PinConnectsTo is linked to the PinID in
the same table [tblPin].

The pins on the front edge connector are permanently
connected to the pins on a card surface connector and
this connection is defined by the conductor traces etched
in the card. So PinConnectsTo holds the PinID of the pin
in the other connector that the current record is
permanently etched to. A specific pin is always connected
to the same place.

The various ways this card can be configured are called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID, VersionID,
PinMatesTo. The PinID and VersionID make up the Primary
Key and the PinID is the same as in the tblPin table.
PinMatesTo, like PinConnectsTo, refers to the PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a query that
will 'walk' across the circuit path from front connector
to the rear connector and show each connection step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but there could
be more or less.

My first stab at the query grabs all the pins for the J1
(front) connector and then has a series of expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup ("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup ("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but the right
idea. Care has been take to isolate which version is
being queried and soforth.

This works, but is VERY slow and not suited to a path
that has differing numbers of intermediate connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc
Click to expand...


.
Click to expand...
Click to expand...
 
HS,

No offence taken.

I looked at Joe's nested set and I don't think it is
quite what I am looking for.

I'll take another stab at explaining with an example we
are all familiar with - Northwind.

The employee table has a Reports To field that is linked
back to the Employee ID. I want to create a query that
will follow the chain of command. So the result would be:

Anne Dodsworth reports to Steven Buchanan reports to
Andrew Fuller reports to ....

My application throws in an extra table, but lets ignore
that for a moment. What would be an efficient approach
to 'parse' this chain of command to see the reporting
structure of each employee from bottom to top?

Does this help clear the fog?

-dc
-----Original Message-----
No, please don't take offence.

Much as you might be inclined to disbelieve me,
most volunteers would rather have a detailed and robust posting like yours
than have vague
rambling posts.

The problem with your post is that it was meant to be answered by people who
are smarter than
me, and recognizing that, I pointed you in the right direction.

You might want to repost this in Comp.Databases.ms- access - Joe Celko is
known to prowl there
or in the SQL server newsgroups

HS

ayeayeaye... clowns everywhere!

Seems my 'expansive'/'robust' expanation has had the
opposite effect...

Thanks for trying.

-dc
Click to expand...
wrote in message
You got that far? <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Ouch, That is a long posting!
I lost it at
J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

Still, I was able to grasp enough (i think) to
Click to expand...
feel
you might benefit
from
Joe Celko's fantastic nested sets
http://www.mvps.org/access/queries/qry0023.htm

Configurations: In a sense, this has nothing to
Click to expand...
do
with the design.
i.e.
the circuit card does get redesigned when jumpers are set,
so why not look at confiigurations as a separate table
PartNumber, VersionNumber, JumperSettings.
You could use Jumper Settings as a bitMap - i.e. a series of Yes/No
Fields
such that
0000 0000 = all off
0100 0100 = 7 and 3 on; rest off
This can be a very efficient and flexible way to store and manipulate
your
data
You can use bitwise operators to determine if a jumper is true/False

HS


I am using Access 2002/XP.

I am creating an application that is tracking circuit
paths on a circuit card. This card can be configured in
several ways by setting jumpers (wire wraps) between pins
(terminals) on various connectors mounted to the card.
Signals come into the card via a connector on
Click to expand...
the
front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit
Click to expand...
the
card
through a connector on the rear edge.

I have a table that contains an entry for each connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin (terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID, PinLabel,
PinConnectsTo. PinConnectsTo is linked to the PinID in
the same table [tblPin].

The pins on the front edge connector are permanently
connected to the pins on a card surface
Click to expand...
connector
and
this connection is defined by the conductor
Click to expand...
traces
etched
in the card. So PinConnectsTo holds the PinID of the pin
in the other connector that the current record is
permanently etched to. A specific pin is always connected
to the same place.

The various ways this card can be configured are called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID, VersionID,
PinMatesTo. The PinID and VersionID make up the Primary
Key and the PinID is the same as in the tblPin table.
PinMatesTo, like PinConnectsTo, refers to the PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a
Click to expand...
query
that
will 'walk' across the circuit path from front connector
to the rear connector and show each connection step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but there could
be more or less.

My first stab at the query grabs all the pins
Click to expand...
for
the J1
(front) connector and then has a series of expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup ("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup ("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but
Click to expand...
the
right
idea. Care has been take to isolate which
Click to expand...
version
is
being queried and soforth.

This works, but is VERY slow and not suited to a path
that has differing numbers of intermediate connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc






.
Click to expand...
Click to expand...


.
Click to expand...
 
DC
You really should take a closer look at Celko's nested sets.
Here is another article that uses your analogy to describe nested sets

http://www.intelligententerprise.com/001020/celko.shtml

HS

dchendrickson said:
HS,

No offence taken.

I looked at Joe's nested set and I don't think it is
quite what I am looking for.

I'll take another stab at explaining with an example we
are all familiar with - Northwind.

The employee table has a Reports To field that is linked
back to the Employee ID. I want to create a query that
will follow the chain of command. So the result would be:

Anne Dodsworth reports to Steven Buchanan reports to
Andrew Fuller reports to ....

My application throws in an extra table, but lets ignore
that for a moment. What would be an efficient approach
to 'parse' this chain of command to see the reporting
structure of each employee from bottom to top?

Does this help clear the fog?

-dc
-----Original Message-----
No, please don't take offence.

Much as you might be inclined to disbelieve me,
most volunteers would rather have a detailed and robust posting like yours
than have vague
rambling posts.

The problem with your post is that it was meant to be answered by people who
are smarter than
me, and recognizing that, I pointed you in the right direction.

You might want to repost this in Comp.Databases.ms- access - Joe Celko is
known to prowl there
or in the SQL server newsgroups

HS

ayeayeaye... clowns everywhere!

Seems my 'expansive'/'robust' expanation has had the
opposite effect...

Thanks for trying.

-dc
-----Original Message-----
I take the 5th <g>

"Lynn Trapp"
Click to expand...
wrote in message
You got that far? <g>

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



message
Ouch, That is a long posting!
I lost it at
J1-1 connects to H7-1 Mates to H12-1 connects to
J2-A12
'format is [ConnectorLabel]-[PinLabel]

Still, I was able to grasp enough (i think) to feel
you might benefit
from
Joe Celko's fantastic nested sets
http://www.mvps.org/access/queries/qry0023.htm

Configurations: In a sense, this has nothing to do
with the design.
i.e.
the circuit card does get redesigned when jumpers
are set,
so why not look at confiigurations as a separate
table
PartNumber, VersionNumber, JumperSettings.
You could use Jumper Settings as a bitMap - i.e. a
series of Yes/No
Fields
such that
0000 0000 = all off
0100 0100 = 7 and 3 on; rest off
This can be a very efficient and flexible way to
store and manipulate
your
data
You can use bitwise operators to determine if a
jumper is true/False

HS


in message
I am using Access 2002/XP.

I am creating an application that is tracking
circuit
paths on a circuit card. This card can be
configured in
several ways by setting jumpers (wire wraps)
between pins
(terminals) on various connectors mounted to the
card.
Signals come into the card via a connector on the
front
edge, travel accross the card through various paths
determined by the jumpers, and eventually exit the
card
through a connector on the rear edge.

I have a table that contains an entry for each
connector
(ConnectorID, Name, Part#, etc) [tblConnector].

A second table contains entries for each pin
(terminal)
in each connector. This table is linked to the
tblConnector table via the ConnectorID. The fields
consist of PinID (autonumber), ConnectorID,
PinLabel,
PinConnectsTo. PinConnectsTo is linked to the
PinID in
the same table [tblPin].

The pins on the front edge connector are
permanently
connected to the pins on a card surface connector
and
this connection is defined by the conductor traces
etched
in the card. So PinConnectsTo holds the PinID of
the pin
in the other connector that the current record is
permanently etched to. A specific pin is always
connected
to the same place.

The various ways this card can be configured are
called
versions and there is a tblCardVersion table that
contains VersionID, Description.

And finally there is a table that contains the pin
connection information for the various versions
[tblPinVersion]. This table contains PinID,
VersionID,
PinMatesTo. The PinID and VersionID make up the
Primary
Key and the PinID is the same as in the tblPin
table.
PinMatesTo, like PinConnectsTo, refers to the
PinID that
the current record pin has been jumpered to.

Finally to my question: I need to develop a query
that
will 'walk' across the circuit path from front
connector
to the rear connector and show each connection
step. For
example:

J1-1 connects to H7-1 Mates to H12-1 connects to
J2-A12
'format is [ConnectorLabel]-[PinLabel]

This example shows one intermediate step between J1
connector (front) and J2 connector (rear) but
there could
be more or less.

My first stab at the query grabs all the pins for
the J1
(front) connector and then has a series of
expressions
using the dlookup method to pull the rest of the
connections:

PinConnectsTo1: dlookup
("PinonnectsTo","tblPin","PinID
= " & [PinID])

Then the next expression is the same for
PinMatesTo but
refers to the expression above:

PinMatesTo1: dlookup
("PinMatesTo","tlbPinVersion","PinID
= " & [PinConnectsTo1])

And so on...

Note: these are not the exact expressions, but the
right
idea. Care has been take to isolate which version
is
being queried and soforth.

This works, but is VERY slow and not suited to a
path
that has differing numbers of intermediate
connections
across the card.

Can anyone suggest a structure that would be more
efficient and also a way to parse the path until
the rear
connector is reached (do until J2)?

Thanks for your help and my appologies for the long
description.

-dc






.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top