I hope this helps. . . .
Glad you are still helping. I know my field names aren't the best but,
this
was just a test database to see if I could get everything to work, then I
was
going to build the real database. My table is set up with the following
fields: Names, Block 1, Block 2, Block 3, Training Managers Course,
Scenario
Training, Homework Review, Lesson Plan block 1, Lesson Plan block 2,
Lesson
Plan block 3, Lesson Plan training managers course, Time, Date, Test
Review,
Test Administration, and Counseling. All of the fields except time and
date
are linked to tables of the same name to use as a lookup or pull down
menu.
I thought this best in case we make any changes to the lessons we teach or
add instructors. I then built a form to use for inputs.
I wanted to build an overview report first. To show the total amount of
time an instructor has taught. So it will just sum up different lessons
and
merge them into larger groups. I built my first query to sum up all the
time
of classroom instruction which is all of Block 1, Block 2, Block 3 and
Training Managers Course from my table. The query was designed with the
following fields from my main table: Names, Time, Block 1, Block 2, Block
3
and Training Managers Course.
Field: Names Time Block 1 Block 2 Block 3 Training
Managers
Table: Main Table """" """" """" """" """"
Total: Group By Sum Where Where Where Where
Criteria: [Who] Between 1 And 9
Or: Between 1 And 11
Or: Between 1 And 4
Or: Between 1 And 10
This Query gives me exactly what I was looking for.
I then built similar queries to total other fields I wanted on my
overview report: Scenario Totals, Homework Review Totals, Lesson Plan
Totals,
Test Review Total, Test Administration Total, and Counseling totals. To
finish of my overview report I created 3 additional queries to sum the
totals
of the Classroom instruction totals, Scenario totals, and Homework Review
Totals into one field. Lesson Plan totals, into another field. Then
finally
to sum up Test Review Total, Test Administration totals, and Counseling
totals into a 3rd field. So my overview report looks something like this
Classroom Instruction ______199.4
Scenario _________________23.5 Classroom Total __225.15
Homework Review_________2.25
Lesson Plan_______________19.25 Lesson Plan total__19.25
Test Review ______________4.75
Test Administration_________5.75 Administration Total__11.75
Counseling________________1.25
Now, what I am trying to do is to build separate reports for each "Total"
to
break down more into each area that makes up the total. List out the
lesson
and the total for each lesson. So, a report for Classroom Total would
look
like this.
Block 1
C2 Terminology/C2 Systems ___________________2.5
AMC Mission_______________________________4.50
AMC Stucture_______________________________6.50
Block 2
Mission Numbers____________________________4.60
Purpose Codes______________________________3.75
Ground Times______________________________6.00
Block 3
GDSS 2___________________________________22.15
M3C_____________________________________10.50
Training Managers Course
Problem Solving___________________________4.5
Cert/Decert________________________________2.0
To start this report I thought the easiest way would be to create a query
to
total the amount of time for each lesson. Then just label it on my report.
Then put all the queries on a single report. For instance the query for
the
total for block 1, lesson 2 (AMC Mission) Looks like this
Field: Names Block 1 Time
Table: Main Table Main Table Main Table
Total: Group By Group By Sum
Criteria: [Who] 2
This is where it all went downhill for me. If I run this query using my
name (Dave) it comes back with
Names Block 1 SumOfTime
Dave AMC Mission 5
This is what I wanted. However, if I run the query with the name Kevin,
it
comes back with nothing. Looks like this.
Names Block 1 SumOfTime
The query works, Kevin has not taught Block 1, lesson 2 (AMC Mission) yet.
If this query is run on my report though, none of the queries will return
information even if he has taught a lesson meeting their criteria. What I
would like to do, if possible, is that if a query runs and nothing matches
the criteria, to show up on the report and look like this:
Block 1
C2 Terminology/C2 Systems ___________________2.5
AMC Mission_______________________________Has not yet taught.
AMC Stucture_______________________________6.50
Block 2
Mission Numbers____________________________4.60
Purpose Codes______________________________3.75
Ground Times______________________________6.00
Block 3
GDSS 2___________________________________22.15
M3C_____________________________________10.50
Training Managers Course
Problem Solving___________________________4.5
Cert/Decert________________________________2.0
I'm sure once I build my experience level that there will probably be a
much
easier way to do all of this however, this is all I know at the moment.
Thanks again, I can't say it enough
Dave
Duane Hookom said:
Please don't email me directly. If we get too stuck, I might consider
this
later.
Describe your table structures and data. A few sample records are very
helpful. Then tell us how you want your queries and/or reports to appear.
