Is this possible? Complicated "where used" query.

[SonicClang]

I'm designing a database for users to check material in and out of a vault. I'm to the point where I'm creating reports for them to track the materials. I have one report in mind that is just so complicated, it's tying my brain in knots.

There are three fields involved here; CSNumber, Parent1, and Parent2. When a new material is checked into the vault, it could be raw material, in which case it would have no parent material. If material is checked out, taken to a machine and processed, when it's checked back in it gets a new CSNumber, and the previous CSNumber is now Parent1.

CSNumber = bag/container number. One lot number could be split into multiple containers.
Parent1 = After material is processed it gets checked in as a new lot and new CS number. The CS number that this was made of is the parent.
Parent2 = If parent 1 wasn't enough, part of another parent can be used.

Here is an example of the values in table tblCSNumbers
 CSNumber | Parent1 | Parent 2 |
--------------------------------
 CS00029  |         |          |
 CS00030  |         |          |
 CS00031  | CS00029 |          |
 CS00032  | CS00029 |          |
 CS00033  | CS00031 |          |
 CS00035  | CS00031 |          |
 CS00036  | CS00029 | CS00030  |
 CS00037  | CS00029 | CS00030  |

This first query is easy. "Show where CS00029 is used"

qryWhereUsed (User selects CS00029 from a combobox on a form and runs the query)
| CSNumber | Parent1 | Parent 2 |
| CS00029  |         |          |
| CS00031  | CS00029 |          |
| CS00032  | CS00029 |          |
| CS00036  | CS00029 | CS00030  |
| CS00037  | CS00029 | CS00030  |

I'd like to write a query that connects the dots even further. If CS00029 is parent 1 of CS00031, then show me anywhere CS00031 is a parent for another material. So essentially, any value in either of the parent columns gets thrown into the CSNumber column... I think. Like I said, my brain is tied in knots right now.

This is what the results should show.
| CSNumber | Parent1 | Parent 2 |
| CS00029  |         |          |
| CS00031  | CS00029 |          |
| CS00033  | CS00031 |          |
| CS00035  | CS00031 |          |
| CS00032  | CS00029 |          |
| CS00036  | CS00029 | CS00030  |
| CS00037  | CS00029 | CS00030  |

Am I going about this the wrong way? Is this even possible? Am I making any sense?

 

[dkinley]

You might want to take a look at 'creating an audit trail' to see how those databases are designed to give you a good start. This sounds like what you are trying to do .... well, IF that is ...

-dK

 

[SonicClang]

You might want to take a look at 'creating an audit trail' to see how those databases are designed to give you a good start. This sounds like what you are trying to do .... well, IF that is ...

-dK

I'm sorry, I don't know what you mean. I must be missing something.

 

[dkinley]

It seemed that you wanted to create a trail of items ... from raw materials out to some processed artifact(s).

There are portions of databases called 'audit trails'. Where you can keep a historical trail of who did what to the database. If you do a search on 'audit trail' - that method of implementation may be what you are trying to do.

-dK

 

[MSAccessRookie]

I'm sorry, I don't know what you mean. I must be missing something.

I believe that he might mean that this is an issue that has been discussed before, and that if you use the site Search Enginer, you might find what you want. YTou can also use the Google Search Engine top find other discussions to consider.

 

[pbaldy]

PMFJI, but I don't think this is an audit trail problem, it's a recursive relationship problem. Google that and see if you find anything relevant.

 

[SonicClang]

PMFJI, but I don't think this is an audit trail problem, it's a recursive relationship problem. Google that and see if you find anything relevant.

After doing a little reading I think you're right on.

The reason I didn't understand what was meant by "audit trail" is because I don't believe that fits this situation. I'm fully aware of what an audit trail is. In fact I'm building in a couple audit trails into this database for when people need to make corrections to entry errors. I log who made the change, what the edit date was, what the record was before, and what it was changed to. I even show them a screen to review the change before they click OK. The changed fields are highlighted in red. I also have an audit trail of who accessed the administrator tools as well (admin tools like putting a new user in the users table or changing someone's password). But I fail to see how that helps me in this situation. I have a m:m problem I believe and now that I know the terminology I think I can figure it out.

Chances are I may have to change the structure of my table, or possibly use some VBA code to automatically fill in a field used to track the component back to original lot number.