Report Help Needed

[lspelman]

I want to ask a question that I'm not quite sure how to even ask and don't want to come across as stupid but I am stuck and need help figuring this out

I have five fields:

BMListening
BMSpeaking


BMReading
BMWriting
BMOverall

Within each of those fields a level of 1 - 4 can be used

They are wanting a report that will show them how many times each of the levels are used for each of the fields. They would like this shown by Month (which I have figured that part out ), by Language Spoken and Ethnic Origin. I'm not sure if there is a way that I can show all of this on one report (sub-reports which I haven't used) or if they have to be individual reports.

I have been trying to create this report based on a query using groupings and count totals but it isn't working.

I just viewed a crosstab query video and not sure how I can make that work yet that is what I think I am trying to do or least to have the information displayed that way

I'm not sure how to make this work and still learning Access so any help would be much appreciated!

Thank you

[June7]

This report output would be easier with table structure like:

ID (foreign key link to a parent info table)
Category (BMListening, BMSpeaking, BMReading, BMWriting, BMOverall)
Value (1-4)

Then you could do joins in queries to retrieve related data and use report Grouping&Sorting with summary calcs.

As is will need a UNION query to get the data in vertical arrangement (the normalized structure I describe above). There is no wizard or designer for UNION query, must typed in the SQL view window of designer.

SELECT "BMListening" As Category, BMListening As [Value], Language, datefieldname FROM tablename
UNION SELECT "BMSpeaking", BMSpeaking, LanguageSpoken, EthnicOrigin, datefieldname FROM tablename
UNION SELECT "BMReading", BMReading, LanguageSpoken, EthnicOrigin, datefieldname FROM tablename
UNION SELECT "BMWriting", BMWriting, LanguageSpoken, EthnicOrigin, datefieldname FROM tablename
UNION SELECT "BMOverall", BMOverall, LanguageSpoken, EthnicOrigin, datefieldname FROM tablename;

Now use this query as the basis for a report Recordsource and use report Grouping&Sorting with summary calcs.

[lspelman]

Thank you for your quick reply and the information . . .

So are you saying that eventhough there are 5 Benchmarks completed for each student I should break the Benchmarks out into a separate table and make a one to many relationship?

I wondered if I had them set up wrong after watching the crosstab query example, but was thinking that because they are separate pieces of information collected for each student I should have them as individual fields similiar to how they are on the form (paper) the consultant completes.

I'm unclear how one would enter/display this information on the form in access for data entry.

Sorry if this is a simple question I'm just not quite getting it yet.

[June7]

No, I am suggesting normalized design is one table with a Category field having 5 choices. Otherwise, go with the UNION.