Select a select? 1

[SQLScholar]

Hey all,

If i have a database of records which have

pk Recordnum Status ValueA ValueB
1 1 P 10 20
2 1 C 10 20
3 2 P 200 10
4 3 P 120 15
5 3 P 10 1
6 3 C 100 10

P stands for pending - C for complete. Now if i wanted a sheet of all the records where the P for a record num (there may be mulitple values, so summed) doesnt add up to the C.

So in this case it would give me

pk Recordnum Status ValueA ValueB
4 3 P 30 6

Which is the difference between total pending, and total complete for a record.

Any help much appreciated

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[MichaelRed]

see your ;oca; / favorite SQL language documentation re "subquery". Ms. A. supports this, but only in SQL view (or mode), not in the query builder. Since you are obviously not familiar with this, it may take some study to get the details correct but the learning is well worth the effort.




MichaelRed
mlred@verizon.net

 

[r937]

where the P doesn't add up to the C?

perhaps you meant where the sum of ValueAs for P rows doesn't add up to the sum of ValueAs for C rows

select Recordnum 
     , sum(iif(Status='P',ValueA,0)) as PsumA
     , sum(iif(Status='P',ValueB,0)) as PsumB
     , sum(iif(Status='C',ValueA,0)) as CsumA
     , sum(iif(Status='C',ValueB,0)) as CsumB
  from yourtable
group
    by Recordnum
having sum(iif(Status='P',ValueA,0)) 
    <> sum(iif(Status='C',ValueA,0))

rudy

http://r937.com/

SQL Consulting

 

[SQLScholar]

Since you are obviously not familiar with this" - bit judgemental i think. I am familiar with SQL already - actually i did a college module on it - its just the brain needs a bit of tweaking on a monday to remember. Any question on here could be found in a book somewhere - but thats the idea of the forums, just to help out.

Thanks for your answer rudy - i will try it when i have a spare sec. STAR.

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[JonFer]

This is another option:

Select RecordNum, Sum(IIf(Status="P",1,-1)*ValueA) as SumA, Sum(IIf(Status="P",1,-1)*ValueB) as SumB
Group By RecordNum
Having Sum(IIf(Status="P",1,-1)*ValueA) <> 0
    or Sum(IIf(Status="P",1,-1)*ValueB) <> 0

If you entered your "C" values as negatives in the table, you could do a simple sum of the values:

Select RecordNum, Sum(ValueA) as SumA, Sum(ValueB) as SumB
Group By RecordNum
Having Sum(ValueA) <> 0
    or Sum(ValueB) <> 0

You could also write a query that summarizes by RecordNum and Status (probably useful for other situations) and then do a self-join on that query where one instance has criteria Status="C" and the other "P".

Michael - You can write the sub-query SQL in the criteria row or select row in the Query Design window in Access. It just doesn't help you build it.


John

http://www.interautomateconsulting.com

 

[MichaelRed]

JonFer Thanks, I'd forgotten that. Do it so seldom that way anymore.




MichaelRed
mlred@verizon.net