Totals and sub-totals for data retrieved from Access db

[Cheryl]

How do I make sub-totalling work for retrieving information from an Access
database. I have frontpage 2003. I can make the totalling work (not using
wizard) for a timesheet based on the week number and employee name. But, each
job needs to be sub-totalled so that that information can be input in other
locations.. At least until the intranet application is complete. It will have
to be displayed in a printer friendly format for each employee and put in
their records so the totals/subtotals need to show.

I can't make the sum totals work using the wizard because I get an error no
matter what permeatation I used... so I coded it the long way around based on
a search form. I don't know how to get subtotals based on the information
selected. Just a grand total.

Could someone point me to an example.. please.. if possible... ?

 

[Kathleen Anderson [MVP - FrontPage]]

Subtotals using the FrontPage Database Results Wizard
http://home.att.net/~codelibrary/FrontPage/subquery.htm

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

 

[Cheryl]

Does this also apply to Grand Totals as well? I got it to work.. for one
subtotal.. but I actually need to sub-total 2 columns.. and add them
together.... And one final total... Thanks for the direction for the one
total...

 

[Cheryl]

Thanks a lot for your help... Think I am getting it now...

 

[Cheryl]

How would I differentiate the Grand Total.. since it should be at the bottom
and not repeating on every sub-total line?

SELECT format([NumberofHours],'#0.00') as FormatHrs,
format([NumberofRandDHours],'#0.00') as FormatRDHrs, format((Select
Sum(NumberofHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date),'#0.00') as TotalHours, (Select
Count(NumberofHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date = EmployeesTimeSheets.Date) as
TotalCount, format((Select Sum(NumberofRandDHours) from EmployeesTimeSheets
as Results_1 where FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date),'#0.00') as TotalRandDHours, (Select
Count(NumberofRandDHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date = EmployeesTimeSheets.Date) as
TotalRDCount, format((Select Sum(NumberofHours+NumberofRandDHours) from
EmployeesTimeSheets as Results_1 where FullName='::FullName::' and
Results_1.Date = EmployeesTimeSheets.Date),'#0.00') as TotalDayHours,
format((Select Sum(NumberofHours+NumberofRandDHours) from EmployeesTimeSheets
as Results_1 where FullName='::FullName::' and
Results_1.DateRangeStartWeekDate =
EmployeesTimeSheets.DateRangeStartWeekDate),'#0.00') as GrandTotalHours ,*
FROM EmployeesTimeSheets WHERE (FullName = '::FullName::' AND
DateRangeStartWeekDate = ':ateRangeStartWeekDate::') ORDER BY FullName
ASC,DateRangeStartWeekDate ASC,Date ASC,JobNumberID ASC,PartNumber ASC

This seems to be on the right track... except for the final total...

 

[Cheryl]

Nevernind.. got it

How would I differentiate the Grand Total.. since it should be at the bottom
and not repeating on every sub-total line?

SELECT format([NumberofHours],'#0.00') as FormatHrs,
format([NumberofRandDHours],'#0.00') as FormatRDHrs, format((Select
Sum(NumberofHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date),'#0.00') as TotalHours, (Select
Count(NumberofHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date = EmployeesTimeSheets.Date) as
TotalCount, format((Select Sum(NumberofRandDHours) from EmployeesTimeSheets
as Results_1 where FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date),'#0.00') as TotalRandDHours, (Select
Count(NumberofRandDHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date = EmployeesTimeSheets.Date) as
TotalRDCount, format((Select Sum(NumberofHours+NumberofRandDHours) from
EmployeesTimeSheets as Results_1 where FullName='::FullName::' and
Results_1.Date = EmployeesTimeSheets.Date),'#0.00') as TotalDayHours,
format((Select Sum(NumberofHours+NumberofRandDHours) from EmployeesTimeSheets
as Results_1 where FullName='::FullName::' and
Results_1.DateRangeStartWeekDate =
EmployeesTimeSheets.DateRangeStartWeekDate),'#0.00') as GrandTotalHours ,*
FROM EmployeesTimeSheets WHERE (FullName = '::FullName::' AND
DateRangeStartWeekDate = ':ateRangeStartWeekDate::') ORDER BY FullName
ASC,DateRangeStartWeekDate ASC,Date ASC,JobNumberID ASC,PartNumber ASC

This seems to be on the right track... except for the final total...

Subtotals using the FrontPage Database Results Wizard
http://home.att.net/~codelibrary/FrontPage/subquery.htm

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

 

[Kathleen Anderson [MVP - FrontPage]]

I had a feeling you would

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

Nevernind.. got it

How would I differentiate the Grand Total.. since it should be at
the bottom and not repeating on every sub-total line?

SELECT format([NumberofHours],'#0.00') as FormatHrs,
format([NumberofRandDHours],'#0.00') as FormatRDHrs, format((Select
Sum(NumberofHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date),'#0.00') as TotalHours, (Select
Count(NumberofHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date) as TotalCount, format((Select
Sum(NumberofRandDHours) from EmployeesTimeSheets as Results_1 where
FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date),'#0.00') as TotalRandDHours, (Select
Count(NumberofRandDHours) from EmployeesTimeSheets as Results_1
where FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date) as TotalRDCount, format((Select
Sum(NumberofHours+NumberofRandDHours) from EmployeesTimeSheets as
Results_1 where FullName='::FullName::' and Results_1.Date =
EmployeesTimeSheets.Date),'#0.00') as TotalDayHours, format((Select
Sum(NumberofHours+NumberofRandDHours) from EmployeesTimeSheets as
Results_1 where FullName='::FullName::' and
Results_1.DateRangeStartWeekDate =
EmployeesTimeSheets.DateRangeStartWeekDate),'#0.00') as
GrandTotalHours ,* FROM EmployeesTimeSheets WHERE (FullName =
'::FullName::' AND DateRangeStartWeekDate =
':ateRangeStartWeekDate::') ORDER BY FullName
ASC,DateRangeStartWeekDate ASC,Date ASC,JobNumberID ASC,PartNumber
ASC

This seems to be on the right track... except for the final total...

Subtotals using the FrontPage Database Results Wizard
http://home.att.net/~codelibrary/FrontPage/subquery.htm

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/



How do I make sub-totalling work for retrieving information from an
Access database. I have frontpage 2003. I can make the totalling
work (not using wizard) for a timesheet based on the week number
and
employee name. But, each job needs to be sub-totalled so that that
information can be input in other locations.. At least until the
intranet application is complete. It will have to be displayed in a
printer friendly format for each employee and put in their records
so the totals/subtotals need to show.

I can't make the sum totals work using the wizard because I get an
error no matter what permeatation I used... so I coded it the long
way around based on a search form. I don't know how to get
subtotals based on the information selected. Just a grand total.

Could someone point me to an example.. please.. if possible... ?