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 want to create a form where there is a drop down box that contains all of the level 1s. When the user choices, lets say, Math, a report opens up and displays all of the items that have "Math" as their level 1 classification.

Is this possible?

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

Thanks a lot

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[dhookom]

Could you share some table structures and data?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[infinitx]

Sure

ItemsTable

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


StatsTable

Item ID (Number)
Difficulty (Number)


ICL Table

Item ID (Number)
ICL 1(Text)
ICL 2(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 Drop-Down Box will display values from the ICL Table's ICL 1 Field Name.

Here's what I see happening:

The user chooses a level 1 from the drop down box. The prorgam then searches through the ItemsTable to find the matching Level 1 Item Classification and then creates a report that displays the matching items' Item Text, Item Key, ICL 1, ICL 2, and Difficulty in a report. I also want it to display one item per page.

Is this possible?

Thanks!

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[dhookom]

I'm still a bit lost. Your first posting contains references to 3 levels and your tables show 2 (I guess).

I don't know if you have questions regarding the filtering a report or creating a report or getting the combo boxes to behave in a particular way.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[infinitx]

Sorry,

I forgot about the Level 3.

So it should be:

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)

ICL Table

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

I first need to create a report and then filter it according to what Level 1 the user has chosen from the drop-down box.

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[dhookom]

Creating a report with more than one "one to many" relationship generally requires at least one subreport. This would be similar to a form/subform setup.

I'm not sure why you need two tables with Item ID, ICL 1, ICL 2, and ICL 3. Are these storing duplicate values? Whenever I see numbers like this in field names, I question the structure. It doesn't seem normalized to me.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[infinitx]

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.

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[infinitx]

The report won't have more than one "one-to-many" relationship because the ICL Table will not be included in the report as its only purpose is for the user to choose which Level 1 Classification they want.

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[dhookom]

To create a report, first combine your tables into a query and save it. Then create a new report based on the query. Then set the sorting and grouping as you desire, add the text boxes and other controls, and then save.

Are you having trouble with any of this?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[infinitx]

Duane,

I am having a problem filtering the records so that only the ones with specific Item Classifications appear in the report.

How do you filter a Query to show only the records with the Item Classification that the user has selected from the drop-down menu?


----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[dhookom]

If you have a combo box on a form that you want to use to filter a report, you can set the criteria under the appropriate query column to
Forms!frmYourForm!cboYourCombo

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[infinitx]

Duane,

I will give that a shot! One more question though, how would I filter the StatisticTable to only display statistics for the items with the specific Item Classification?

Thanks!

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[dhookom]

Do you know anything about queries? I really don't know where to start explaining and don't have the time to start at the beginning.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.