Find a SQL statement ... about school management ...

height001

3 student tables ...
A basic information form (student number, name, remarks), a curriculum (course number, course name, credits,) a transcript (student number, name, course name, grades, credits)
The requirement is to manage the repeating students. If the score is less than 60, the corresponding credits will be suspended ... If the corresponding credits are enough, the grades will be repeated ... The remarks are those that have been repeated and those who are suspended will not be repeated ...
There were no credits in the original transcript ... I added it for the sake of not even having a transcript ... if not necessary, the best ...
The environment is VB6.0 .... ACCESS do database ...
Little brother beginner database ... is in the process of designing the curriculum ... to be due tomorrow ... just a sentence ... it originally had a report ... but it was abandoned at the beginning ... Help ...
I don't know if the explanation is white ... wait online ... while adding ...

height001

Student ID Name Course Name Results Credits
0309040214 Lan Genshun Data Structure 0 6
0309040214 Lan Genshun Database 0 4
0309040214 Langenshun Smart Instrument 0 6
0309040214 Lan Genshun Solid State Physics 0 5
0309040216 Zhang Lei Data Structure 83 6
0309040216 Zhang Lei Database 93 4
0309040216 Zhang Lei Intelligent Instrument 74 6
0309040216 Zhang Lei Solid State Physics 84 5
0303140101 Li Qiang Data Structure 75 6
0303140101 Li Qiang Database 85 4
0303140101 Li Qiang Intelligent Instrument 95 6
0303140101 Li Qiang Solid Physics 65 5
0303140102 Li Yong Data Structure 54 6
0303140102 Li Yong Database 50 4
0303140102 Li Yong Smart Instrument 53 6
0303140102 Li Yong Solid State Physics 35 5
0303140103 Zhou Jie Data Structure 77 6
0303140103 Zhou Jie Database 87 4
0303140103 Zhou Jie Intelligent Instrument 97 6
0303140103 Zhou Jie Solid Physics 67 5
-------------------------------------------------- -
Course number Course name Credits Course type Course description
Ranch
1 Advanced Mathematics 6 Compulsory Public Courses 1
2 Signal system 5 Compulsory communication system 1
3 Communication principle 6 Compulsory communication system 1
4 College English 5 Compulsory Common Courses 1
5 Data Structure 6 Compulsory Computer Department 2
6 Database 4 Compulsory Computer Department 2
7 Intelligent instruments 6 Compulsory electronics 2
8 Solid State Physics 5 Compulsory Electronics 2
9 Storage Technology 4 Compulsory Electronics 3
--------------------------------------------------
Student name Name Gender Birth year Class Department Remarks
0309040217 Du Jianwen Male 1984-9-20 03090402 Computer suspension
0303140101 Li Qiang Male 1984-10-20 03031401 Leader, Department of Electronics
0303140102 Li Yong Male 1999-11-11 03031401 Department of Electronics
0303140103 Zhou Jie Female 1984-12-12 03031401 Department of Electronics Repetition
0309040216 Zhang Lei Male 1985-5-15 03090402 Computer
0309040218 Yellow River Male 1983-3-20 03090402 Computer
0309040220 Li Wenjiang Male 1984-9-22 03090402 Computer

lyz1984321

select Basic Information Table. Student ID, Basic Information Table. Name, Temporary. Total Credits from
(select sum (credits) as total credits, student number from transcript where results <60 group by student number) temporary, basic information table
where Temporary. Student ID = Basic Information Form. Student ID and Basic Information Form. Remarks <> 'Retired' and Basic Information Form. Remarks <> 'Study Off' and Provisional.

lyz1984321

Temporary total credits> highest credit

height001

Private Sub Command2_Click ()

Adodc1.RecordSource = "select stu. Student number, stu. Name, grade. Total credits from (select sum (credits) as total credits, student number from grade where grade <60 group by student number) grade, stu where grade .Student ID = stu.Student ID and stu. Remarks <> 'Retired' and stu. Remarks <> 'Study off' and grade. Total credits> highest quasi credits "

Adodc1.Refresh
End Sub

Is it written like this ...? But there is an error ... say that at least one parameter is not specified ...
What's wrong ...
I don't know which table for the time being ... the first table is grade, the second is course, and the third is stu ...

height001

I forgot to change the final highest quasi credits to specific values ​​...
But I didn't report an error ... but I couldn't find out that Li Yong should be relegated ...
What happened ...-

lyz1984321

"Temporary" is (select sum (credits) as total credits, student number from grade where grade <60 group by student number) query result set, not a real table, you already have a table named grade, You can use another name instead, such as t_grade, otherwise there may be problems

Adodc1.RecordSource = "select stu. student number, stu. name, t_grade. total credits from (select sum (credit) as total credits, student number from grade where grade <60 group by student number) t_grade, stu where t_grade .Student ID=stu.Student ID and stu.Remarks<>'Has been graded' and stu.Remarks<>'Suspension' and t_grade.Total credits>Highest quasi credits"