Prev: HELP: Compare Column values with column names in different tables
Next: Updating an unbound table
From: Douglas J. Steele on 25 May 2010 15:41
No offense, but your Grades_Table isn't properly designed. It should be

Student_Id
Course_Id
Grade

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

"sam" <sam(a)discussions.microsoft.com> wrote in message
news:6E347A1E-2B2C-463B-BF6F-AEAE07644622(a)microsoft.com...
>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


From: sam on 25 May 2010 16:47
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" wrote:

> No offense, but your Grades_Table isn't properly designed. It should be
>
> Student_Id
> Course_Id
> Grade
>
> --
> Doug Steele, Microsoft Access MVP
> http://www.AccessMVP.com/DJSteele
> (no e-mails, please!)
>
> "sam" <sam(a)discussions.microsoft.com> wrote in message
> news:6E347A1E-2B2C-463B-BF6F-AEAE07644622(a)microsoft.com...
> >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
>
>
> .
>
From: Douglas J. Steele on 26 May 2010 08:46
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.

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

"sam" <sam(a)discussions.microsoft.com> wrote in message
news:B1195FE8-EA69-4875-875F-531595CC913C(a)microsoft.com...
> 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" wrote:
>
>> No offense, but your Grades_Table isn't properly designed. It should be
>>
>> Student_Id
>> Course_Id
>> Grade
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://www.AccessMVP.com/DJSteele
>> (no e-mails, please!)
>>
>> "sam" <sam(a)discussions.microsoft.com> wrote in message
>> news:6E347A1E-2B2C-463B-BF6F-AEAE07644622(a)microsoft.com...
>> >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
>>
>>
>> .
>>


From: BruceM via AccessMonster.com on 26 May 2010 12:55
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.

Douglas J. Steele wrote:
>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]
>>>
>>> .

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1

From: Douglas J. Steele on 26 May 2010 14:56
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!)

"BruceM via AccessMonster.com" <u54429(a)uwe> wrote in message
news:a896e742fd790(a)uwe...
> 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.
>
> Douglas J. Steele wrote:
>>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]
>>>>
>>>> .
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
>


 | 
Pages: 1
Prev: HELP: Compare Column values with column names in different tables
Next: Updating an unbound table