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.
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. Please give me a moment to check your files and your requirements.
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?
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.
Please check on this link below and download the file I created for you:
Is that the structure you want?
Holly, were you able to download the file using the link I gave you? If not, please see attached.
OK Holly. Please take your time and let me know when you are ready to resume.
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.
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 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.