SQL query that net amounts

[TSO456]

Hi,

I need to create an Access query that can cycle through different ACCT numbers and net the Negative CASH (negcash) from the positive Cash (POScash). And display the net amount. For Example ACCT 90313-13-0005 should net to
-200.00 The ACCT field is not numeric it is text.
Thanks
Jill

ACCT category NEGcash POScash
903-13-0005 EDT SUB-TOTALS: -900.00
903-13-0005 SHARE PURCHASE 500.00
903-13-0005 SHARE PURCHASE 200.00
803-05-8888 EDT SUB-TOTALS: -500.00
803-05-8888 SHARE PURCHASE 100.00

 

[mwolf00]

SELECT sum(NEGcash) + sum(POScash) FROM myTable WHERE acct = "903-13-0005" Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048

mikewolf@tst-us.com

 

[RiverGuy]

SELECT ACCT, Sum(NEGcash + POScash) FROM SOMETHING GROUP BY ACCT

 

[TSO456]

I need to able to display the ACCT number column and I have multiple ACCT to do sums on. so I would like for the quesry result to look something like this

ACCT NEGCASH+POSCASH
903-13-0005 -200
803-05-8888 -400

Remember the ACCT account is a text field and can not be changed to a number field.

Thanks
Jill

 

[RiverGuy]

OK. What I type will do that. But I wasn't naming the calculated column.

SELECT ACCT, Sum(NEGcash + POScash) AS [NEGCASH + POSCASH] FROM SOMETHING GROUP BY ACCT.

The data type of ACCT has nothing to do with what you want to do, because you don't want any calculations of the ACCT's data.

I can't tell if you have null values in some fields. If so, you might need to make use of the null zero function.

 

[TSO456]

Ok, now the table looks something like this in other words the negcash and poscash are in one column. How do I subtract the two amounts to get one number on one line?


903-13-0005 EDT SUB-TOTALS: -900.00
903-13-0005 SHARE PURCHASE 500.00

Thanks
Jill

 

[RiverGuy]

How did you end up with that? You must have select and grouped by that 2nd field with the description. If you would have left it off, you would have gotton 1 line.

 

[TSO456]

Yes I did. I found out that some of the ACCT have more than 2 categories (i.e EDT SUB-TOTALS:, SHARE PURCHASE, and SHARE REDEMPTION) and that I always have to display EDT SUB-TOTALS and always subtract SHARE PURCHASE from SHARE REDEMPTION and call it something)
I think this is much more complicated.
Thanks
Jill

 

[RiverGuy]

Well, the least complicated way to do it would be to make it in 3 queries.

You could easily accomplish it by joining the same table to itself with different where criteri (one for purchase and one for redemption), but it looks like there could be more than one for each product ID in either.

So, make a view select of whatever you wanted summed for the SHARE PURCHASE. Then make one for SHARE redemption (remembering to group on Acct). Then join them together by Acct and do the subtraction there