HELP: Compare Column values with column names in different tables

[sam]

I have two tables, Grades_Table and Course_Table

In Grades_Table, I have the following columns:

Student_ID
English
Math
Physics
Chemistry
Biology


In Course_Table, I have the following columns:

Course_ID
Course_Name


Now, I want to right a combination of SQL query and VBA to search specific
students

For E.g.:

I want to view students who has a grade of “B†in Physics, “B†in Math and
“B†in Chemistry

How can I do this?? How can I grab a course from “Course_Name†column, from
“Course_Table†then locate that course in Grades_Table (Here the course names
are the column names)?

Thanks in advance

 

[Douglas J. Steele]

No offense, but your Grades_Table isn't properly designed. It should be

Student_Id
Course_Id
Grade

 

[sam]

Hi Douglas,

Yes, I know the table is not properly designed, But as of now this is the
table structure. Is there a way to compare the column values with column
names?

 

[Douglas J. Steele]

Create a query that normalizes your data:

SELECT Student_ID, Course_ID, English AS Grade
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "English"
UNION
SELECT Student_ID, Course_ID, Math
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "Math"
UNION
SELECT Student_ID, Course_ID, Physics
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "Physics"
UNION
SELECT Student_ID, Course_ID, Chemistry
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "Chemistry"
UNION
SELECT Student_ID, Course_ID, Biology
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "Biology"

Use that query rather than Grades_Table.

Any other approach would require you either need to generate the SQL for
your query dynamically, or else build extremely complicated SQL statements
that may generate "too complex" error messages.

 

[Douglas J. Steele]

Union queries get their field names from the first subquery in the query.
That means all the grades will be aliased Grade.

There's no harm in including the As Grade in all of the subqueries if it
makes you feel more comfortable. <g>

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Just lurking in at effort to learn new stuff. Did you intend to use Grade
as
the alias for English, Math, etc., or just for English as written? If the
latter, I don't understand.

Create a query that normalizes your data:

SELECT Student_ID, Course_ID, English AS Grade
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "English"
UNION
SELECT Student_ID, Course_ID, Math
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "Math"
UNION
SELECT Student_ID, Course_ID, Physics
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "Physics"
UNION
SELECT Student_ID, Course_ID, Chemistry
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "Chemistry"
UNION
SELECT Student_ID, Course_ID, Biology
FROM Grades_Table, Course_Table
WHERE Course_Table.Course_Name = "Biology"

Use that query rather than Grades_Table.

Any other approach would require you either need to generate the SQL for
your query dynamically, or else build extremely complicated SQL statements
that may generate "too complex" error messages.

Hi Douglas,

[quoted text clipped - 43 lines]

.