R
Rick A.B.
Need your highly regarded opinion.
I reciently began working on an existing database registering students
for classes. In the past, all registration was done manually. People
would go into registration form for student and pick class from
combobox. I can now download this information from web and import
into a temp table. I have even been able to append information to the
registration table with an append query. What I haven't been able to
do is to use criteria to keep a record from being appended if they are
already registered for that class. The temp table I use is [1000 29]
(I know, there is a space there). The only field from the table that I
use is "Username", This is basically a text based number unique for
each student that corrisponds with the "MCTCID" field in
tblDemotrack. tblDemotrack holds all the student information, Name,
address etc. It's PK is "StudentID" which is an auto number.
tblRegistrationTracking does not contain the "MCTCID" but has
"StudentID" as a FK so I use the tblDemoTrack in my query to mate up
Username to MCTCID. Here is my append query.
INSERT INTO tblRegistationTracking ( StudentID, DateRegistered,
CourseName, DLCourseID )
SELECT tblDemoTrack.StudentID, Date() AS Expr1, 2000 AS Expr3, 2000 AS
Expr2
FROM tblDemoTrack INNER JOIN [1000 29] ON tblDemoTrack.MCTCID = [1000
29].Username
WHERE ((([1000 29].Username)=[tblDemoTrack].[MCTCID]) AND
("TABLE[tblRegistationTracking].[DLCourseID]"<>2000));
The criteria does not work, I get a Data Type Mismatch in criteria
expression each time it runs. I'm not certain but I believe my
problem may be that the DLCourseID and the CourseName come from a
lookup table. That's the way the tblRegistrationTracking table was
originally set up. Is there a better way of doing this other than a
append query. I know I can use Dlookup to check if there is an entry
for that student and course in the Registration table and loop through
the temp table but it seems a lot of work to do so for each class. Am
I missing something in my append query criteria? The datatype for
DLCourse in the table is a number but again, this is set up as a look
up field in the table.
Any help would be appreciated.
Rick
I reciently began working on an existing database registering students
for classes. In the past, all registration was done manually. People
would go into registration form for student and pick class from
combobox. I can now download this information from web and import
into a temp table. I have even been able to append information to the
registration table with an append query. What I haven't been able to
do is to use criteria to keep a record from being appended if they are
already registered for that class. The temp table I use is [1000 29]
(I know, there is a space there). The only field from the table that I
use is "Username", This is basically a text based number unique for
each student that corrisponds with the "MCTCID" field in
tblDemotrack. tblDemotrack holds all the student information, Name,
address etc. It's PK is "StudentID" which is an auto number.
tblRegistrationTracking does not contain the "MCTCID" but has
"StudentID" as a FK so I use the tblDemoTrack in my query to mate up
Username to MCTCID. Here is my append query.
INSERT INTO tblRegistationTracking ( StudentID, DateRegistered,
CourseName, DLCourseID )
SELECT tblDemoTrack.StudentID, Date() AS Expr1, 2000 AS Expr3, 2000 AS
Expr2
FROM tblDemoTrack INNER JOIN [1000 29] ON tblDemoTrack.MCTCID = [1000
29].Username
WHERE ((([1000 29].Username)=[tblDemoTrack].[MCTCID]) AND
("TABLE[tblRegistationTracking].[DLCourseID]"<>2000));
The criteria does not work, I get a Data Type Mismatch in criteria
expression each time it runs. I'm not certain but I believe my
problem may be that the DLCourseID and the CourseName come from a
lookup table. That's the way the tblRegistrationTracking table was
originally set up. Is there a better way of doing this other than a
append query. I know I can use Dlookup to check if there is an entry
for that student and course in the Registration table and loop through
the temp table but it seems a lot of work to do so for each class. Am
I missing something in my append query criteria? The datatype for
DLCourse in the table is a number but again, this is set up as a look
up field in the table.
Any help would be appreciated.
Rick