Q:

conditional IIF in a JOIN

I have the next data base:

Table Bill:

Table Bill_Details:

And Table Type:

I want a query to show this result:

The query as far goes like this:
SELECT 

Bill.Id_Bill, 
Type.Id_Type, 
Type.Info, 
Bill_Details.Deb, 
Bill_Details.Cre, 
Bill.NIT, 
Bill.Date2, 
Bill.Comt

FROM Type 

RIGHT JOIN (Bill INNER JOIN Bill_Details 
ON Bill.Id_Bill = Bill_Details.Id_Bill)
ON Type.Id_Type = Bill_Details.Id_Type

ORDER BY Bill.Id_Bill, Type.Id_Type;

With this result:

I'm not sure how to deal or how to include this: 
Type.600, 
Type."TOTAL",
IIF(SUM(Bill_Details.Deb) - Sum(Bill_Details.Cre) >= 0, ABS(SUM(Bill_Details.Deb) - Sum(Bill_Details.Cre)), "" ), 
IIF(SUM(Bill_Details.Deb) - Sum(Bill_Details.Cre) <= 0, ABS(SUM(Bill_Details.Deb) - Sum(Bill_Details.Cre)), "" )

The previous code is the responsable of include new data in some fields, since all of the other fields will carry the same data of the upper register. I'll apreciate some sugestions to acomplish this.  

A:

Here is a revised version of the UNION which you removed from the question.  The original query was a good start, but you just did not provide sufficient details about the error or problem you were experiencing.  My comments were not meant to have you remove the problem query, only that you needed to provide more details about the error or problem.  In the future if you have a UNION, make sure the each query of the UNION works separately.  Then you could debug problems easier, one step at a time.
Problems which I corrected in the second query of the UNION:

Removed reference to table [Type] in the query, since it was not part of the FROM clause.  Instead, I replaced it with a literal value.
Fixed FROM clause to join both [Bill] and [Bill_Details] tables.  You had fields from both tables, so why would you not join on them just like in the first query of the UNION?
Grouped on all fields from table [Bill] referenced in the SELECT clause.  You must either group on all fields, or include them in aggregate expressions like Sum() or First(), etc.
Replaced empty strings with Nulls for the False cases on Iif() statements.

SELECT 
    Bill.Id_Bill, Type.Id_Type, Type.Info,
    Bill_Details.Deb, 
    Bill_Details.Cre, 
    Bill.NIT, Bill.Date2, Bill.Comt
FROM 
    Type RIGHT JOIN (Bill INNER JOIN Bill_Details
        ON Bill.Id_Bill = Bill_Details.Id_Bill)
    ON Type.Id_Type = Bill_Details.Id_Type;

UNION

SELECT 
    Bill.Id_Bill, 600 As Id_Type, "TOTAL" As Info,
    IIF(SUM(Bill_Details.Deb) - Sum(Bill_Details.Cre) >= 0, ABS(SUM(Bill_Details.Deb) - Sum(Bill_Details.Cre)), Null ) As Deb, 
    IIF(SUM(Bill_Details.Deb) - Sum(Bill_Details.Cre) <= 0, ABS(SUM(Bill_Details.Deb) - Sum(Bill_Details.Cre)), Null ) As Cre,
    Bill.NIT, Bill.Date2, Bill.Comt

FROM Bill INNER JOIN Bill_Details
    ON Bill.Id_Bill = Bill_Details.Id_Bill

GROUP BY Bill.Id_Bill, Bill.NIT, Bill.Date2, Bill.Comt;

