Access Query 2010

I’m trying to put together a query and am having trouble conceptualizing how to go about it.  Let me explain first what I’m trying to do.
I have a order.  The ID is 12203641.  It has 5 parts in it.  If I partial ship 1 part, it creates another order with the ID 1292766.  Then I partial ship 2 more parts, and it creates another order with ID 1292767.
Here’s what the query looks like.  It works.

And the result is:

 query result
And now for the problem.  I want to partial ship from an order that is a partial shipment (a partial of a partial).  So if I partial ship 1 of the 2 parts from order 1292767, it creates another order with ID 1292768.  If I could get it to work, the result would look like this:

query result 2
The field AH_HAUPT_AUFTR points back to the order it was partialed from.  But if you partial a partial, there’s no record of the very first order this originated from which is 12203641.

Just for more info, here’s a visual of the software that this is coming from.  It shows how the order is being split off.  Also, keep in mind you can do an infinite number of partials, and partials of partials of partials, etc…..

originating software
Thank you for your consideration in helping me figure this out,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It doesn't make sense to partially ship a partial shipment.  Shipments are generally created at the time the order is ready to ship.  You ship what is available and back order the rest.  Sounds like you are splitting the order too early.   Wouldn't it be more logical to split the 5th order by adding a 6th under the same parent rather than creating a second level of shipments.  Plus, by your logic, why can't a partial shipment of a partial shipment also be split?
Genius123Author Commented:
Phil, this is our business practice.  It can't be changed.  Partials have to be made ahead of time to designate which factory produces it.
It's Pat, not Phil

Partials have to be made ahead of time to designate which factory produces it.
 Are you saying that you don't know when you do the original split which factory will be making the product?  and how many levels of partials do you need?  

What is the rational for creating a new level at all when you can split a shipment and have all the shipments at the same level?  If it is just because we've always done it that way, perhaps now is a good time to rethink the whole process.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Genius123Author Commented:
Here is part of the reason.  Let's say you partial out 5 parts so that a certain factory can produce it.  Then the customer says I need 3 right now!  So then you would partial out 3 from the partial.
Fine, is there a problem with changing 5 to 2 and adding another partial for 3?  is there shipment involved?  Did the 5 actually leave for the factory or is this always done from the main plant?  If they haven't shipped yet, it seems like the paper work and the queries would be easier if you just changed the partial and made another one.  If they've already shipped, you probably need that record so you would have to add the second level.
Genius123Author Commented:
That can't be done that unfortunately.  It's a business process and would take too long to explain.
Hamed NasrRetired IT ProfessionalCommented:
This issue looks like an employee-manger hierarchy.

order  partialedFrom

1112  1111
1113  1111
1114  1112
1115  1114

Upload an Access table, if more help is required.
Genius123Author Commented:
Yes, I guess it would be kind of like a employee-manger hierarchy.  I've uploaded the database, and also a current visual of how the jobs are partialed.  Thanks for your help.

I was pretty sure you would need more than just a second level.  Finally the truth comes out.

Your choices are to figure out the maximum number of levels you will ever need and hard-code a set of related tables - OR - do it the correct way and create a self - referencing table.  So all the splits, get built and added to the same table.  You would create this relationship in the relationship window by adding the shipment table twice and creating the relationship between RelatedToShipmentID (foreign key) and ShipmentID (primary key).  This is an infinitely recursive relationship and is similar to a Bill of Material or an employment hierarchy.

You can create queries that traverse the tree either from the bottom up or the top down.  Look for BOM examples of SQL.  Usually, the queries fix the number of levels so they can flatten the tree.  Or, if you are using a Tree View, the table should natively load the Tree View.
Hamed NasrRetired IT ProfessionalCommented:
See if you can make use of this comment.
If you know the levels, then a query will be fine.
If not, then more work, with some VBA code, is needed.
Genius123Author Commented:
hnasr, that is a clever report.  Thank you.  Now can you make the query return the son values in one column?  The result would be:


I've added some bogus records just to make sure it doesn't pull it into the query.  Thanks!

Hamed NasrRetired IT ProfessionalCommented:
Interesting request.
I'll give it a try.
Hamed NasrRetired IT ProfessionalCommented:
Try this.
Some VBA code was required.
Run form partials, and click Process Partials.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Genius123Author Commented:
Thanks so much for your help hnasr.  I'm getting this error though.

error message
Hamed NasrRetired IT ProfessionalCommented:
This indicates a required piece of code is not found.
One adds this extra software through references.
Check the references at your side.
While in VBA editor (to get there  press Alt + F11while in access).
Select Tool > References, and check with this list.
Genius123Author Commented:
It looks like maybe I am missing the MS Access 16.0?  How would I go about getting it?

You have a different version of Access.  You have 14 (second line) so that isn't the problem.  Look a little further.  The one that is missing is marked -- MISSING.  You seem to be using some Outlook component that needs this library.
Hamed NasrRetired IT ProfessionalCommented:
Look for available references with similar names, but with suitable version numbers.
Uncheck the missing references.
Genius123Author Commented:
Thank you!  Works great!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.