How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 6658
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I have a table which show the parent child relationship but

Customer Question

I have a table which show the parent child relationship but goes to grandchild extra and I need to write a query that gives me the top level parent part no for all of the children levels. How do I do this?
Submitted: 9 months ago.
Category: Microsoft Office
Customer: replied 9 months ago.
Here is an example file of the data I have and what I want.
Expert:  Jess M. replied 9 months ago.

Hi Holly, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Are you doing this with MS Excel or MS Access?

Please let me know by replying to me here so that I can help you further.

Best regards,
Jess

Customer: replied 9 months ago.
MS Access
Expert:  Jess M. replied 9 months ago.

Thank you for writing back with that information. Are there supporting tables involved with this that you need to call on your query?

Also, can you send me a sample or dummy Access database so that I can check your data structure and help you build the desired query? You can create a COPY of the database and then delete the sensitive info and then use the Add Files button to attach it here.

Thank you.

Jess

Customer: replied 9 months ago.
Hi Jess,Attached is the example database. I have a table that shows a bill of material structure. I need to write a query that will give me the top level part no but for each component a list of there components to the lowest level. Some components are used in multiple parent parts and need to be expanded for each parent. I have also attached another excel spreadsheet to show what I currently have and what I need in MS Access.
Expert:  Jess M. replied 9 months ago.

Thank you. Please give me a moment to check your files and your requirements.

Expert:  Jess M. replied 9 months ago.

I saw the table and the new excel sample. Please clarify and confirm on this:

Are you trying to construct a query utilizing the tbl_BOMStructure alone to come up with your desired 3-field table results?

Customer: replied 9 months ago.
I am trying to construct a query ustilizing the tbl_BomStructure alone and want to have a two field table that shows only the top level parent part in first column and in the second column shows the child, grandchild, greatgrandchild etc of the parent. Sorry in my excel example I want to show you how it would link and work down but essentially would only want to see column D and F. I hope this makes sense.
Expert:  Jess M. replied 9 months ago.

Please give me a moment. I need to expect if these pairings in your sample exist in the table. Have so many rows, this can take some time.

Customer: replied 9 months ago.
No problem
Expert:  Jess M. replied 9 months ago.

Thank you.

Expert:  Jess M. replied 9 months ago.

Please check on this link below and download the file I created for you:

http://filesxpress.com/d-a50ddc88

Is that the structure you want?

Expert:  Jess M. replied 9 months ago.

Holly, were you able to download the file using the link I gave you? If not, please see attached.

Customer: replied 9 months ago.
Hi Jess,
I have left work. I will download tomorrow and let you know.Thanks
Holly
Expert:  Jess M. replied 9 months ago.

OK Holly. Please take your time and let me know when you are ready to resume.

Best regards,

Jess

Customer: replied 9 months ago.
Hi Jess,I just had a look at the table you just sent through is this not the same table as in the database I gave you? If I breakdown and example of one parent part and what I want it may be easier, let's use parent part "0000001". Currently in the table this parent part has five components on level 1 "B0000001", "B2022310", "B2023008FR","B2033615", "B2151094P". I want the query to search all these components on level 1 in the parent pare column and return the components and these will be level 2 components to original parent part "0000001". The query would then carry on do this until there were no more levels. I have attached another table on how it would look and have added a third column denoting the level.If this is two difficult to do in Access can you let me know and we won't spend anymore time on this.Thanks,Holly
Expert:  Jess M. replied 9 months ago.

Hi Holly,

Thank you for writing back with that information. I'm not on my computer right now, I will check your attached Excel file in just a little while when I'm back on my computer desk and I will let you know.

Best regards,

Jess

Customer: replied 9 months ago.
Thanks Jess
Expert:  Jess M. replied 9 months ago.

Hi Holly,

Thank you for your patience. Unfortunately, given your one and only table, this cannot be done in Access using simple or complex queries. There must be changes needed to be made in the table named "tbl_BOMStructure" like adding another field to hold the ID of the parent. So this makes your tbl_BOMStructure table to have additional column that will hold the ID of the parent that you can call like Parent_ID.

Also, to make the query reusable and dynamic, you also need to define a new table listing all IDs.

Is changing your table structure an option for you? I do not know actually how that affects your Access system since I do not have your whole access database, but there is really no way of extracting the parent IDs based on the tbl_BOMStructure alone.

However, I can do it manually in Excel if that will help you implement it in your Access.

Thank you.

Best regards,

Jess

Customer: replied 9 months ago.
Hi Jess,I appreciate you looking at this for me but I think I will call it quits and look at other options. What will be due payable for the work you have done?Thanks,Holly
Expert:  Jess M. replied 9 months ago.

Hi Holly,

Thank you for writing back. Yes, since tbl_BOMStructure is just one table of your MS Access database, changing its structure will surely affect the entire database. But those are the options available for you to come up with the desired results. The safest I believe is creating extra tables instead of directly modifying tbl_BOMStructure.

Regarding the fees, I believe you have already paid the deposit and you just need to rate my service to close the request.

Please rate my service positively (3 to 5 stars/smileys) so that I can get credit for my time and efforts in helping you with this issue.

Thank you for using our service. If you need assistance in the future, you can request me any time by posting a new question starting with "For Jess M" so that I can assist you immediately.

Best regards,
Jess