Resolve Negative Number Difference in Query (1 Viewer)

awoitte · Mar 16, 2018

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 · Mar 16, 2018

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 · Mar 16, 2018

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

Mark_ · Mar 16, 2018

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 · Mar 16, 2018

Mark_ said:
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 · Mar 17, 2018

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 · Mar 19, 2018

CJ_London said:
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 · Mar 19, 2018

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 · Mar 19, 2018

The_Doc_Man said:
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 · Mar 19, 2018

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 · Mar 19, 2018

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

In Inventory = Incoming - Outgoing

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

CJ_London · Mar 19, 2018

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 · Mar 19, 2018

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 · Mar 19, 2018

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

Resolve Negative Number Difference in Query (1 Viewer)

awoitte

Registered User.

Attachments

CJ_London

Super Moderator

awoitte

Registered User.

Mark_

Longboard on the internet

awoitte

Registered User.

CJ_London

Super Moderator

awoitte

Registered User.

Attachments

The_Doc_Man

Immoderate Moderator

awoitte

Registered User.

jdraw

Super Moderator

awoitte

Registered User.

CJ_London

Super Moderator

awoitte

Registered User.

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread