Newbie needs quarterly reports with percentages

[PAWelch]

I am a novice with Access, building only basic reports, no programming background. I must create a report that totals the number of 5 different grades that teachers dole out and what percentage of the quarterly total does each grade receive. It needs to look like this:

Qtr 1 A 25 13%
B 84 44%
C 65 34%
D 17 9%
F 2 1%

Qtr 2 A 16 10%
B 77 47%
C 55 33%
D 12 7%
F 5 3%

When I try this in Report Wizard, I get monthly, not knowing how to change to Quarterly and the percentages were based on all months totaled.

Can someone please assist me? Humble thanks. (I don't know how you guys learn all this...mind boggling.)

 

[dhookom]

We don't have a clue how your tables are structured. You mentioned the report needs to be grouped by quarter. Do you have a field in a table the stores the quarter?

Do all teachers give out all five grades each quarter? If not, do the five grades still need to appear?

Is this report grouped by teacher and quarter?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PAWelch]

The data is stored by month. Table structure:
Month (mm/yyyy)
StudentID
ProjectID
GradeAssigned


It is possible that not all grades will be used.
The count of grades assigned needs to be done quarterly. Individual teachers are not to be in the report. ProjectID's are not in the report.

I built the first report from a query rather than the table. I couldn't figure out how to get the quarterly totals and the percentages done in that. That seemed to make the most sense to do it in a query rather than in the report, that there would be a calculation that could do the percentages by quarterly rather than as whole total. However, I cannot find info in my book on proper procedure. (Ignorance is not bliss.)

Thank you.

 

[dhookom]

Assuming the Month (not a good name) field is a date/time and your quarters match the definition of accepted quarters...

You could create a totals query like:

SELECT DatePart("Q",[Month]) as Qtr, GradeAssigned, Count(StudentID) as NumOf
FROM [structure]
GROUP BY DatePart("Q",[Month]),GradeAssigned;

Then in your report set the sorting and grouping to Qtr (display the header) and GradeAssigned.

Add a text box to the Qtr Header
Name: txtQtrCount
Control Source: =Count(*)
Visible: No
Add a text box for the percent:
Control Source: =NumOf/txtQtrCount

If your Month field isn't a date and your quarters don't match the general definition of quarters, then you need to provide more information so that we can provide a suggested solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PAWelch]

Thank you very much. I will give this a shot!
Do you have a book you would recommend for learning Access 2003?

 

[dhookom]

I like John Viesca's books

http://www.viescas.com/Info/books.htm.

His "Building Applications..." book takes 4 complete applications that he has created and gives them to you on CD. The book explains each and describes how to modify them for your needs. You might even find a familiar name as an instructor in one of the applications ;-).

The Access Developers handbooks are generally consider very good for serious developers

http://www.developershandbook.com/.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SkipVought]

Hi,

"Month (mm/yyyy)"

mm/yyyy is NOT a REAL DATE, althought you can enter a REAL DATE and format to DISPLAY mm/yyyy.

But if you are STORING this as TEXT (mm/yyyy), it will forever be a pain.

As Dwayne stated, "Assuming the Month (not a good name) field is a date/time ..." be absolutely certain that you are string this value as a REAL DATE

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[helpthanks]

Format([Month],"qq")

again, assuming the "MONTH" field is a date/time field type.

this format changes the date to show quarters in a two digit numeric format.

 

[dhookom]

helpthanks,
You might want to try your expression before suggesting it to others. "qq" will return values like 11, 22, 33, or 44.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PAWelch]

The actual name of the field in my table is "EvalDate". I put "Month" as a more generic term and the mm/yyyy was just to let you know how it was formatted. Sorry for the confusion. I will be more accurate next time. Thank you all.

 

[SkipVought]

EvalDate is much more descriptive than MONTH or mm/yyyy.

Formatting mean absolutely NOTHING to the data.

Formatting changes NOTHING.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]