Query results returned into one column (1 Viewer)

[GK in the UK]

I have a query which returns the values I need, but I would like them returned into one column. By that, I mean, I can get three columns in the dataset, called Balance_Grp, Balance_PL, and Balance_BS.
I want to consolidate them into one value, OR, format a string according to whether the value is >0 or <0.
I made a string function fDebitCredit which returns a 32 character string, either like '_______________£123,456,789.02' OR ' £123,456,789.02_______________' (with spaces not underscores but you get the idea)

Now when I put this in my query:
nodeText: nominalName & Switch( [nominalType]="H",fDebitCredit(Balance_Grp), [nominalType]="P",fDebitCredit(Balance_PL), [nominalType]="B",fDebitCredit(Balance_BS))
Access says sub queries cannot be used.

Here is the query. For a single row returned by the query, I want one only of Balance_BS, Balance_PL, Balance_Grp, but returned into the same named column. fGroupTotal is an external function.

SELECT "recid" & [nominalid] AS ID,
       "recid" & [nominalparent] AS parentID,
       tblnominalaccounts.nominalname AS nodeText,
       "recid" AS identifier,
       tblnominalaccounts.nominaltype, Sum(tbltranslines.tlnetvalue) AS NetSum,
       Sum(Iif([thacyrprd] <= [endacyrprd] AND [nominaltype] = "b", Nz([tbltranslines].[tlnetvalue], 0), NULL)) AS Balance_BS,
       Sum(Iif(( [thacyrprd] >= [startacyrprd] )AND ( [thacyrprd] <= [endacyrprd] )AND ( [nominaltype] = "p" ), ( Nz([tlnetvalue], 0) ), NULL))AS Balance_PL,
       Sum(Iif([nominaltype] = "h", Fgrouptotal([nominalid], [startacyrprd],[endacyrprd],[thpbno]), NULL)) AS Balance_Grp
FROM   tbltransheaders
       RIGHT JOIN (tbltranslines
                   RIGHT JOIN tblnominalaccounts
                           ON tbltranslines.tlnominalfk =
                              tblnominalaccounts.nominalid)
               ON tbltransheaders.transheaderid = tbltranslines.tltransheaderfk
GROUP  BY "recid" & [nominalid],
          "recid" & [nominalparent],
          tblnominalaccounts.nominalname,
          "recid",
          tblnominalaccounts.nominaltype
HAVING (( ( tblnominalaccounts.nominaltype ) = "h" ))
        OR ( ( ( Sum(tbltranslines.tlnetvalue) ) <> [thvalue] )
             AND ( ( Max(tbltransheaders.thpostingbatchno) ) >= [thpbno] ) );

 

[theDBguy]

Hi. Can you show us a sample of your data and what you want it to look like?

 

[GK in the UK]

The query returns a result in any *one* of the last 3 columns. Ignore NetSum, it's not filtered for my criteria. (There are balances for Balance_PL but only where nominalType = "P" and they're not showing in the screenshot) I want to plant whatever the value is into a sub directly from the query. Most likely it will be a string function which will return the value formatted into the leftmost side, or the rightmost side, of a string, according to whether it's >0 or <0

The query gets the right results I just need the value returned in one column for each row.

 

[theDBguy]

The query returns a result in any *one* of the last 3 columns. Ignore NetSum, it's not filtered for my criteria. (There are balances for Balance_PL but only where nominalType = "P" and they're not showing in the screenshot) I want to plant whatever the value is into a sub directly from the query. Most likely it will be a string function which will return the value formatted into the leftmost side, or the rightmost side, of a string, according to whether it's >0 or <0

The query gets the right results I just need the value returned in one column for each row.

View attachment 82254

Hi. Thanks. It looks like each record will only have a value in one of those columns, correct? If so, you can either use an IIf() statement or simply add the three columns together (if you want a number, but use Nz() to convert the null values to a 0) or concatenate them (if you want a string).

 

[GK in the UK]

I tried the IIF and I also tried a Switch but when I include the fGroupTotal function Access says sub queries are not allowed. I can try again, but ... leaving it alone for a short while ...

Would a Union query do it? I've never done one. But I'm thinking

Iteration 1 all "B" type nominals and put the sum into Total.
Iteration 2 all "P" type nominals and put the sum into Total.
Iteration 3 all "H" type nominals and put the sum into Total.

Then Union the 3 queries and I've got the Total in one place. Would that work? I think it's something I'm going to try.