I know my initial setup may be a little off in where my queries are ran and whatnot, but I'm just building this up and figuring it out. I have my report's record source set to this query:
Code:
SELECT dbo_MIMOH.buildItem, dbo_MIMOH.bomItem, dbo_MIMOH.bomRevFROM dbo_MIMOH
WHERE dbo_MIMOH.moStat = 1;
This grabs all open Manufacturing Orders from the database. For each result, I run it through this query:
Code:
SELECT MIBOMD.bomItem, MIBOMD.bomRev, MIBOMD.partId, MIITEM.revId, MIBOMD.qty,
MIBOMD2.partId AS partId2, MIITEM2.revId AS revId2, MIBOMD2.qty AS qty2,
MIBOMD3.partId AS partId3, MIITEM3.revId AS revId3, MIBOMD3.qty AS qty3,
MIBOMD4.partId AS partId4, MIITEM4.revId AS revId4, MIBOMD4.qty AS qty4,
MIBOMD5.partId AS partId5, MIITEM5.revId AS revId5, MIBOMD5.qty AS qty5
FROM MIBOMD
LEFT JOIN MIITEM ON MIITEM.itemId = MIBOMD.partId
LEFT JOIN MIBOMD AS MIBOMD2 ON MIBOMD2.bomItem = MIBOMD.partId AND MIBOMD2.bomRev = MIITEM.revId
LEFT JOIN MIITEM AS MIITEM2 ON MIITEM2.itemId = MIBOMD2.partId
LEFT JOIN MIBOMD AS MIBOMD3 ON MIBOMD3.bomItem = MIBOMD2.partId AND MIBOMD3.bomRev = MIITEM2.revId
LEFT JOIN MIITEM AS MIITEM3 ON MIITEM3.itemId = MIBOMD3.partId
LEFT JOIN MIBOMD AS MIBOMD4 ON MIBOMD4.bomItem = MIBOMD3.partId AND MIBOMD4.bomRev = MIITEM3.revId
LEFT JOIN MIITEM AS MIITEM4 ON MIITEM4.itemId = MIBOMD4.partId
LEFT JOIN MIBOMD AS MIBOMD5 ON MIBOMD5.bomItem = MIBOMD4.partId AND MIBOMD5.bomRev = MIITEM4.revId
LEFT JOIN MIITEM AS MIITEM5 ON MIITEM5.itemId = MIBOMD5.partId
WHERE MIBOMD.bomItem = '2263144'
Replace the number 2263144 with the textbox from the report's detail tab and it runs per result of the first query. I have it set to a specific number and syntax is a little off because this is being worked on in SQL Server Management Studio currently, which is where I copied the code from. This second query gives me results that look like this:
Now, I want to use vba to loop through these results and grab the last non NULL value for partid# and set it in an array. Then I can group like items in the array and sort them how I would like. After that I want to display the results of the array, after formatting, on my report. I'm not sure how to approach the last portion of this or where the queries and whatnot need to go for this to work out correctly.