Smart Deleting

  • Thread starter Thread starter Dom
  • Start date Start date
D

Dom

I have three tables one with parts required the second
with part satisfies and the third with part status
The second table has two columns
The first being the part the second being satisfies
I.e.
TABLE 1 (REQUIRED)
PART
Part A
Part B
Part C
Part D
Part E
Part F

TABLE 2 (SATISFIES)
PART SATISFIES
Part B Part A
Part C Part A
Part G Part F
Part G Part C
Part G Part D
Part Z Part G

TABLE 3 (STATUS)
PART
Part Z
Part E

What I need to be able to do is take out any parts that
are done or satisfies any parts is table1
The problem I have is where Part Z is done which clears
Part G which in turn Clears part F,C & D
The end result in table 1 should be only be Part B
required

I have a query that does the opposite of what I want i.e
there is another table called pre-requisite
Which adds parts but this seems to be a lot easier as it
just adds part until it find no more pre-req’s

Can anyone give me some advice on this?

Thanks
Dom
 
Dom said:
I have three tables one with parts required the second
with part satisfies and the third with part status
The second table has two columns
The first being the part the second being satisfies
I.e.
TABLE 1 (REQUIRED)
PART
Part A
Part B
Part C
Part D
Part E
Part F

TABLE 2 (SATISFIES)
PART SATISFIES
Part B Part A
Part C Part A
Part G Part F
Part G Part C
Part G Part D
Part Z Part G

TABLE 3 (STATUS)
PART
Part Z
Part E

What I need to be able to do is take out any parts that
are done or satisfies any parts is table1
The problem I have is where Part Z is done which clears
Part G which in turn Clears part F,C & D
The end result in table 1 should be only be Part B
required

I have a query that does the opposite of what I want i.e
there is another table called pre-requisite
Which adds parts but this seems to be a lot easier as it
just adds part until it find no more pre-req’s
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have a tree that you need to traverse to its root (highest
branch?) and then reverse the traverse, deleting as you go. IOW,
Satisfies part tree (best viewed w/ fixed font):

Z
|
G
+--+--+
F C D
|
A

You'd have to traverse from Z to A and then reverse & delete back up
to Z. You'd have to use a Recordset & multiple queries (actually the
same one, but w/ different criteria) to find the next level of parts
that need to be deleted. This tree concept is usually used in Bill of
Material (BOM) problems. There are many discussions about this
subject in the various Access & SQL newsgroups. Try searching the
Google newsgroups search:

http://www.google.com/advanced_group_search?hl=en

Try searching newsgroups:
microsoft.public.access.queries;
comp.databases.ms-access

Use search strings like "BOM," "Bill of Materials," "tree traversals,"
"trees."

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFioMIechKqOuFEgEQIu8wCg7ajwUcitGdrot1aIEs1Jlomq3y0AoJnh
RP8fV0qUzE3Zw9J7n6agZO0S
=sMu3
-----END PGP SIGNATURE-----
 
Back
Top