Access Query

[bob8000]

Hello everyone.

This is the query I have

"List all students, by name and number, who took more than 2 modules in
semester 1 of 2000"

I need to know how do you determine if a student as done 2 or more
modules. Or more then 1 if you put it another way.

Any SQL code examples or advice would be good

Thanks

BOB8000

 

[Guest]

Old boy it rather difficult to tell without knowing your talbe structure. Do
you have the module information in the same table as the student?
How's about posting your table arrangement and an example of your data. Say
about 5-6 records old chap.

 

[bob8000]

Hello Karl

I have the following tables shown with their attributes

student - firstName, lastName, studentNo, studyYear

module - moduleNo, moduleTitle, leaderNo, year, semester

assignment - assignmentNo, assignmentTitle, moduleNo, LocationNo,
Hand_in_date

recipt - reciptNo, officerInitial, dateReceived, weekNo, assignmentNo

location - locationNo, locationName

module leader - leaderNo, firstName, lastName

award - awardCode, awardTitle, awardLevel

studying - studyNo, moduleNo, studentNo


sample Student table data:

StudentNo / FirstName / Last_Name / StudyYear
11111111 Jenifer Aldridge 2000
22222222 Peggy Wolley 2000


sample module table data:

ModuleNo / ModuleTitle / LeaderNo / Year / Semester
2 Java Peas 1 2000 1
3 Bonzai 3 2000 1
4 GDP 4 2000 2

hope you can help

bob8000

 

[Guest]

This will do it I think.
SELECT student.studentNo, student.firstName, student.lastName
FROM (student LEFT JOIN studying ON student.studentNo = studying.studentNo)
LEFT JOIN [module] ON studying.moduleNo = module.moduleNo
WHERE (((module.year)="2000") AND ((module.semester)="1"))
GROUP BY student.studentNo, student.firstName, student.lastName
HAVING (((Count(studying.moduleNo))>1));

I recommend some changes to your table structure.
The studyYear in the student table should not be used as you would need to
re-enter the same information each year for every student. The year in the
module table has the needed year information.

 

[bob8000]

Thanks Karl

That worked spot on.

And thank you for the advise on the table structure, I see what you
mean by it.

I will change that bit straight away.


BOB8000

 

[bob8000]

Can some one help me with subqueries in MySQL

I have done the same query in access and it works fine.

the first access query:

SELECT Student.First_Name, Student.Last_Name,
Assignment.Assignment_Title
FROM Student INNER JOIN (([Module] INNER JOIN Assignment ON
Module.Module_No = Assignment.Module_No) INNER JOIN Studying ON
Module.Module_No = Studying.Module_No) ON Student.Student_No =
Studying.Student_No
WHERE (((Student.First_Name)="bathsheba"));



the second access query:

SELECT DISTINCT Query9a.Assignment_Title, Student.First_Name,
Student.Last_Name, Assignment.Module_No
FROM Student INNER JOIN (Studying INNER JOIN (Query9a INNER JOIN
Assignment ON Query9a.Assignment_Title = Assignment.Assignment_Title)
ON Studying.Module_No = Assignment.Module_No) ON Student.Student_No =
Studying.Student_No
WHERE (((Assignment.Module_No)=6 Or (Assignment.Module_No)=4 Or
(Assignment.Module_No)=7));



and the final query which uses the first two queries:


SELECT DISTINCT Query9b.First_Name, Query9b.Last_Name
FROM Query9a INNER JOIN Query9b ON Query9a.Assignment_Title =
Query9b.Assignment_Title
ORDER BY Query9b.Last_Name;



so how would this transfer to MySQL


BOB8000

 

[John Spencer]

Is query9a your first query and query9b your second query?

To move this over to MySQL, I think that you need to remove the brackets []
and change the quote marks to apostrophes (" to ') and remove the semi-colon
at the end.

I don't understand what you are trying to accomplish, but if you want to use
the first and second query as subqueries in the third, the method that I
sometimes use is to take your last query and paste the other queries in the
appropriate places.

So starting with
SELECT DISTINCT Query9b.First_Name, Query9b.Last_Name
FROM (<Paste query 9A here>) as Query9a
INNER JOIN (<Paste Query9B Here>) as Query9b
ON Query9a.Assignment_Title = Query9b.Assignment_Title
ORDER BY Query9b.Last_Name

SInce you also use query9A in query 9B you would need to paste it into 9B
using the same technique. Having no way to test this and not really knowing
MySQL that well, this is all I can suggest.