Call data to a list box depending on combo box list (1 Viewer)

[Al-Hilaly]

Dear all,

I am beginner in Access, I have an assignment so I am stuck.

I need to populate a ListBox with data from an Access Data Base, based on a ComboBox selection.
And show them in the listbox.
I need to calculate (How many students with -Exxellent, very good..etc- (giving the names and the count result)
1) In 1rs Level
2) 2nd level
3)3rd level
3) 4th level
5) In all levels -who has excellent, very good...etc in all years)

Note: I don't want to use VBA.
I hope you get what I want.

 

[GPGeorge]

This is a homework assignment for a class you are taking? If so, we can offer suggestions for you research, but it would be wrong to do it for you and cheat you out of the chance to learn by doing it.

The technique you probably want is generally known as "Cascading Combo Boxes". It works with two combo boxes, or a list box and a combo box. Search for information about it.

However, there is a bit of a complication in that you also want a calculation by group. That's going to require an aggregate query, which you'll need to construct so as to work with the filtering in the combo box. A parameterized aggregate query will most likely give you the result you need.

I hate to say it, but not using VBA would put you at a disadvantage in using Access. Please don't let that hold you back. It'll be worth the effort to learn what you need to know to use VBA effectively.

 

[Al-Hilaly]

Thank you for your answer.

"but it would be wrong to do it for you and cheat you out of the chance to learn by doing it." This is not my intention.

I send a little amount of my homework. (I have done queries, forms, and many tables.)
I send this sample to guide me to the correct way to do it without using VBA. (I know some of VBA in Excel.)

Don't give me a fish; teach me how to fish. This is my principle in my life.

Explain the way. Or where to search. I was searching for a week. I don't know exactly what to look for to help me.

Thank you again.

 

[June7]

What you describe is "cascading" or "dependent" combox/listbox and that does require a little VBA.

The combobox selects a rating level and I am not clear on how that should impact what the listbox shows. You say you want listbox to show calculation of how many students at each level, not one selected level.

Structure of StERatings is not normalized and filtering by level and/or aggregating data will not be simple. Either need to change table structure or use a UNION query to rearrange data to normalized structure and that query will be source for filtering/aggregating.

You need to revisit lessons on "normalization".

The query you currently show in Listbox RowSource is nonsense.

 

[Al-Hilaly]

Thank you for your advice. I will research again, as you said, and I will find the answer.

Appreciate your sincere answer.

 

[June7]

A normalized table structure would look like:

ID	StRecID	RatingRecID	RateLevel
1	1	1	1
2	1	3	2
3	1	4	3
4	1	2	4
5	2	5	1
6	2	1	2
7	2	2	3
8	2	3	4

Note this is saving as foreign keys the primary key values from Stdata and Ratings that would be created with an autonumber type.

A CROSSTAB query can join tables to pull descriptive info and do a count with grouping on Rating or RateLevel, result like:

RatingLevel	Acceptable	Excellent	Good	VeryGood	Weak	Total
1	2	2				4
2		3	1			4
3		1	3			4
4			2	1	1	4

However, showing names that comprise each count WILL REQUIRE VBA.

 

[Al-Hilaly]

Thank you June 7 for your answer.
Could you please tell me how to do it?
Names aren't necessary.

 

[June7]

How to do what - build a table? Or build CROSSTAB query? Suggest you look for tutorial on CROSSTAB.

 

[mike60smart]

Thank you for your advice. I will research again, as you said, and I will find the answer.

Appreciate your sincere answer.

Hi

Your Relationships window should look something like the attached.

This allows you to create a Main Form based on People with a Subform based on Levels and Ratings.

 

[Al-Hilaly]

mike60smart I like the ideas and the design. Thank you.​

I need the steps to start with that design. As I said, I am a beginner in Access.​

 

[June7]

I need the steps to start with that design.

Didn't we already establish that this is an assignment from a formal course?
Steps to start a db design should have been day 1.
Mike has given you the design, now just build the tables.