• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

Adding a total row to a select query Access 2003

Hi Experts.

I've stumbled on this challenge for quite some time and thought I could get some great piece of advice within the community. I need to add a Grand Total row to a select query !!!

I have designed a query that filters through my records to indicate equipment needs for a specific location.( See 1_Query_Select_Design).  The query prompts for the region, Type of facility (airport/ seaport) and finally the name of the facility and upon values entered by the user returns a set of rows for the specific location ( See 2_Query_Select_Result). So far so good.

Now I need to summarize the total number of equipment on Record and Needed on that returned selection by possibly inserting a row at the end of the selection that will compute the items (3_Adding_TotalRow_To_Select_Query)

I've read that I should use a temporary_table on my query design window that sort of store some data temp. Anyways, I have no idea how to implement screencast 3.

Hope you can lend some dearly needed help.

Many thanks in advance,
Jeewai
1-Query-Select-Design.jpg
2-Query-Select-Result.jpg
3-Adding-TotalRow-To-Select-Quer.jpg
0
jeewai
Asked:
jeewai
  • 2
  • 2
  • 2
  • +2
3 Solutions
 
peter57rCommented:
You cannot do this with a single select statement.

You need a Select statment which produces the details and a second select statement which just produces the totals .
You can use a Union All query to combine the two.

Select POEID, Region , ...etc ....., Equiomentname, need, onrecord from tablename
Union All
Select "Totals" as POEID, Null as Region, Null as ...etc...., Null as Equiomentname, Sum(Need) as Needsum, Sum(Onrecord) as Onrecordsum from tablename  Group By POEID, Region, ...other fields, Equiomentname
0
 
Rey Obrero (Capricorn1)Commented:
you will need a union query to accomplish this.
using the SQL view of your select query

select ... {this is the sql statement of your select query }


union
select 'Query Total','','','','','',Sum([need]),sum([onrecord])
from tblPOEIDequipment


0
 
Dale FyeCommented:
Or, you could create a report based on the query, and put the totals in the report footer.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
<No Points wanted>

Though a query will give you what you want, I am with fyed.

A Report has a lot more options concerning the way the output is displayed
(Page breaks, Grouping, Headers/Footers, Page Numbering, Printing, ...etc)

You can use the raw query (No Grouping) and use the report wizard to create a grouped report.
Then simply select the "Needs" and "OnRecord" fields to summarize with a Sum.

So play around with this as well, it's actually fun in most cases...
(to me at least)

;-)

JeffCoachman
0
 
Jeffrey CoachmanCommented:
Or just glom this...
Database5.mdb
0
 
jeewaiAuthor Commented:
Hi guys,

Thanks all for the great advice. I'm considering creating a report at a later stage to present the results from the query but for now I need to get the query displaying these results. Thanks to Fyed and boag2000 for this.

I followed diligently the examples given by peter57r  and capricorn1. Both allow me to obtain an additional row at the bottom of my select query as I was expecting. Great stuff !!!  HOWEVER, THE grand total of Need and Onrecord computes all the values without only considering the selection. See SQL query attached. I tried to tweak it so that the query returns not only according to the criteria entered (Having ... like[]) but also sums the last row accordingly.

Please could you look into my SQL query and tell where to modify it ?

Thanks again,
Jeewai



SELECT tblPOE.POEID, tblPOE.Region, tblPOE.CheckPointType, tblPOE.CheckpointName, tblEquipment.EquipmentID, tblEquipment.EquipmentName, tblPOEIDEquipment.Need, tblPOEIDEquipment.OnRecord
FROM tblPOE INNER JOIN (tblEquipment INNER JOIN tblPOEIDEquipment ON tblEquipment.EquipmentID = tblPOEIDEquipment.EquipmentID) ON tblPOE.POEID = tblPOEIDEquipment.POEID


GROUP BY tblPOE.POEID, tblPOE.Region, tblPOE.CheckPointType, tblPOE.CheckpointName, tblEquipment.EquipmentID, tblEquipment.EquipmentName, tblPOEIDEquipment.Need, tblPOEIDEquipment.OnRecord
HAVING (((tblPOE.Region) Like [Forms]![frmSearch_Equipment]![Region]) AND ((tblPOE.CheckPointType) Like [Forms]![frmSearch_Equipment]![CheckPointType]) AND ((tblPOE.CheckpointName) Like [Forms]![frmSearch_Equipment]![CheckpointName]))
ORDER BY tblEquipment.EquipmentID;

Union All
Select "Totals" as POEID, Null as Region, Null as CheckPointType, Null as CheckpointName, Null as EquipmentID, Null as Equipmentname, Sum(Need) as Needsum, Sum(Onrecord) as Onrecordsum from tblPOEIDEquipment

Open in new window

New-Query.jpg
0
 
peter57rCommented:
Copy Line 6 into a new line 11 and change 'Having' to 'Where' in the copy.
0
 
jeewaiAuthor Commented:

Thanks peter57r for the hints. It still took me a little while to play with the SQL code but I finally got it. Here it is attached.


SELECT tblPOE.POEID, tblPOE.Region, tblPOE.CheckPointType, tblPOE.CheckpointName, tblEquipment.EquipmentID, tblEquipment.EquipmentName, tblPOEIDEquipment.Need, tblPOEIDEquipment.OnRecord
FROM tblPOE INNER JOIN (tblEquipment INNER JOIN tblPOEIDEquipment ON tblEquipment.EquipmentID = tblPOEIDEquipment.EquipmentID) ON tblPOE.POEID = tblPOEIDEquipment.POEID

GROUP BY tblPOE.POEID, tblPOE.Region, tblPOE.CheckPointType, tblPOE.CheckpointName, tblEquipment.EquipmentID, tblEquipment.EquipmentName, tblPOEIDEquipment.Need, tblPOEIDEquipment.OnRecord
HAVING (((tblPOE.Region) Like [Forms]![frmSearch_Equipment]![Region]) AND ((tblPOE.CheckPointType) Like [Forms]![frmSearch_Equipment]![CheckPointType]) AND ((tblPOE.CheckpointName) Like [Forms]![frmSearch_Equipment]![CheckpointName])
AND ((tblEquipment.EquipmentName) Like [Forms]![frmSearch_Equipment]![EquipmentName]))
ORDER BY tblEquipment.EquipmentID;

UNION ALL Select Null as POEID, Null as Region, Null as CheckPointType, Null as CheckpointName, "Totals" as EquipmentID, Null as Equipmentname, Sum(Need) as Needsum, Sum(Onrecord) as Onrecordsum
FROM tblPOE INNER JOIN (tblEquipment INNER JOIN tblPOEIDEquipment ON tblEquipment.EquipmentID = tblPOEIDEquipment.EquipmentID) ON tblPOE.POEID = tblPOEIDEquipment.POEID
WHERE (((tblPOE.Region) Like [Forms]![frmSearch_Equipment]![Region]) AND ((tblPOE.CheckPointType) Like [Forms]![frmSearch_Equipment]![CheckPointType]) AND ((tblPOE.CheckpointName) Like [Forms]![frmSearch_Equipment]![CheckpointName])
AND ((tblEquipment.EquipmentName) Like [Forms]![frmSearch_Equipment]![EquipmentName]));

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now