SQL query to select eligible students

[Alex_738]

Hello,

I have large database with students' names, courses they took, and grades. If all will be working okay, this database should be updated every quarter.
I need to select students for specific certificate. For example, for one certificate, I need to select every student who took course 125, course 126, and course 134, and have grades >=2.0 or "P".
I would like to create SQL query to pull needed information from the table. I already have started and can do SQL query to select students with grades >=2.0 or "P". However, I do not know how to combine all needed criteria into one query.

So, I need:
1. Select students who took specific classes for the particular certificate (for example, for one certificate, in could be: course 125, course 126, and course 134). Please note that I have many different certificates with different requrements
2. Grades should be equal or greater than 2.0 or "P"
3. Somehow mark eligible students
4. And next time (when new classes are added to the database) do not select students who already got certificate.

If it is possible please help me to create such query or suggest me how to do this assignment in better way (maybe using macro or Visual Basic?)

Thank you very much.

Here is example of the database

YRQ	DEPT DIV	CRS NUM	DEC GR	GR	NAME	SID	STREET	CITY	ST	ZIP	DAY PHONE	STU-EMAIL-ADDR
A893	Course	125	2.8		Name 111	111111111	Street 1	City 1
A894	Course	126	0.7		Name 111	111111111	Street 1	City 1
A902	Course	134	2.4		Name 111	111111111	Street 1	City 1
A903	Course	135	4.0		Name 111	111111111	Street 1	City 1
A904	Course	136	0.0	Z	Name 111	111111111	Street 1	City 1
A892	Course	124	3.0		Name 111	111111111	Street 1	City 1
A904	Course	146	0.0		Name 22222	222222222	Street 2	City 2
A892	Course	144	4.0		Name 22222	222222222	Street 2	City 2
A902	Course	144	3.6		Name 22222	222222222	Street 2	City 2
A893	Course	145	3.7		Name 22222	222222222	Street 2	City 2
A903	Course	145	4.0		Name 22222	222222222	Street 2	City 2
A894	Course	146	3.7		Name 22222	222222222	Street 2	City 2
B234	Course	125	3.0		Name 3333333	333333333	Street 3	City 3
A904	Course	126	3.6		Name 3333333	333333333	Street 3	City 3
B234	Course	126	1.1		Name 3333333	333333333	Street 3	City 3
B012	Course	134	2.0		Name 3333333	333333333	Street 3	City 3
B013	Course	135	2.7		Name 3333333	333333333	Street 3	City 3
B014	Course	136	4.0		Name 3333333	333333333	Street 3	City 3
B122	Course	144	3.1		Name 3333333	333333333	Street 3	City 3
B123	Course	145	2.8		Name 3333333	333333333	Street 3	City 3
B124	Course	146	3.7		Name 3333333	333333333	Street 3	City 3
B232	Course	154	0.0	P	Name 3333333	333333333	Street 3	City 3
B233	Course	155	4.0		Name 3333333	333333333	Street 3	City 3
A903	Course	157	2.6		Name 3333333	333333333	Street 3	City 3

[lfpm062010]

See if this helps.
Database_.accdb

[Alex_738]

Thank you for the response and the help,

It works, but it does only part of what I need. It selects classes needed. But is there a way to select only students with whole set of classes? It means students who took course 125 AND course 126 AND course 134. If students did not take whole set of requirements, do not select students. Also, it would be good to sort by NAME==>DEPT DIV==>CRS NUM.

Moreover, is there a way to mark already found eligible students that in the next time when I will add more classes to the database, they will not be found again (will not be repeated)? After each quarter, I need to add more classes students have taken. So, how to look only for students names that will be "currently" added?

If you have any suggestions, it would be good to hear from you.

[lfpm062010]

This is one way of doing it.

Programming in VBA will give you what you need. This is what I think will work (not tested).

1) SELECT unique student identifier.
2) Loop through the unique student, and check to see if each student have take the set of requirements. That is, check to see if each students have taken course 125, 126, and 134 (or more) and pass each course.
3) Get the student information along with course information for each students have set of requirements.

[Alex_738]

Thank you,

However, I do not know how to write in VBA. If it is possible, could you please do it for me? (I think, it could be time consuming)

If yes, additional information may be required:

1. I could have large database with classes from all previous quarters.
2. I have to download classes for current quarter, add them to the database, and check students only who took classes in current quarter
3. Also, I have spreadsheets with eligible students who already got certificates, so maybe we can pull information from them about students who already got certificate and do not duplicate certificates for those students.
4. I need to do same work after every quarter is ended.

[lfpm062010]

I would highly recommend learning Visual Basic for anyone that uses Access or Excel.

I have put ths code in and it seem to work.
Database_.accdb

[Alex_738]

Thank you very much for your time and help!

I will try to figure out how it works.