473,888 Members | 2,213 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

One Total from multiple Fields

31 New Member
Hello everyone, I know Access fairly well, but I am very, very weak on SQL. I have a very large table that I need to pull a grand Total from multiple fields. I have created a query to SUM all the fields individually, but I do not know how to add all the field totals to get one Grand Total. Here are 4 fields from my query as an example.

SELECT
Sum([Environmental Rounds].[Fire Extinguishers]) AS [SumOfFire Extinguishers],
Sum([Environmental Rounds].[Fire Exits]) AS [SumOfFire Exits],
Sum([Environmental Rounds].[Fire alarms]) AS [SumOfFire alarms],
Sum([Environmental Rounds].[Evacuation Map]) AS [SumOfEvacuation Map]
FROM [Environmental Rounds];

Please be specific in the programing help if you can, like I said, I am very weak in this area.
Thank you,
Nick
Apr 24 '07 #1
11 2373
MMcCarthy
14,534 Recognized Expert Moderator MVP
Hi Nick,

Try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. Sum([Environmental Rounds].[Fire Extinguishers]) AS [SumOfFire Extinguishers], 
  3. Sum([Environmental Rounds].[Fire Exits]) AS [SumOfFire Exits], 
  4. Sum([Environmental Rounds].[Fire alarms]) AS [SumOfFire alarms], 
  5. Sum([Environmental Rounds].[Evacuation Map]) AS [SumOfEvacuation Map], 
  6. [SumOfFire Extinguishers]+[SumOfFire Exits]+[SumOfFire alarms]+[SumOfEvacuation Map] As GrandTotal
  7. FROM [Environmental Rounds];
  8.  
Apr 25 '07 #2
nsymiakakis
31 New Member
Hi Nick,

Try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. Sum([Environmental Rounds].[Fire Extinguishers]) AS [SumOfFire Extinguishers], 
  3. Sum([Environmental Rounds].[Fire Exits]) AS [SumOfFire Exits], 
  4. Sum([Environmental Rounds].[Fire alarms]) AS [SumOfFire alarms], 
  5. Sum([Environmental Rounds].[Evacuation Map]) AS [SumOfEvacuation Map], 
  6. [SumOfFire Extinguishers]+[SumOfFire Exits]+[SumOfFire alarms]+[SumOfEvacuation Map] As GrandTotal
  7. FROM [Environmental Rounds];
  8.  


Thank you very much "M" that worked great.
I really appreciate the help.
Nick
Apr 25 '07 #3
MMcCarthy
14,534 Recognized Expert Moderator MVP
Thank you very much "M" that worked great.
I really appreciate the help.
Nick
No problem Nick

You're welcome

Mary
Apr 25 '07 #4
nsymiakakis
31 New Member
No problem Nick

You're welcome

Mary
_______________ _______________ _______________

Hi Mary, if I could bug you for one more problem I am having, I need to do a parameter for the query to prompt between two dates. I tried using:
Between [Enter Start Date] And [Enter End Date] under the "DATE" field, but it is returning all the records, not just the specified ones. The "DATE" field is formatted as Date/Time, so I don't know why it isn't working.
Again, any help would be appreciated.
Nick
Apr 25 '07 #5
MMcCarthy
14,534 Recognized Expert Moderator MVP
_______________ _______________ _______________

Hi Mary, if I could bug you for one more problem I am having, I need to do a parameter for the query to prompt between two dates. I tried using:
Between [Enter Start Date] And [Enter End Date] under the "DATE" field, but it is returning all the records, not just the specified ones. The "DATE" field is formatted as Date/Time, so I don't know why it isn't working.
Again, any help would be appreciated.
Nick
WHERE [DateField] BETWEEN #[Enter Start Date]# And #[Enter End Date]#
Apr 25 '07 #6
nsymiakakis
31 New Member
_______________ _______________ _______________

Hi Mary, if I could bug you for one more problem I am having, I need to do a parameter for the query to prompt between two dates. I tried using:
Between [Enter Start Date] And [Enter End Date] under the "DATE" field, but it is returning all the records, not just the specified ones. The "DATE" field is formatted as Date/Time, so I don't know why it isn't working.
Again, any help would be appreciated.
Nick
Hi Mary, I played with this a bit more, and got the date to work, but it is coming up with all the dates as individual records. I need it to still only give me one grand total. In other words combine all the months between the two dates together. I have a feeling this is more intense than I had expected.
Thanks Again,
Nick
Apr 25 '07 #7
MMcCarthy
14,534 Recognized Expert Moderator MVP
Hi Mary, I played with this a bit more, and got the date to work, but it is coming up with all the dates as individual records. I need it to still only give me one grand total. In other words combine all the months between the two dates together. I have a feeling this is more intense than I had expected.
Thanks Again,
Nick
Nick

Can you post the full SQL of the query

Mary
Apr 25 '07 #8
nsymiakakis
31 New Member
Hello Again Mary, thank you for sending the Date parameters.
I tried your parameter by plugging this into the SQL, and I am getting an error stating "Syntax error (missing operator) in query expression.
what did I miss? Here is my code as it stands so far.
Thanks again,
Nick

SELECT Sum([Environmental Rounds].[Fire Extinguishers]) AS [SumOfFire Extinguishers], Sum([Environmental Rounds].[Fire Exits]) AS [SumOfFire Exits], Sum([Environmental Rounds].[Fire alarms]) AS [SumOfFire alarms], Sum([Environmental Rounds].[Evacuation Map]) AS [SumOfEvacuation Map], [SumOfFire Extinguishers]+[SumOfFire Exits]+[SumOfFire alarms]+[SumOfEvacuation Map] AS GrandTotal,
WHERE [Date] BETWEEN #[Enter Start Date]# And #[Enter End Date]#

FROM [Environmental Rounds];
Apr 25 '07 #9
MMcCarthy
14,534 Recognized Expert Moderator MVP
Try this ...
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum([Environmental Rounds].[Fire Extinguishers]) AS [SumOfFire Extinguishers], Sum([Environmental Rounds].[Fire Exits]) AS [SumOfFire Exits], Sum([Environmental Rounds].[Fire alarms]) AS [SumOfFire alarms], Sum([Environmental Rounds].[Evacuation Map]) AS [SumOfEvacuation Map], [SumOfFire Extinguishers]+[SumOfFire Exits]+[SumOfFire alarms]+[SumOfEvacuation Map] AS GrandTotal,
  2. FROM [Environmental Rounds]
  3. WHERE [Date] BETWEEN #[Enter Start Date]# And #[Enter End Date]#;
Apr 25 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

0
2413
by: Chris Hall | last post by:
The records in my database are displayed in a form as follows: %> <form action="report-ammend1.42.asp" method="post"name="form"> <table border=1> <% x = 1
1
10821
by: beavenour | last post by:
I am looking for some help creating a more universal function out of this lame attempt at javascript. I have little experience with javascript and want to be able to universalize this function. So how do I change this script so that I can call any field or any number of fields to be totaled? For example - calculate(myfield01, myfield02) at one total field and calculate(myfield06, myfield08, myfield11) at another total field instead of...
0
1376
by: Richard Holliingsworth | last post by:
Hello: I'm building a report to display statistics on the entire database. I have successfully built a report that groups the entire db on one field and gives me a count of db records for the categories in that field and a total record count for the db. Now, I need to add another "total database" grouping to get another set of record counts. I DO NOT want to nest this group, It MUST consider ALL database records.
3
3268
by: CSDunn | last post by:
Hello, I have 14 fields on a report that hold integer values. The field names use the following naming convention: T1Number, T2Number ....T14Number. I need to get a 'sub total' of all fields as follows: =Sum() ... =Sum() Then I need to get an average of all fields as follows:
2
2336
by: Jana | last post by:
Using Access 97. Background: I have a main report called rptTrustHeader with a subreport rptTrustDetails in the Details section of the main report. The main report is grouped by MasterClientID. There are also several other subreports in the main report's footer. The rptTrustDetails subreport has two grouping levels MasterClientID and ClientID, with headers and footers for each grouping level. In the ClientID footer, I have an...
10
2478
meenakshia
by: meenakshia | last post by:
hi forum:) i have a form in which i have four input fields for pieces to be entered and 4 fields for amount what i want is that the first pieces-t1 should be visible and rest three should not show up on the form unless asked for. can anyone suggest me a way to do this i have come across a lot of help areas where we can add input fields but in my case i have only predefined 3 fields the code is below <td>Pieces-t1</td> <td><input...
7
2148
by: Ecohouse | last post by:
I'm working on a project that was dropped in my lap. It is in Access 2003. There are short time fields which get calculated values (eg. 1:30). But I will need to total up those values for reports in qiueries. When I tried to drop one of the fields in the query builder and used the total function the totals was way off. So I was wondering what would be the best way to do this? Thanks in advance for the help.
58
8167
by: bonneylake | last post by:
Hey Everyone, Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also the parts section an i seem to be having trouble. When i try to insert into the parts section i get the error Invalid character value for cast specification. But not sure what i am doing wrong. Here is what i am using to insert. All the sections...
482
28067
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. what i am trying to display previously entered multiple fields. I am able to get my serial fields to display correctly, but i can not display my parts fields correctly. Currently this is what it does serial information 1 parts 1
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11173
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10772
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10434
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5810
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6011
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4635
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.