Currently this is done in the same table (from advice to not have separate input and output tables) which results in something like:
Expand|Select|Wrap|Line Numbers
- Table: InventoryData2
- ID InputID OutputID
- 1 data1
- 2 data1
- 3 data2
- 4 data3
- 5 data1
- 6 data2
SQL code:
Expand|Select|Wrap|Line Numbers
- SELECT First(InventoryData2.InputID) AS [InputID Field], Count(InventoryData2.InputID) AS InputItemCount,
- FROM InventoryData2
- GROUP BY InventoryData2.InputID,
- HAVING (((Count(InventoryData2.InputID))>=1));
Expand|Select|Wrap|Line Numbers
- InputIDField InputItemCount
- data1 2
- data2 1
- data3 1
i.e
Expand|Select|Wrap|Line Numbers
- InputIDField InputItemCount OutputItemCount
- data1 2 1
- data2 1 1
- data3 1
To achieve this, I altered the SQL code to bring in the output data, shown below
Expand|Select|Wrap|Line Numbers
- SELECT First(InventoryData2.InputID) AS [InputID Field], First(InventoryData2.OutputID) AS FirstOfOutputID, Count(InventoryData2.InputID) AS InputItemCount, Count(InventoryData2.OutputID) AS OutputItemCount
- FROM InventoryData2
- GROUP BY InventoryData2.InputID, InventoryData2.OutputID
- HAVING (((Count(InventoryData2.InputID))>=1)) OR (((Count(InventoryData2.OutputID))>=1));
When this is run, the output is:
Expand|Select|Wrap|Line Numbers
- InputIDField (outputIDField) InputItemCount OutputItemCount
- data1 1
- data2 1
- data1 2
- data2 1
- data3 1
Expand|Select|Wrap|Line Numbers
- InputItemCount
Expand|Select|Wrap|Line Numbers
- OutputItemCount
Expand|Select|Wrap|Line Numbers
- InputIdField InputItemCount OutputItemCount
- data1 2 1
- data2 1 1
- data3 1
Is this a good approach to this issue ? I'm going about this in this manner mainly because once the count for each is in the same record, then my subtraction of InputItemCount and OutputItemCount are a subtraction of the same record and thus results in
Expand|Select|Wrap|Line Numbers
- data1_stock = InputItemCount - OutputItemCount
If this isn't the correct approach let me know!