Resolve Negative Number Difference in Query

[awoitte]

A query from one of our software databases shows quantity of items pulled from inventory known as a counter-release. However, the report is showing both the quantity that was released, as well as the quantity that was re-stocked into the inventory because it was no longer used, or the counter-return. The counter-return is being shown as a negative number, which is helpful because it differentiates the two. This is also being shown in the same column. If they were in different columns I could do total the difference, but everything is vertical to each other. This information isn't very helpful as I need the amount that was released and used. I'm limited to the two fields and and the front end doesn't offer a field for showing only the amount that was released and not returned.

Is there a way I can somehow show the difference between these two fields showing the actual amount of parts that were taken and kept from the stock?

 

[CJ_London]

the way the data has been presented to you - I presume the bottom image - is a normalised structure so any reason you can't just group on the D column and sum the C column?

For D=33546 will give you 1 and 33744 will give you 0

 

[awoitte]

That sounds like something that would work, but how do I group two cells and sum the total into another column?

 

[Mark_]

If you want D=34002 to return C=10 instead of C=-10, you would use an expression for the ABS(C) to return the absolute value.

 

[awoitte]

If you want D=34002 to return C=10 instead of C=-10, you would use an expression for the ABS(C) to return the absolute value.

I never said I wanted the negative numbers to return as positive. I want the difference between the positive and negative integer that have the same corresponding number to the right of it.

 

[CJ_London]

group by queries are a commonly used type of query

in your query design, select the sigma button on the ribbon, drag down onto the query grid the fields required (D and C). In the totals row, group by D and sum C. You may need other fields as well to get the result you want

 

[awoitte]

group by queries are a commonly used type of query

in your query design, select the sigma button on the ribbon, drag down onto the query grid the fields required (D and C). In the totals row, group by D and sum C. You may need other fields as well to get the result you want

Not sure what's supposed to be happening here. Followed what I believed were your instructions: Selected Sigma and changed the Total = SUM for C. Group By was already defaulted in D as well as all other columns. Still seeing negative numbers and not getting totals.

 

[The_Doc_Man]

Are you looking for both individual totals and grand totals at the same time? Can't do that so easily in a single query, but not at all impossible in a report.

 

[awoitte]

Are you looking for both individual totals and grand totals at the same time? Can't do that so easily in a single query, but not at all impossible in a report.

I'm pulling data from my stockroom inventory transactions. For some reason, however, I'm receiving two quantities (positive/negative) quantity released and the quantity returned respectively.

All I want to know is how many parts were actually taken out and kept out. I'm trying to avoid having a person look at this spreadsheet and say "There were 26 of these parts released, but then 3 got returned so we used 23." Instead, I just want it to show 23 as the number of items that were released.

 

[jdraw]

Released, returned and Used??
Typically with transactions:
Incoming/purchases/acquisitions are + positive.
Outgoing/sales/disseminations are - negative

In Inventory = Incoming - Outgoing

OR

In Inventory = Released - Returned

 

[awoitte]

Released, returned and Used??
Typically with transactions:
Incoming/purchases/acquisitions are + positive.
Outgoing/sales/disseminations are - negative

In Inventory = Incoming - Outgoing

Lol?..........

 

[CJ_London]

I can't read your images, resolution is too low, but looks like you are grouping by a lot of fields. Would normally only expect to group by one field (D per your original post)

Perhaps you are trying to get a running sum?

 

[awoitte]

I'm going to try to elaborate one more time as simply as I can - Going off the original post & image -.

Looking at the bottom portion of the image (results)

Column C (2nd and 3rd row) - Two numbers POSITIVE 3, NEGATIVE 2.
The 3 comes from the amount of parts released (or checked out) initially. The negative 2 represents 2 of the original 3, that were returned. Only 1 of the 3 parts checked out were kept, with 2 being returned. I now want to sum those two (to show that on transaction 33546 - Column D -) had 1 checked out part.

 

[CJ_London]

1 of the 3 parts checked out were kept, with 2 being returned. I now want to sum those two (to show that on transaction 33546 - Column D -) had 1 checked out part

per post #2

so any reason you can't just group on the D column and sum the C column?

by my reckoning that is two columns but your image seems to have more

your query would look something like

SELECT Sum(C) as sumofC
FROM myTable
GROUP BY D

you responded with

Group By was already defaulted in D as well as all other columns

to which I said

Would normally only expect to group by one field (D per your original post)

Explain why this doesn't work for you and perhaps we can move forward