Add All Items to Report With Specific Classifications

[infinitx]

Hi,

I have a database that contains test items. These items are then grouped by classification.

Here is a sample of the classifications:

Level 1: Math

Level 2: Addition Level 3: Simple, Hard

Level 2: Division Level 3: Simple, Hard

I created a form where there is a drop down box that contains all of the level 1s. When the user chooses, lets say, Math, a report opens up and displays all of the items that have "Math" as their level 1 classification.

The Item Classification is located in a table that has the rest of the item information such as Item Text.

Here's more information:

ItemsTable

Item ID (Primary Key)(Autonumber)
Item Text (Memo)
Item Key (Text)
ICL 1(Item Classification Level 1)(Text)
ICL 2(Text)
ICL 3(Text)


StatsTable

Item ID (Number)
Difficulty (Number)


ICL Table

Item ID (Number)
ICL 1(Text)
ICL 2(Text)
ICL 3(Text)

The ItemsTable and the StatsTable are linked with a one-to-many relationship. The ItemsTable and the ICLTable are linked with a one-to-many relationship as well.

The ICL Table stores all of the Item Classifications that the user has entered before entering any items. When the user adds an item, they select the item classifications from drop-down boxes and add them to the ItemsTable ICL Fields.

The report has one "one-to-many" relationship. (I have a subreport for this)

The Drop-Down Box displays values from the ICL Table's ICL 1 Field Name.

I have created a query that has the following criteria for the ICL 1 Field:
Forms!frmYourForm!cboYourCombo (This is used to filter the records that have the Item Classification that the user has selected from the drop down box.)

Here's my problem:

How would I filter the StatsTable (which is the subreport) to only display statistics for the items with the specific Item Classification that the user has selected from the drop down box?

Thanks a lot!

Alex

 

[WayneRyan]

Alex,

It's late Friday, and I probably didn't read your post too clearly.

The query for your subreport should be something like:

Select ItemID, Difficulty
From   StatsTable
Where  ItemID In (
   Select ItemID
   From   ItemsTable
   Where  ItemKey = Forms!frmYourForm!cboYourCombo)

You can also use Master-Child links to control your subreports.

Wayne