cross-table or something alike

[Jean-Paul De Winter]

Hi,
Sorry for the difficult way I explain what I need but.... it's the best
I can......

I work in a school for students with character problems.
To evaluate their behaviour a system of points is create, there are a
total of 10 different items.
Things that can go wrong "cost" one or two points: fighting cost 2,
smoking 1...
Every other month the points are summed.
When a techers sees somebody smoking in a forbidden area he gives the
student a "smoke-point"
Every 2 months each student gets a report.
On this report a sum is made for every item.

Say I have this recordset:

Student1 datewith month1 smoking 1
Student1 datewith month2 smoking 1
Student2 datewith month1 fighting 1
Student2 datewith month1 fighting 1
Student2 datewith month1 not comming to school 2

This should give me the report for

Student1
Month 1

Smoking fighting not comming to school
1

Student1
Month 2

Smoking fighting not comming to school
1

Student2
Month 1

Smoking fighting not comming to school
2 2

How to create such a report?

Thanks
JP

 

[Duane Hookom]

Create a crosstab query with
Student as Row Heading
Format(DateField,"yyyymm") as Row Heading
CharacterProblem as Column Heading
Sum([CostField]) as the Value

 

[Jean-Paul De Winter]

I haven't alreeady found the time to test things out but...
We have a total of 10 different items (CharacterProblem)
The report should print all 10 items even if the student doesn't have
any bad point for this item....
I think in your approach only the items with a score will be printed (or
am I wrong?)

Any idea?
Thanks
JP


Duane Hookom schreef:

 

[Duane Hookom]

Enter all 10 items into the Column Headings property of your crosstab query.

I haven't alreeady found the time to test things out but...
We have a total of 10 different items (CharacterProblem)
The report should print all 10 items even if the student doesn't have any
bad point for this item....
I think in your approach only the items with a score will be printed (or
am I wrong?)

Any idea?
Thanks
JP


Duane Hookom schreef:

Create a crosstab query with
Student as Row Heading
Format(DateField,"yyyymm") as Row Heading
CharacterProblem as Column Heading
Sum([CostField]) as the Value

 

[Jean-Paul De Winter]

Hi,
Thanks for your replies to my messages... as most of us, I badly need help.
Ths suggestions you gave work just fine, but the final result isn't
exactly what I need (but close)
now I can create a report per student which is OK but i get a new page
per month.
the final result should look like:


TL PL Ma Be Ta ................... Totals(1) Totals(2)
September
October
November
December
January
February
March
April
May
June

Totals(3)

Also,
Totals(1) should sum all values for 1 month
Totals(2) should sum all values per 2 months (sept+oct; nov+dec)
Totals(3) should sum all values per column heading

can this be done?

Thanks
JP

Duane Hookom schreef:

Enter all 10 items into the Column Headings property of your crosstab query.

I haven't alreeady found the time to test things out but...
We have a total of 10 different items (CharacterProblem)
The report should print all 10 items even if the student doesn't have any
bad point for this item....
I think in your approach only the items with a score will be printed (or
am I wrong?)

Any idea?
Thanks
JP


Duane Hookom schreef:

Create a crosstab query with
Student as Row Heading
Format(DateField,"yyyymm") as Row Heading
CharacterProblem as Column Heading
Sum([CostField]) as the Value

 

[Duane Hookom]

I don't understand how you want your report to display. I don't see any
numbers below and don't have a clue what TL, PL, Ma... are.

--
Duane Hookom
MS Access MVP

Hi,
Thanks for your replies to my messages... as most of us, I badly need
help.
Ths suggestions you gave work just fine, but the final result isn't
exactly what I need (but close)
now I can create a report per student which is OK but i get a new page per
month.
the final result should look like:


TL PL Ma Be Ta ................... Totals(1) Totals(2)
September
October
November
December
January
February
March
April
May
June

Totals(3)

Also,
Totals(1) should sum all values for 1 month
Totals(2) should sum all values per 2 months (sept+oct; nov+dec)
Totals(3) should sum all values per column heading

can this be done?

Thanks
JP

Duane Hookom schreef:

Enter all 10 items into the Column Headings property of your crosstab
query.

I haven't alreeady found the time to test things out but...
We have a total of 10 different items (CharacterProblem)
The report should print all 10 items even if the student doesn't have
any bad point for this item....
I think in your approach only the items with a score will be printed (or
am I wrong?)

Any idea?
Thanks
JP


Duane Hookom schreef:
Create a crosstab query with
Student as Row Heading
Format(DateField,"yyyymm") as Row Heading
CharacterProblem as Column Heading
Sum([CostField]) as the Value

 

[Jean-Paul De Winter]

TL, PL, Ma... are, waht you called "Characterproblems"... we use 10
different types

I didn't tpe the whole table but it should look like:

T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
September 1 5 1 1 8
October 4 1 5 13
November 1 1 2
December 1 1 3
January 1 1
February 0 1
March 7 1 2 1 11
April 1 1 12
May 1 1
June 0 0

14 6 1 5 1 3 29


Hope I explained it better now!
Thanks again
JP, Belgium


Duane Hookom schreef:

I don't understand how you want your report to display. I don't see any

numbers below and don't have a clue what TL, PL, Ma... are.

-- Duane Hookom MS Access MVP "Jean-Paul De Winter"

Hi,
Thanks for your replies to my messages... as most of us, I badly need
help.
Ths suggestions you gave work just fine, but the final result isn't
exactly what I need (but close)
now I can create a report per student which is OK but i get a new page per
month.
the final result should look like:


TL PL Ma Be Ta ................... Totals(1) Totals(2)
September
October
November
December
January
February
March
April
May
June

Totals(3)

Also,
Totals(1) should sum all values for 1 month
Totals(2) should sum all values per 2 months (sept+oct; nov+dec)
Totals(3) should sum all values per column heading

can this be done?

Thanks
JP

Duane Hookom schreef:

Enter all 10 items into the Column Headings property of your crosstab
query.

I haven't alreeady found the time to test things out but...
We have a total of 10 different items (CharacterProblem)
The report should print all 10 items even if the student doesn't have
any bad point for this item....
I think in your approach only the items with a score will be printed (or
am I wrong?)

Any idea?
Thanks
JP


Duane Hookom schreef:
Create a crosstab query with
Student as Row Heading
Format(DateField,"yyyymm") as Row Heading
CharacterProblem as Column Heading
Sum([CostField]) as the Value

 

[Duane Hookom]

The crosstab suggestion I have you should create the values you need in your
report. I'm not sure what the issue is since "now I can create a report per
student which is OK but i get a new page per month" doesn't make much sense
to me. You shouldn't be getting a new page per month unless you have
something defined to do this in your report.

--
Duane Hookom
MS Access MVP

TL, PL, Ma... are, waht you called "Characterproblems"... we use 10
different types

I didn't tpe the whole table but it should look like:

T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
September 1 5 1 1 8
October 4 1 5 13
November 1 1 2
December 1 1 3
January 1 1
February 0 1
March 7 1 2 1 11
April 1 1 12
May 1 1
June 0 0

14 6 1 5 1 3 29


Hope I explained it better now!
Thanks again
JP, Belgium


Duane Hookom schreef:

I don't understand how you want your report to display. I don't see any

numbers below and don't have a clue what TL, PL, Ma... are.

-- Duane Hookom MS Access MVP "Jean-Paul De Winter"

Hi,
Thanks for your replies to my messages... as most of us, I badly need
help.
Ths suggestions you gave work just fine, but the final result isn't
exactly what I need (but close)
now I can create a report per student which is OK but i get a new page
per month.
the final result should look like:


TL PL Ma Be Ta ................... Totals(1) Totals(2)
September
October
November
December
January
February
March
April
May
June

Totals(3)

Also,
Totals(1) should sum all values for 1 month
Totals(2) should sum all values per 2 months (sept+oct; nov+dec)
Totals(3) should sum all values per column heading

can this be done?

Thanks
JP

Duane Hookom schreef:
Enter all 10 items into the Column Headings property of your crosstab
query.

I haven't alreeady found the time to test things out but...
We have a total of 10 different items (CharacterProblem)
The report should print all 10 items even if the student doesn't
have any bad point for this item....
I think in your approach only the items with a score will be
printed (or am I wrong?)

Any idea?
Thanks
JP


Duane Hookom schreef:
Create a crosstab query with
Student as Row Heading
Format(DateField,"yyyymm") as Row Heading
CharacterProblem as Column Heading
Sum([CostField]) as the Value

 

[Jean-Paul De Winter]

Too bad but I can't try things out right now.... but maybe you could
help with some other questions about this crosstab...

How do I get the name of the months in the first column? All months
should be printed even if there are no values for it?
How to calculate per column or per row, and how to sum per 2 months?

I probably did something in the report settings so I get a new page
every month... I'll check it out

Thanks for your kind help
JP, Belgium

The crosstab suggestion I have you should create the values you need in >your
report. I'm not sure what the issue is since "now I can create a report >per
student which is OK but i get a new page per month" doesn't make much
sense
to me. You shouldn't be getting a new page per month unless you have
something defined to do this in your report.

-- Duane Hookom MS Access MVP "Jean-Paul De Winter"

TL, PL, Ma... are, waht you called "Characterproblems"... we use 10
different types

I didn't tpe the whole table but it should look like:

T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
September 1 5 1 1 8
October 4 1 5 13
November 1 1 2
December 1 1 3
January 1 1
February 0 1
March 7 1 2 1 11
April 1 1 12
May 1 1
June 0 0

14 6 1 5 1 3 29


Hope I explained it better now!
Thanks again
JP, Belgium


Duane Hookom schreef:

I don't understand how you want your report to display. I don't

see any
numbers below and don't have a clue what TL, PL, Ma... are.

-- Duane Hookom MS Access MVP "Jean-Paul De Winter"

Hi,
Thanks for your replies to my messages... as most of us, I badly need
help.
Ths suggestions you gave work just fine, but the final result isn't
exactly what I need (but close)
now I can create a report per student which is OK but i get a new page
per month.
the final result should look like:


TL PL Ma Be Ta ................... Totals(1) Totals(2)
September
October
November
December
January
February
March
April
May
June

Totals(3)

Also,
Totals(1) should sum all values for 1 month
Totals(2) should sum all values per 2 months (sept+oct; nov+dec)
Totals(3) should sum all values per column heading

can this be done?

Thanks
JP

Duane Hookom schreef:
Enter all 10 items into the Column Headings property of your crosstab
query.

I haven't alreeady found the time to test things out but...
We have a total of 10 different items (CharacterProblem)
The report should print all 10 items even if the student doesn't
have any bad point for this item....
I think in your approach only the items with a score will be
printed (or am I wrong?)

Any idea?
Thanks
JP


Duane Hookom schreef:
Create a crosstab query with
Student as Row Heading
Format(DateField,"yyyymm") as Row Heading
CharacterProblem as Column Heading
Sum([CostField]) as the Value

 

[Duane Hookom]

To get all the months displayed as "row headings" you need to outer join
your query to a table that contains all of the months.
I would always use the Month number to do this and then format the month
number as a month name in your report.

Calculating is generally done in your report. A crosstab can have additional
Row Heading columns that calculate sums etc.

What is the reason for "sum per 2 months"? Which 2 months? The most recent 2
months or what?


--
Duane Hookom
MS Access MVP

Too bad but I can't try things out right now.... but maybe you could help
with some other questions about this crosstab...

How do I get the name of the months in the first column? All months should
be printed even if there are no values for it?
How to calculate per column or per row, and how to sum per 2 months?

I probably did something in the report settings so I get a new page every
month... I'll check it out

Thanks for your kind help
JP, Belgium

The crosstab suggestion I have you should create the values you need in >your
report. I'm not sure what the issue is since "now I can create a report >per
student which is OK but i get a new page per month" doesn't make much
sense
to me. You shouldn't be getting a new page per month unless you have
something defined to do this in your report.

-- Duane Hookom MS Access MVP "Jean-Paul De Winter"

TL, PL, Ma... are, waht you called "Characterproblems"... we use 10
different types

I didn't tpe the whole table but it should look like:

T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
September 1 5 1 1 8
October 4 1 5 13
November 1 1 2
December 1 1 3
January 1 1
February 0 1
March 7 1 2 1 11
April 1 1 12
May 1 1
June 0 0

14 6 1 5 1 3 29


Hope I explained it better now!
Thanks again
JP, Belgium


Duane Hookom schreef:
I don't understand how you want your report to display. I don't see any
numbers below and don't have a clue what TL, PL, Ma... are.

-- Duane Hookom MS Access MVP "Jean-Paul De Winter"
Hi,
Thanks for your replies to my messages... as most of us, I badly need
help.
Ths suggestions you gave work just fine, but the final result isn't
exactly what I need (but close)
now I can create a report per student which is OK but i get a new page
per month.
the final result should look like:


TL PL Ma Be Ta ................... Totals(1) Totals(2)
September
October
November
December
January
February
March
April
May
June

Totals(3)

Also,
Totals(1) should sum all values for 1 month
Totals(2) should sum all values per 2 months (sept+oct; nov+dec)
Totals(3) should sum all values per column heading

can this be done?

Thanks
JP

Duane Hookom schreef:
Enter all 10 items into the Column Headings property of your crosstab
query.

I haven't alreeady found the time to test things out but...
We have a total of 10 different items (CharacterProblem)
The report should print all 10 items even if the student doesn't
have any bad point for this item....
I think in your approach only the items with a score will be
printed (or am I wrong?)

Any idea?
Thanks
JP


Duane Hookom schreef:
Create a crosstab query with
Student as Row Heading
Format(DateField,"yyyymm") as Row Heading
CharacterProblem as Column Heading
Sum([CostField]) as the Value