By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

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

P: 23
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:
Expand|Select|Wrap|Line Numbers
  1. Table: InventoryData2
  2. ID    InputID    OutputID
  3. 1      data1
  4. 2      data1
  5. 3      data2
  6. 4      data3
  7. 5                   data1
  8. 6                   data2
  9.  
I then ran the following SQL code to count and display each unique data set:

SQL code:
Expand|Select|Wrap|Line Numbers
  1. SELECT First(InventoryData2.InputID) AS [InputID Field],  Count(InventoryData2.InputID) AS InputItemCount, 
  2. FROM InventoryData2
  3. GROUP BY InventoryData2.InputID, 
  4. HAVING (((Count(InventoryData2.InputID))>=1));
  5.  
which results in the following correct count of input data
Expand|Select|Wrap|Line Numbers
  1. InputIDField   InputItemCount
  2. data1             2
  3. data2             1
  4. 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

Expand|Select|Wrap|Line Numbers
  1. InputIDField    InputItemCount  OutputItemCount
  2. data1               2                1
  3. data2               1                1
  4. data3               1              
  5.  

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

Expand|Select|Wrap|Line Numbers
  1. SELECT First(InventoryData2.InputID) AS [InputID Field], First(InventoryData2.OutputID) AS FirstOfOutputID, Count(InventoryData2.InputID) AS InputItemCount, Count(InventoryData2.OutputID) AS OutputItemCount
  2. FROM InventoryData2
  3. GROUP BY InventoryData2.InputID, InventoryData2.OutputID
  4. HAVING (((Count(InventoryData2.InputID))>=1)) OR (((Count(InventoryData2.OutputID))>=1));

When this is run, the output is:
Expand|Select|Wrap|Line Numbers
  1. InputIDField   (outputIDField) InputItemCount  OutputItemCount
  2.                    data1                            1
  3.                    data2                            1
  4. data1                                 2
  5. data2                                 1
  6. data3                                 1
  7.  
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
Expand|Select|Wrap|Line Numbers
  1.  InputItemCount
and the
Expand|Select|Wrap|Line Numbers
  1. OutputItemCount
are in the same record. Reiterating, the result should be

Expand|Select|Wrap|Line Numbers
  1. InputIdField    InputItemCount  OutputItemCount
  2. data1                  2              1
  3. data2                  1              1
  4. data3                  1
  5.  

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
  1. 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!
May 2 '17 #1

✓ answered by 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!

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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

P: 23
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
Expert Mod 15k+
P: 31,186
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

Post your reply

Sign in to post your reply or Sign up for a free account.