Cannot Sum a column of figures in a Query

[elder73]

My table consists of: "ID" "Item" and "Amount"

When I go to the Query in Design view, and list all the above fields. In the "ID" column I list it as not showing.

The Table is listed as Family Data for all three columns

In the "Item" column it is listed as Visible and the function as Group.

The "Amount" column is listed as Visible and the function as Sum.

If I don't list anything under Alias the field name becomes SUM("FamilyData"."Amount") so a command is there on that column but nothing happens. When I run the Query all I get is a listing with no total in sight.

Any help would be really appreciated.

[Sliderule]

Per your description ( which was very good, thank-you ), I am not sure why the results returned to you is as described, unless, that calculated sums are all zero and you have formatted zero to display that way.

How about this, follow these instructions, and, see what is displayed. I am adding two additional columns:

1. "Minimum"
2. "Maximum"

just to see if they are calculated correctly .

1. Open your OpenOffice Base file ( *.odb )
   
2. On the left, click on the Queries icon
   
3. Under Tasks, click on: Create Query in SQL View...
   
4. Copy and paste the following:
   

   Code:

   Select      "FamilyData"."Itme"    , SUM("FamilyData"."Amount") as "Sum"    , MIN("FamilyData"."Amount") as "Minimum"    , MAX("FamilyData"."Amount") as "Maximum" From "FamilyData" as "FamilyData" Group By "FamilyData"."Item"

   
   
5. Run the Query, either:
   1. Press the F5 key
      
   2. Press the Run Query icon on the toolbar
      
   3. From the Menu: Edit -> Run Query
   
   
6. You can SAVE the query if / when it works, OR, from the Toolbar icon, press: Switch Design View On/Off to see what it looks like if you had used the GUI ( Graphical User Interface ) to create it.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.

[elder73]

[Sliderule]

I am sending you a PM ( Private Message ).

Sliderule

[elder73]

This is how Sliderule solve my problem:


You could say that what you really wanted was a Query that included UNION. And, a UNION Query is a query that, because of the Base Parser, at the current time, it must be Run in Direct Mode.

The actual code I used was:


-- Query Written by Sliderule 2012-05-17
-- Uses a UNION Query, so, at the current time it must by run direct . . . passed to DB engine withOUT Base Parserr
Select
"Item",
"ID",
TO_CHAR("Date",'YY-MM-DD') as "Date",
"Amount",
"Income",
"Food",
"Gasoline",
"Auto",
"Rent",
"Insurance",
"Clothing",
"Misc",
"Tithe",
"Taxes"
FROM "FamilyData"

UNION ALL

SELECT
'** TOTAL **',
999999999,
'',
SUM("Amount"),
SUM("Income"),
SUM("Food"),
SUM("Gasoline"),
SUM("Auto"),
SUM("Rent"),
SUM("Insurance"),
SUM("Clothing"),
SUM("Misc"),
SUM("Tithe"),
SUM("Taxes")
FROM "FamilyData"

ORDER BY "ID"