Using SQL/Queries to display two unique 'count' fields (inventory in,out counts)

[TylerBennett]

My end goal is to have a simple inventory system, (a bunch of data input, counted, subtracted against a bunch of output data, which is also counted)

Currently this is done in the same table (from advice to not have separate input and output tables) which results in something like:

Table: InventoryData2
ID    InputID    OutputID
1      data1
2      data1
3      data2
4      data3
5                   data1
6                   data2
 

I then ran the following SQL code to count and display each unique data set:

SQL code:

SELECT First(InventoryData2.InputID) AS [InputID Field],  Count(InventoryData2.InputID) AS InputItemCount, 
FROM InventoryData2
GROUP BY InventoryData2.InputID, 
HAVING (((Count(InventoryData2.InputID))>=1));
 

which results in the following correct count of input data

InputIDField   InputItemCount
data1             2
data2             1
data3             1

Now, to me, the best way to deal with the difference in count numbers is to have the count of each input/output in the same record with the data name which can then be subtracted from each other directly to create another field (ideally)....
i.e

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

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:

InputIDField   (outputIDField) InputItemCount  OutputItemCount
                   data1                            1
                   data2                            1
data1                                 2
data2                                 1
data3                                 1
 

The above was the best I could do but it isn't want I want. Essentially, I want to 'concatenate' the two so that the

 InputItemCount

and the

OutputItemCount

are in the same record. Reiterating, the result should be

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

data1_stock = InputItemCount - OutputItemCount

With the individual tables, I was able to create two tables, each with their own count, but couldn't progress because I didn't know how to compare that count number with any record of the part number... i.e "sure I can take a number in a record of a field and subtract it from another number of another record in another field... but where does this data go and how does the user know if that subtracted number is for data1 or data2... this could be manually entered... but what if data1 changes?" This seemed to involve too much coding and looked to be inefficient which then brought me to the proposed problem above.

If this isn't the correct approach let me know!

[NeoPa]

You're separating the input and output, even in [InventoryData2], into different fields. This isn't what you need. What you need is a field that identifies your data. I'm currently ignorant of the type of data so I'll simply call it [DataID]. Separately you have the volume of such data, which may simply be the number of items. This can be a positive number or a negative one, depending on the direction in or out.

If the different directions are required to be shown separately then you can easily filter on >0 or <0 in the SQL.

Clearly with this structure you'd GROUP BY the [DataID] field and go from there. Hopefully easily, but hey - we're here if more assistance or explanations are required.

Good luck Tyler.

PS Well done for laying the question out so well. There may be things in this area that are still new and unfamiliar to you but you obviously understand the benefit of stating your question as clearly as possible in order to help us to help you. Way to go!

[TylerBennett]

Thank you for the response although I realize now I have going about the wrong method, the data input is simply serial numbers (each input will always be unique) and thus my goal is to simply delete any identical records between the two tables... I will post another question because i'm not sure the best method to go about it but thanks for this response!

(I'm working on proper question formatting haha)

[NeoPa]

TylerBennett:
(I'm working on proper question formatting haha)

That's always very encouraging TBF :-)

You keep asking the questions and we'll keep trying to find answers. The better your questions get - the more value we can include in the answers.