0

I do have the next query to be run in Access, It does well, but as you can see, there is a similitude in the first 3 SELECT. I want to know if there is the possibility to optimize this, since I will be adding houndreds of c's.

Original Table

SELECT id, IIF(c1>=0, c1, ) AS positive, IIF(c1<0, c1, ) AS negative  FROM tblcostos 

UNION ALL

SELECT id, IIF(c2>=0, c2, ) AS positive, IIF(c2<0, c2, ) AS negative  FROM tblcostos 

UNION ALL 

SELECT id, IIF(c3>=0, c3, ) AS positive, IIF(c3<0, c3, ) AS negative  FROM tblcostos

UNION ALL 

SELECT 'sum  positivo + negative' AS id, SUM(c1 + c2 + c3) AS positive, "" AS negative FROM tblcostos

ORDER BY id;

Query Table

I require the final table to be shown like this, because I'll end up with a .txt with that format

dirojas
  • 57
  • 8
  • 2
    Sample data and the results you're trying to obtain from that data would be helpful here. – Ken White Sep 07 '18 at 01:21
  • 1
    Table not `Normalized` and you are limited to 255 fields in a table/query. – ComputerVersteher Sep 07 '18 at 02:26
  • 3
    don't add hundred's of "c's" as new columns. SQL is "row oriented" and your queries will be MUCH easier if you change to using extra rows instead of extra columns. research `normalization` – Paul Maxwell Sep 07 '18 at 05:26
  • Sounds like you want to run [UNPIVOT](https://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access-2010) - there is no other way in Access than lots of UNION clauses. – Andre Sep 07 '18 at 06:10
  • Thanks for those advices, Normalization of tables and "row oriented" – dirojas Sep 07 '18 at 16:21

1 Answers1

-2

I dont know what you try to archive but it seems like you want so sort your table by the ID and split them in positive and negative and sum all.

'Order after ID
SELECT ID FROM tblcosts ORDER BY ID DESC 'or mabye ASC

'Positive and Negative columns
SELECT ID, IIF( ID > 0, ID, Null) AS Positive, IIF( ID < 0, ID, Null) AS Negative FROM tblcosts ORDERBY ID DESC

'Sum
SELECT SUM(ID) AS SummeID FROM tblcosts
Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20