SQL code, need help

  • Thread starter Thread starter Nike
  • Start date Start date
N

Nike

I have 3 tabels that I am trying to get information from
one of them. Here is what I am trying to do.

tblClass
ID
Class

tblScheduleClass
Id
Class I have it bound to 1 but display the name
etc

tblStudentsAndClasses
ID
ClassID
SSAN


This form is from the student side of the DB. The class
maybe assigned to the student from the instructos side of
the DB.
First the student selects an item from a combox which is
from a union SQL (ClassID, Class). The reason I have a
union is because they can sign in for other reasons than
just for a class (this is for each visit). From this
selection I want the student to see if they are already
assigned to this class for that day. It sounds rediculous,
but believe me the way I have it set up is needed this way.

The code I am trying to use is as follows, but it does not
seem to work can someone provide some assistance with this?

vClass = "select * from tblStudentsAndClasses " & "WHERE
classid =" & tblScheduleClass.Class = Me!REASONLIST.Column
(0)

Thanks a bunch
 
The code I am trying to use is as follows, but it does not
seem to work can someone provide some assistance with this?

vClass = "select * from tblStudentsAndClasses " & "WHERE
classid =" & tblScheduleClass.Class = Me!REASONLIST.Column
(0)
Click to expand...

It looks like you're uncertain whether you're searching for ClassID or
for tblScheduleClasses.Class. If the combo Reasonlist contains a
ClassID, just search for it:

vClass = "select * from tblStudentsAndClasses WHERE classid =" _
& Me!REASONLIST.Column(0)
 
It sounds that easy but it isn't. Maybe I didn't say it
right.
I have 5 tables (actually more but this states my case)
tblCustomer
LAST4SSAN these three are my primary keys
FName
LNAME

tblClass
ID
Class and other reasons

tblScheduleClass
id
ClassID
Date
Time
etc.

tblStudentsAndClasses
ID
ScheduleClassID
LAST4SSAN/FNAME/LNAME

tblVisits
ID
LAST4SSAN/FNAME/LNAME
etc. other info related to the visit

Now on my for I have a combo box. When they select an
item from the combo box for classes being held that day
then I want it to compare to the tblStudentsAndClasses to
see if in fact this person is registered for that class.
If not then I want to pop a msg box for them to see a
staff member for assistance. If so then proceed and
register them in the tblVisits. I hope this makes it a
little clearer.

So here is what I think I need to do, please advise. Do I
need a subquery or something else?

vClass = "select * from tblStudentsAndClasses " & "WHERE
ScheduleClassID =" & tblScheduleClass.Class = Me!
REASONLIST.Column(0)
 
On Thu, 1 Apr 2004 04:35:40 -0800, "Nike"

Answers inline.
It sounds that easy but it isn't. Maybe I didn't say it
right.
I have 5 tables (actually more but this states my case)
tblCustomer
LAST4SSAN these three are my primary keys
FName
LNAME
Click to expand...

This is a BAD CHOICE OF PRIMARY KEY. You have no guarantee that it
will be unique; many people have the same name; some people don't have
SSANs; it's still illegal to require people to provide SSANs; there
are many fraudulent SSAN's. I'd really suggest assigning a CustomerID,
either a concealed autonumber (used only within your database for
linking), or a sequential number maintained with code.
tblClass
ID
Class and other reasons

tblScheduleClass
id
ClassID
Date
Time
etc.

tblStudentsAndClasses
ID
ScheduleClassID
LAST4SSAN/FNAME/LNAME

tblVisits
ID
LAST4SSAN/FNAME/LNAME
etc. other info related to the visit
Click to expand...
Now on my for I have a combo box. When they select an
item from the combo box for classes being held that day
then I want it to compare to the tblStudentsAndClasses to
see if in fact this person is registered for that class.
If not then I want to pop a msg box for them to see a
staff member for assistance. If so then proceed and
register them in the tblVisits. I hope this makes it a
little clearer.
Click to expand...

I'd suggest then basing the Combo Box on a query based on
tblStudentsAndClsses offering only those classes for which the student
is enrolled:

SELECT tblStudentsAndClasses.ClassID, tblClass.Class
FROM tblStudentsAndClasses INNER JOIN tblClass
ON tblStudentsAndClasses.ClassID = tblClass.ID
ORDER BY Class;

You could then put code in the combo box's Not In List event to pop up
a message if the class they want isn't on the list.
So here is what I think I need to do, please advise. Do I
need a subquery or something else?

vClass = "select * from tblStudentsAndClasses " & "WHERE
ScheduleClassID =" & tblScheduleClass.Class = Me!
REASONLIST.Column(0)
Click to expand...

This is simply not valid. It's neither a SQL string nor valid VBA. At
the very best it would evaluate the logical expression

tblScheduleClass.Class = Me!REASONLIST.Column(0)

as either TRUE or FALSE, and then select all the enrollment table
fields for ClassID equal to TRUE (or equal to FALSE). Not what you
want!
 
Back
Top