old timer needs help with summed totals

[Guest]

hi all

i moved my preamble to the bottom so you don't have to read it if you are
short on time.

here is one problem i cannot fix in my app.

table: volunteers
fields: name, on line, in office, out of office, bingo, workshop, supervision.
data collected is number of hours.

i have the monthly query and report done but the annual is giving me a
headache.
for the annual i have to create a total of all the fields in a separate
column.

annual query:
SELECT DISTINCTROW volunteers.[volunteer names], Sum(volunteers.[On line])
AS [Sum Of On line], Sum(volunteers.[In office]) AS [Sum Of In office],
Sum(volunteers.[Out office]) AS [Sum Of Out office], Sum(volunteers.Bingo) AS
[Sum Of Bingo], Sum(volunteers.Workshop) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super]
FROM volunteers
GROUP BY volunteers.[volunteer names], volunteers.Date
HAVING (((volunteers.Date) Between [enter beginning date ( like: apr 2005)]
And [enter ending date ( like: mar 2005)]));

annual report:
report header: title and pic
page header: column labels
volunteer names header: blank
detail (moved right up against header above): sum of online, sum of in
office, etc...
detail not visible
volunteer names footer: volunteer names, =sum([sum of on line]), =sum([sum
of in office]), etc...
page footer: current date and page number
report footer: total (label), =sum([sum of on line]), =sum([sum of in
office]), etc...

this gives a nice report with each volunteer having 1 line and all their
hours summed in each column.
now at the end (to the right) of each row i would like to add a new column
that adds all the previous columns so a get a total hour tally per line of
volunteer.
and to top it off in the report footer a grand overall total for all hours
served.
..................

i made and used 3 sets of dbase apps with paradox, dos version, well over 10
years ago.
as a volunteer i have maintained and ran them.
but as i am getting on in years and 'old timers' is getting the better of me
i want my local crisis centre to take it over from me and have the secretary
do the inputting and reports.
so i am porting it over to access. almost done but by using the wizards
only, just too old to start learning coding and sql stuff, i just can't
finish it up the way i want it.

hope i am not asking for too much.
appreciated,
nick

 

[John Vinson]

now at the end (to the right) of each row i would like to add a new column
that adds all the previous columns so a get a total hour tally per line of
volunteer.

Add a field to the Query - and, for better efficiency, change the
HAVING to WHERE:

SELECT DISTINCTROW volunteers.[volunteer names], Sum(volunteers.[On
line])
AS [Sum Of On line], Sum(volunteers.[In office]) AS [Sum Of In
office],
Sum(volunteers.[Out office]) AS [Sum Of Out office],
Sum(volunteers.Bingo) AS
[Sum Of Bingo], Sum(volunteers.Workshop) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super],
Sum(NZ([On Line]) + NZ([In Office]) + NZ([Out Office]) + NZ([Bingo]) +
NZ([Workshop]) + NZ([0#1#super])) AS TotalTime
FROM volunteers
GROUP BY volunteers.[volunteer names], volunteers.Date
WHERE (((volunteers.[Date]) Between [enter beginning date ( like: apr
2005)]
And [enter ending date ( like: mar 2005)]));

This will give you a new field TotalTime which you can bind to a
textbox on your report; in the footer, you can then add a textbox with
a control source

=Sum([TotalTime])

to get your grand total.

John W. Vinson[MVP]

 

[Guest]

thanks john

pasted your new query but got this error msg:
syntax error (missing operator) in expression 'volunteers.Date WHERE
(((volunteers.[Date] Between..... etc.

so i substituted HAVING for WHERE and all went as advertised.
can you tell me the difference between the two and what the significance is
of your NZ designation?

regards
nick

 

[John Vinson]

thanks john

pasted your new query but got this error msg:
syntax error (missing operator) in expression 'volunteers.Date WHERE
(((volunteers.[Date] Between..... etc.

Hm. Could you post the actual SQL you're using?

so i substituted HAVING for WHERE and all went as advertised.
can you tell me the difference between the two and what the significance is
of your NZ designation?

WHERE selects the records to be processed first, so that all of the
Sum, Count, etc. operations get done only on valid records.

HAVING sums, counts, etc. all the records in the table, and then
discards the composite records which don't pass the criterion.

HAVING is useful if you want to (say) display only groups with a
certain number of records or a certain range of sums - if your
criterion is on a table field, WHERE is more efficient (often far more
efficient if you're looking at a small subset of a large table).

NZ() returns a zero if the field is NULL. If you don't use NZ when
adding up a bunch of fields (as in your example), the entire sum will
be NULL if any one of the fields has no data. Anything plus NULL is
NULL - NZ() converts the Null to a Zero so the sum will work.

John W. Vinson[MVP]

 

[Guest]

thanks for the explaniations.

this one works fine but with WHERE it does not.

SELECT DISTINCTROW volunteers.[volunteer names], Sum(volunteers.[On line])
AS [Sum Of On line], Sum(volunteers.[In office]) AS [Sum Of In office],
Sum(volunteers.[Out office]) AS [Sum Of Out office], Sum(volunteers.Bingo) AS
[Sum Of Bingo], Sum(volunteers.Workshop) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super]
FROM volunteers
GROUP BY volunteers.[volunteer names], volunteers.Date
HAVING (((volunteers.Date) Between [enter beginning date ( like: jan 2005)]
And [enter ending date ( like: mar 2005)]));

thanks john

pasted your new query but got this error msg:
syntax error (missing operator) in expression 'volunteers.Date WHERE
(((volunteers.[Date] Between..... etc.

Hm. Could you post the actual SQL you're using?

so i substituted HAVING for WHERE and all went as advertised.
can you tell me the difference between the two and what the significance is
of your NZ designation?

WHERE selects the records to be processed first, so that all of the
Sum, Count, etc. operations get done only on valid records.

HAVING sums, counts, etc. all the records in the table, and then
discards the composite records which don't pass the criterion.

HAVING is useful if you want to (say) display only groups with a
certain number of records or a certain range of sums - if your
criterion is on a table field, WHERE is more efficient (often far more
efficient if you're looking at a small subset of a large table).

NZ() returns a zero if the field is NULL. If you don't use NZ when
adding up a bunch of fields (as in your example), the entire sum will
be NULL if any one of the fields has no data. Anything plus NULL is
NULL - NZ() converts the Null to a Zero so the sum will work.

John W. Vinson[MVP]

 

[John Vinson]

thanks for the explaniations.

this one works fine but with WHERE it does not.

SELECT DISTINCTROW volunteers.[volunteer names], Sum(volunteers.[On line])
AS [Sum Of On line], Sum(volunteers.[In office]) AS [Sum Of In office],
Sum(volunteers.[Out office]) AS [Sum Of Out office], Sum(volunteers.Bingo) AS
[Sum Of Bingo], Sum(volunteers.Workshop) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super]
FROM volunteers
GROUP BY volunteers.[volunteer names], volunteers.Date
HAVING (((volunteers.Date) Between [enter beginning date ( like: jan 2005)]
And [enter ending date ( like: mar 2005)]));

It's very likely that this is due to the use of the reserved word Date
as a fieldname - or it might be misinterpreting the date string that
you enter. You also don't want to group by the date field if it's just
being used as a criterion. Try

PARAMETERS [enter beginning date ( like: jan 2005)] DateTime, [enter
ending date ( like: mar 2005)] DateTime;
SELECT DISTINCTROW volunteers.[volunteer names], Sum(volunteers.[On
line])
AS [Sum Of On line], Sum(volunteers.[In office]) AS [Sum Of In
office],
Sum(volunteers.[Out office]) AS [Sum Of Out office],
Sum(volunteers.Bingo) AS
[Sum Of Bingo], Sum(volunteers.Workshop) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super]
FROM volunteers
GROUP BY volunteers.[volunteer names]
WHERE (((volunteers.[Date]) Between [enter beginning date ( like: jan
2005)]
And [enter ending date ( like: mar 2005)]));


John W. Vinson[MVP]

 

[Guest]

SELECT DISTINCTROW volunteers.[volunteer names], Sum(volunteers.[On line]) AS
[Sum Of On line], Sum(volunteers.[In office]) AS [Sum Of In office],
Sum(volunteers.[Out office]) AS [Sum Of Out office], Sum(volunteers.[Bingo])
AS [Sum Of Bingo], Sum(volunteers.[Workshop]) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super], Sum(NZ([On Line]) + NZ([In
Office]) + NZ([Out Office]) + NZ([Bingo]) + NZ([Workshop]) + NZ([O#l#super]))
AS TotalTime
FROM volunteers
GROUP BY volunteers.[volunteer names], volunteers.Date
HAVING (((volunteers.Date) Between [enter beginning date ( like: jan 2005)]
And [enter ending date ( like: mar 2005)]));


this is the only way it will work, with or without your PARAMETERS statement.

so i will leave well enough alone and finish this dbase.
thanks very much for your help so far.

regards
nick

 

[John Spencer]

Mr Vinson,
I think you misplaced the WHERE clause. I believe it has to occur before
the GROUP BY clause.

PARAMETERS [enter beginning date ( like: jan 2005)] DateTime,
[enter ending date ( like: mar 2005)] DateTime;
SELECT DISTINCTROW volunteers.[volunteer names],
Sum(volunteers.[On line])
AS [Sum Of On line],
Sum(volunteers.[In office]) AS [Sum Of In office],
Sum(volunteers.[Out office]) AS [Sum Of Out office],
Sum(volunteers.Bingo) AS [Sum Of Bingo],
Sum(volunteers.Workshop) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super]
FROM volunteers
WHERE (((volunteers.[Date]) Between [enter beginning date ( like: jan 2005)]
And [enter ending date ( like: mar 2005)]))
GROUP BY volunteers.[volunteer names]

thanks for the explaniations.

this one works fine but with WHERE it does not.

SELECT DISTINCTROW volunteers.[volunteer names], Sum(volunteers.[On line])
AS [Sum Of On line], Sum(volunteers.[In office]) AS [Sum Of In office],
Sum(volunteers.[Out office]) AS [Sum Of Out office], Sum(volunteers.Bingo)
AS
[Sum Of Bingo], Sum(volunteers.Workshop) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super]
FROM volunteers
GROUP BY volunteers.[volunteer names], volunteers.Date
HAVING (((volunteers.Date) Between [enter beginning date ( like: jan
2005)]
And [enter ending date ( like: mar 2005)]));

It's very likely that this is due to the use of the reserved word Date
as a fieldname - or it might be misinterpreting the date string that
you enter. You also don't want to group by the date field if it's just
being used as a criterion. Try

PARAMETERS [enter beginning date ( like: jan 2005)] DateTime, [enter
ending date ( like: mar 2005)] DateTime;
SELECT DISTINCTROW volunteers.[volunteer names], Sum(volunteers.[On
line])
AS [Sum Of On line], Sum(volunteers.[In office]) AS [Sum Of In
office],
Sum(volunteers.[Out office]) AS [Sum Of Out office],
Sum(volunteers.Bingo) AS
[Sum Of Bingo], Sum(volunteers.Workshop) AS [Sum Of Workshop],
Sum(volunteers.[O#l#super]) AS [Sum Of O#l#super]
FROM volunteers
GROUP BY volunteers.[volunteer names]
WHERE (((volunteers.[Date]) Between [enter beginning date ( like: jan
2005)]
And [enter ending date ( like: mar 2005)]));


John W. Vinson[MVP]

 

[John Vinson]

Mr Vinson,
I think you misplaced the WHERE clause. I believe it has to occur before
the GROUP BY clause.

Thanks John! You're exactly correct.

John W. Vinson[MVP]