One Total from multiple Fields

[nsymiakakis]

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

[MMcCarthy]

Hi Nick,

Try this ...

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
FROM [Environmental Rounds];
 

[nsymiakakis]

Hi Nick,

Try this ...

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
FROM [Environmental Rounds];
 

Thank you very much "M" that worked great.
I really appreciate the help.
Nick

[MMcCarthy]

Thank you very much "M" that worked great.
I really appreciate the help.
Nick

No problem Nick

You're welcome

Mary

[nsymiakakis]

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

[MMcCarthy]

_______________ _______________ _______________

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]#

[nsymiakakis]

_______________ _______________ _______________

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

[MMcCarthy]

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

[nsymiakakis]

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];

[MMcCarthy]

Try this ...

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,
FROM [Environmental Rounds]
WHERE [Date] BETWEEN #[Enter Start Date]# And #[Enter End Date]#;