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:
I then ran the following SQL code to count and display each unique data set:

SQL code:
which results in the following correct count of input data
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


To achieve this, I altered the SQL code to bring in the output data, shown below


When this is run, the output is:
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 and the are in the same record. Reiterating, the result should be


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 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!

May 2 '17 #1

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!

May 3 '17 #2

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)

May 9 '17 #3

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.

May 9 '17 #4