Register Totals

[Neil via AccessMonster.com]

I have a register which records the attendance details for students in the
format
name- surname-week-mon-tue-wed-thu-fri
and i would like to display them in a report with the totals for each week in
4 week slotslike

name-surname- week1- week2-week3-week4

any ideas

 

[Duane Hookom]

Consider normalizing your table so that you don't have fields with days of
the week as names. Dates should be values stored in fields, not field names.

 

[Neil via AccessMonster.com]

the days are yes/no fields to say wheather they are in that day of the week
and there is a new record for each week.

i have it to count the number of mondays, tuesdays etc

if i calculate it by week i get the format

name- surname- week- total
Some One 30/08/05 4
Some One 27/08/05 3

and i want

name- surname 30/08/05 27/08/05 total
Some One 4 3
7

 

[Duane Hookom]

Create a crosstab query with Name and SurName as the row headings, Week as
the column heading, and Abs(Mon+Tue+Wed+Thu+Fri) as the Value.

 

[Neil via AccessMonster.com]

Thanks for ur advice Duane

i have created a crosstab query which has the desired function but when i use
this query in a report i get

cannot use the crosstab of a non-fixed column as a subquery

i get this when i try to change the record source when the report is opened,
any ideas

thanks Neil

Create a crosstab query with Name and SurName as the row headings, Week as
the column heading, and Abs(Mon+Tue+Wed+Thu+Fri) as the Value.

the days are yes/no fields to say wheather they are in that day of the
week

[quoted text clipped - 17 lines]

 

[Duane Hookom]

Why/how are you changing the record source of the report? Consider "fixing"
the column headings by using relative dates rather than changing dates.

If you want more information, come back with
-the name of your open form
-the SQL of your crosstab query
-the number of columns you expect to display

--
Duane Hookom
MS Access MVP
--

Thanks for ur advice Duane

i have created a crosstab query which has the desired function but when i
use
this query in a report i get

cannot use the crosstab of a non-fixed column as a subquery

i get this when i try to change the record source when the report is
opened,
any ideas

thanks Neil

Create a crosstab query with Name and SurName as the row headings, Week as
the column heading, and Abs(Mon+Tue+Wed+Thu+Fri) as the Value.

the days are yes/no fields to say wheather they are in that day of the
week

[quoted text clipped - 17 lines]

the week as names. Dates should be values stored in fields, not field
names.

 

[Neil via AccessMonster.com]

the name of the report is called invoice and this is the sql i am using and i
am hoping to use 7 columns
Firstname, surname, week1, week2, week3, week4, total
and i wish to open this invoice depending on the dates selected in a form

TRANSFORM Abs(Sum(MondaySet))+Abs(Sum(TuesdaySet))+Abs(Sum(WednesdaySet))+Abs
(Sum(ThursdaySet))+Abs(Sum(FridaySet)) AS [Week Total]
SELECT Student.StudentID, Student.FirstName, Student.Surname, School.SchoolID,
School.Name, Student.Billing, BillingUnionQry.Address, BillingUnionQry.
PostCode, BillingUnionQry.PhoneNo, BillingUnionQry.Fax, Abs(Sum([MondaySet]))
+Abs(Sum([TuesdaySet]))+Abs(Sum([WednesdaySet]))+Abs(Sum([ThursdaySet]))+Abs
(Sum([FridaySet])) AS Total
FROM Attendance, School, Centre, Student, BillingUnionQry
WHERE (((Attendance.StudentID)=[Student].[StudentID]) AND ((Student.Centre)=
[Centre].[CentreID]) AND ((Student.School)=[School].[SchoolID]) AND ((Student.
Billing)=[BillingUnionQry].[Name]))
GROUP BY Student.StudentID, Student.FirstName, Student.Surname, School.
SchoolID, School.Name, Student.Billing, BillingUnionQry.Address,
BillingUnionQry.PostCode, BillingUnionQry.PhoneNo, BillingUnionQry.Fax,
School.Address, School.PostCode, School.PhoneNo, School.Fax, Student.
StudentID
PIVOT Format([Week],'Short Date');

Why/how are you changing the record source of the report? Consider "fixing"
the column headings by using relative dates rather than changing dates.

If you want more information, come back with
-the name of your open form
-the SQL of your crosstab query
-the number of columns you expect to display

Thanks for ur advice Duane

[quoted text clipped - 18 lines]

 

[Duane Hookom]

Assuming a form named "frmDates" with a text box named "txtEndDate".
Change the Column Headings expression from:
Format([Week],'Short Date')
to:
"Week" & DateDiff("ww", [Week], Forms!frmDates!txtEndDate + 7)

Then open the Query->Parameters and enter:
Forms!frmDates!txtEndDate Date/Time

Set the query's Column Headings property to:
Column Headings: "Week1","Week2","Week3","Week4"

This should create columns where Week1 is the results from the week entered
into the text box. Week4 would be 3 weeks earlier.

--
Duane Hookom
MS Access MVP

the name of the report is called invoice and this is the sql i am using
and i
am hoping to use 7 columns
Firstname, surname, week1, week2, week3, week4, total
and i wish to open this invoice depending on the dates selected in a form

TRANSFORM
Abs(Sum(MondaySet))+Abs(Sum(TuesdaySet))+Abs(Sum(WednesdaySet))+Abs
(Sum(ThursdaySet))+Abs(Sum(FridaySet)) AS [Week Total]
SELECT Student.StudentID, Student.FirstName, Student.Surname,
School.SchoolID,
School.Name, Student.Billing, BillingUnionQry.Address, BillingUnionQry.
PostCode, BillingUnionQry.PhoneNo, BillingUnionQry.Fax,
Abs(Sum([MondaySet]))
+Abs(Sum([TuesdaySet]))+Abs(Sum([WednesdaySet]))+Abs(Sum([ThursdaySet]))+Abs
(Sum([FridaySet])) AS Total
FROM Attendance, School, Centre, Student, BillingUnionQry
WHERE (((Attendance.StudentID)=[Student].[StudentID]) AND
((Student.Centre)=
[Centre].[CentreID]) AND ((Student.School)=[School].[SchoolID]) AND
((Student.
Billing)=[BillingUnionQry].[Name]))
GROUP BY Student.StudentID, Student.FirstName, Student.Surname, School.
SchoolID, School.Name, Student.Billing, BillingUnionQry.Address,
BillingUnionQry.PostCode, BillingUnionQry.PhoneNo, BillingUnionQry.Fax,
School.Address, School.PostCode, School.PhoneNo, School.Fax, Student.
StudentID
PIVOT Format([Week],'Short Date');

Why/how are you changing the record source of the report? Consider
"fixing"
the column headings by using relative dates rather than changing dates.

If you want more information, come back with
-the name of your open form
-the SQL of your crosstab query
-the number of columns you expect to display

Thanks for ur advice Duane

[quoted text clipped - 18 lines]

the week as names. Dates should be values stored in fields, not field
names.