Column Totals in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that I am operating ONLY in datasheet view with the following
information:

Records/Rows

Registered Dietician
Medical Office Assistant
Workshop Materials
Workshop Incentives
Workshop Snack
Workshop Food Demo
Office Support
Telephone
FUEL
ESMM
Training
Computer
Multifunction Machine
Durable Goods
Non-reusable goods
PA Equipment
RD Travel
After school Transport
Total

and the Following Columns:
Current Contract Budget
Previous Accumulated Expense
January
Fenruary
March
April
May
New Ending Balance

What I would like to do is get a TOTAL for all of the ammounts entered into
each column and have it appear in the Total record/row for each. Ex. All
items in the New Ending Balance Column added and placed into the Total
Record, all items for January added and entered into the Total Record...etc.
Is there a way to do this?
 
Totals aren't normally stored in your tables. You create queries and reports
to calculate totals. You are also committing spreadsheet with column names
of months.
 
What I need is sort of a "one stop shop" so to speak. I need all of this
information available in a single place. It needs to be easily usable by
people who are more used to using Excel. The "month" columns are for
expenditures that occur in each catrgory during that month. I need a total
for each month at the bottom of the column. I would prefer not to have this
data in more than one place. Like I said, I am using a form in datasheet
view for the purpose of keeping this data in one place and inserting macros
for the Totals across the rows.
 
If you can't change your table structure and want to provide a view of
details and totals, you can create a union query that combines details with
totals like:

SELECT 0 as SortField, [Current Contract Budget], [Previous Accumulated
Expense],
[January],[Fenruary],[March],[April], [May], [New Ending Balance]
From tblSpreadsheet
UNION ALL
SELECT 1, "Total", Sum([Previous Accumulated Expense]),
Sum([January]),Sum([Fenruary]),Sum([March]),Sum([April]),
Sum([May]), Sum([New Ending Balance])
FROM tblSpreadsheet;
 
I tried the Union query and it tells me "error in FROM Clause" The table
name is Monthly Report for Wish. It doesn't like the statement From
tblMonthly Report for Wish; Any idea what I am doing wrong? (Thank you
for all of the help, btw)

Duane Hookom said:
If you can't change your table structure and want to provide a view of
details and totals, you can create a union query that combines details with
totals like:

SELECT 0 as SortField, [Current Contract Budget], [Previous Accumulated
Expense],
[January],[Fenruary],[March],[April], [May], [New Ending Balance]
From tblSpreadsheet
UNION ALL
SELECT 1, "Total", Sum([Previous Accumulated Expense]),
Sum([January]),Sum([Fenruary]),Sum([March]),Sum([April]),
Sum([May]), Sum([New Ending Balance])
FROM tblSpreadsheet;


--
Duane Hookom
MS Access MVP
--

BillGrubbs said:
What I need is sort of a "one stop shop" so to speak. I need all of this
information available in a single place. It needs to be easily usable by
people who are more used to using Excel. The "month" columns are for
expenditures that occur in each catrgory during that month. I need a
total
for each month at the bottom of the column. I would prefer not to have
this
data in more than one place. Like I said, I am using a form in datasheet
view for the purpose of keeping this data in one place and inserting
macros
for the Totals across the rows.
Click to expand...
Click to expand...
 
What you are doing wrong is not providing us with the SQL view of the query
that is failing. You can see it but we can't.

--
Duane Hookom
MS Access MVP
--

BillGrubbs said:
I tried the Union query and it tells me "error in FROM Clause" The table
name is Monthly Report for Wish. It doesn't like the statement From
tblMonthly Report for Wish; Any idea what I am doing wrong? (Thank you
for all of the help, btw)

Duane Hookom said:
If you can't change your table structure and want to provide a view of
details and totals, you can create a union query that combines details
with
totals like:

SELECT 0 as SortField, [Current Contract Budget], [Previous Accumulated
Expense],
[January],[Fenruary],[March],[April], [May], [New Ending Balance]
From tblSpreadsheet
UNION ALL
SELECT 1, "Total", Sum([Previous Accumulated Expense]),
Sum([January]),Sum([Fenruary]),Sum([March]),Sum([April]),
Sum([May]), Sum([New Ending Balance])
FROM tblSpreadsheet;


--
Duane Hookom
MS Access MVP
--

BillGrubbs said:
What I need is sort of a "one stop shop" so to speak. I need all of
this
information available in a single place. It needs to be easily usable
by
people who are more used to using Excel. The "month" columns are for
expenditures that occur in each catrgory during that month. I need a
total
for each month at the bottom of the column. I would prefer not to have
this
data in more than one place. Like I said, I am using a form in
datasheet
view for the purpose of keeping this data in one place and inserting
macros
for the Totals across the rows.

:

Totals aren't normally stored in your tables. You create queries and
reports
to calculate totals. You are also committing spreadsheet with column
names
of months.

--
Duane Hookom
MS Access MVP


I have a form that I am operating ONLY in datasheet view with the
following
information:

Records/Rows

Registered Dietician
Medical Office Assistant
Workshop Materials
Workshop Incentives
Workshop Snack
Workshop Food Demo
Office Support
Telephone
FUEL
ESMM
Training
Computer
Multifunction Machine
Durable Goods
Non-reusable goods
PA Equipment
RD Travel
After school Transport
Total

and the Following Columns:
Current Contract Budget
Previous Accumulated Expense
January
Fenruary
March
April
May
New Ending Balance

What I would like to do is get a TOTAL for all of the ammounts
entered
into
each column and have it appear in the Total record/row for each.
Ex.
All
items in the New Ending Balance Column added and placed into the
Total
Record, all items for January added and entered into the Total
Record...etc.
Is there a way to do this?
Click to expand...
Click to expand...
Click to expand...
 
Sorry about that:
SELECT 0 as SortField, (Current Contract Budget), (Previous Accumulated
Expense),
(January),(February),(March),(April), (May), (New Ending Balance)
From (Monthly Report for Wish)
UNION ALL SELECT 1, "Total", Sum(Previous Accumulated Expense),
Sum(January),Sum(February),Sum(March),Sum(April),
Sum(May), Sum(New Ending Balance);

I have also tried:

SELECT 0 as SortField, [Current Contract Budget], [Previous Accumulated
Expense],
[January],[Fenruary],[March],[April], [May], [New Ending Balance]
From tblMonthly Report for Wish
UNION ALL SELECT 1, "Total", Sum([Previous Accumulated Expense]),
Sum([January]),Sum([Fenruary]),Sum([March]),Sum([April]),
Sum([May]), Sum([New Ending Balance])
FROM tblMonthly Report for Wish;


Duane Hookom said:
What you are doing wrong is not providing us with the SQL view of the query
that is failing. You can see it but we can't.

--
Duane Hookom
MS Access MVP
--

BillGrubbs said:
I tried the Union query and it tells me "error in FROM Clause" The table
name is Monthly Report for Wish. It doesn't like the statement From
tblMonthly Report for Wish; Any idea what I am doing wrong? (Thank you
for all of the help, btw)

Duane Hookom said:
If you can't change your table structure and want to provide a view of
details and totals, you can create a union query that combines details
with
totals like:

SELECT 0 as SortField, [Current Contract Budget], [Previous Accumulated
Expense],
[January],[Fenruary],[March],[April], [May], [New Ending Balance]
From tblSpreadsheet
UNION ALL
SELECT 1, "Total", Sum([Previous Accumulated Expense]),
Sum([January]),Sum([Fenruary]),Sum([March]),Sum([April]),
Sum([May]), Sum([New Ending Balance])
FROM tblSpreadsheet;


--
Duane Hookom
MS Access MVP
--

What I need is sort of a "one stop shop" so to speak. I need all of
this
information available in a single place. It needs to be easily usable
by
people who are more used to using Excel. The "month" columns are for
expenditures that occur in each catrgory during that month. I need a
total
for each month at the bottom of the column. I would prefer not to have
this
data in more than one place. Like I said, I am using a form in
datasheet
view for the purpose of keeping this data in one place and inserting
macros
for the Totals across the rows.

:

Totals aren't normally stored in your tables. You create queries and
reports
to calculate totals. You are also committing spreadsheet with column
names
of months.

--
Duane Hookom
MS Access MVP


I have a form that I am operating ONLY in datasheet view with the
following
information:

Records/Rows

Registered Dietician
Medical Office Assistant
Workshop Materials
Workshop Incentives
Workshop Snack
Workshop Food Demo
Office Support
Telephone
FUEL
ESMM
Training
Computer
Multifunction Machine
Durable Goods
Non-reusable goods
PA Equipment
RD Travel
After school Transport
Total

and the Following Columns:
Current Contract Budget
Previous Accumulated Expense
January
Fenruary
March
April
May
New Ending Balance

What I would like to do is get a TOTAL for all of the ammounts
entered
into
each column and have it appear in the Total record/row for each.
Ex.
All
items in the New Ending Balance Column added and placed into the
Total
Record, all items for January added and entered into the Total
Record...etc.
Is there a way to do this?
Click to expand...
Click to expand...
Click to expand...
 
You are experiencing the built-in penalty for having spaces in your object
names. Your second attempt placed [ ]s around field names. You must do the
same with table or query names if they contain spaces.

--
Duane Hookom
MS Access MVP
--

BillGrubbs said:
Sorry about that:
SELECT 0 as SortField, (Current Contract Budget), (Previous Accumulated
Expense),
(January),(February),(March),(April), (May), (New Ending Balance)
From (Monthly Report for Wish)
UNION ALL SELECT 1, "Total", Sum(Previous Accumulated Expense),
Sum(January),Sum(February),Sum(March),Sum(April),
Sum(May), Sum(New Ending Balance);

I have also tried:

SELECT 0 as SortField, [Current Contract Budget], [Previous Accumulated
Expense],
[January],[Fenruary],[March],[April], [May], [New Ending Balance]
From tblMonthly Report for Wish
UNION ALL SELECT 1, "Total", Sum([Previous Accumulated Expense]),
Sum([January]),Sum([Fenruary]),Sum([March]),Sum([April]),
Sum([May]), Sum([New Ending Balance])
FROM tblMonthly Report for Wish;


Duane Hookom said:
What you are doing wrong is not providing us with the SQL view of the
query
that is failing. You can see it but we can't.

--
Duane Hookom
MS Access MVP
--

BillGrubbs said:
I tried the Union query and it tells me "error in FROM Clause" The
table
name is Monthly Report for Wish. It doesn't like the statement From
tblMonthly Report for Wish; Any idea what I am doing wrong? (Thank
you
for all of the help, btw)

:

If you can't change your table structure and want to provide a view of
details and totals, you can create a union query that combines details
with
totals like:

SELECT 0 as SortField, [Current Contract Budget], [Previous
Accumulated
Expense],
[January],[Fenruary],[March],[April], [May], [New Ending Balance]
From tblSpreadsheet
UNION ALL
SELECT 1, "Total", Sum([Previous Accumulated Expense]),
Sum([January]),Sum([Fenruary]),Sum([March]),Sum([April]),
Sum([May]), Sum([New Ending Balance])
FROM tblSpreadsheet;


--
Duane Hookom
MS Access MVP
--

What I need is sort of a "one stop shop" so to speak. I need all of
this
information available in a single place. It needs to be easily
usable
by
people who are more used to using Excel. The "month" columns are
for
expenditures that occur in each catrgory during that month. I need
a
total
for each month at the bottom of the column. I would prefer not to
have
this
data in more than one place. Like I said, I am using a form in
datasheet
view for the purpose of keeping this data in one place and inserting
macros
for the Totals across the rows.

:

Totals aren't normally stored in your tables. You create queries
and
reports
to calculate totals. You are also committing spreadsheet with
column
names
of months.

--
Duane Hookom
MS Access MVP


message
I have a form that I am operating ONLY in datasheet view with the
following
information:

Records/Rows

Registered Dietician
Medical Office Assistant
Workshop Materials
Workshop Incentives
Workshop Snack
Workshop Food Demo
Office Support
Telephone
FUEL
ESMM
Training
Computer
Multifunction Machine
Durable Goods
Non-reusable goods
PA Equipment
RD Travel
After school Transport
Total

and the Following Columns:
Current Contract Budget
Previous Accumulated Expense
January
Fenruary
March
April
May
New Ending Balance

What I would like to do is get a TOTAL for all of the ammounts
entered
into
each column and have it appear in the Total record/row for each.
Ex.
All
items in the New Ending Balance Column added and placed into the
Total
Record, all items for January added and entered into the Total
Record...etc.
Is there a way to do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top