I'm using DBaccess and I have a Table includind these columnsA,B,C,D,E,F,G,H
I used this SQL only for B,C,D,E,F,G,H
" SELECT B,C,D,E, SUM(F),LAST(G),LAST(H)FROM Table1 GROUP BY B,C,D,E "

I want to join these three statments SQL in one SQL
(SELECT B,C,D,E, SUM(F) Where A='Buy', LAST(G),LAST(H)FROM Table1 GROUP BY B,C,D,E ")+

((SELECT B,C,D,E, SUM(F) Where A='Sell', LAST(G),LAST(H)FROM Table1 GROUP BY B,C,D,E ")-

SELECT B,C,D,E, SUM(F) Where A='Damage', LAST(G),LAST(H)FROM Table1 GROUP BY B,C,D,E "))

Not tough. Simple.

You clearly have the WHERE clause in the wrong place, of course.
And then all you need is UNION:
But, if you examine that query, you can easily see it can be MUCH simpler:
But this doesn't really make sense. Since you aren't *also* SELECTing A, you won't know which record applies to what value of A.

Maybe you should explain in words what you are after, since the SQL doesn't make sense.

That was wonderful but I didn't explain clearly what I want
If we take the second SQL
this will sum all values in F according buy, sell,damage and showed the result in the same row with b,c,d,e… g,h
so if there are:

-Tow Buy where F= 10, F=5
-Three Sell where F = 5, F =15, F = 10
-Two damage where F =20 , F = 5
The [u]resuLt </u>will be : (10+5) + (5+15+10) + (20+5) = 70

I want to make the code like this example
The result:
(10+5) + ((5+15+10) - (20+5)) = 20

OH!!!

Yes, reasonably easy to do!
Do you see it?

The WHERE clause ensures that you'll only get records where A is one of the three values, BUY, SELL, or DAMAGE.

Then in the SUM, we use IIF to check if A is DAMAGE. If so, we subtract F (we SUM a negative F); if not (that is, if A is BUY or SELL) we add F (we SUM a positive F).

Okay?

OH!.What is this. How can you do like this. I'm looking on the web to find any advanced lesson bu I couldn't. Every thing you did is new for me and I didn't except there is like those way in SQL quarries.
By the way I tried this to make Sum(F) for Buy (+) and Sum(F) for Sell and Damage (-). Is this way true.
And Thank you very mush for help!

Looks right to me.

Access and MySQL use this kind of "trinary function" to allow you to select one of two different values, based on whether a condition is true or false. (In MySQL, the name is just "IF" instead of "IIF".)

That is:
    IIF( condition, value1, value2 )
can be implemented in VB thus:
    Function IIF( condition, value1, value2 )
        If condition Then Return value1 Else Return value2
    End Function
and in C/Java/C# thus:
    int IIF( condition, value1, value2 )
    {
        if ( condition ) return value1;
        else return value2;
    }
But those languages don't need this function because they have the TRINARY OPERATOR:
    ( condition ? value1 : value2 )
which does *exactly* the same thing.

Other databases--SQL Server, Oracle, Sybase, and even MySQL--support the CASE WHEN construction:
    ( CASE WHEN condition THEN value1 ELSE value2 END )
which is effectively the same thing (except that CASE WHEN can be used in much more complex and useful ways).

So...

All that
     IIF( A='Buy', F, -F )
is doing is saying "when the condition A='Buy' is true, then use the value F, otherwise use the value -F."

Really pretty simple once you get used to it!

Incidentally, we *COULD* have written that expression other ways to get the same results:
     IIF( A<>'Buy', -F, F )
or
     IIF( A='Sell' OR A='Damage', -F, F )
or
     IIF( A IN ('Sell','Damage'), -F, F )

Do you see that? The IN( ) operator is just a handy way to make an "OR" test of multiple values.

Hope this helps.

p.s.: You don't see IIF( ) explained much becuase the "big guys" in the DB world--SQL Server and Oracle--don't allow it. As noted, they use CASE WHEN instead.

every time you surprise me.Where can I find like this expalnation on the web.

Honestly, I don't know. I have just learned all this stuff over many years.
(I've been using Access since 1997, for example.)

Mostly, I just read the manuals for products. That is, the manufacturer's documentation.

And they are typically quite complete.
But what they don't tell you about is how to use IIF in the middle of a SUM, for example.
That's just something you learn by experimenting.

Thank you very mush for help!
It was a very useful topic for me.
Thank you again.