Query of query

[Daryl2106]

Hi,



I have a form that runs a select query when the teachers name is chosen from a combo box. The form and query work fine returning the names of students in all the classes assigned to that teacher. What I would like to do next is run an append query to write the results of this query and some new data (attendance, attendance period) entered into the form to a table called student attendance using a command button and linked to an append query.

I have been able to design the append query and it will run without error, but appends "0 records". Am I on the right track and what might be the problem? The select query returns about 50 records which new data can be added to; just the selected records and new data are not being appended to the attendance table.

Always grateful,

Daryl

[June7]

Provide the sql statements or the db (instructions at bottom of my post) for analysis.

[Daryl2106]

Thanks June for looking at this for me. The DB needs to be cleaned up quite a bit, but I am trying to reslove this challege a being able to record biweekly attendance from 12 fafcult who each teach multiple courses...the easiest way possible for them to enter their data.

I'll repost. I seem to be having trouble with the attachment mangager. It doesn't delete previous uploads and won't upload the current DB I wish to sent???

Thanks and take care,

Daryl

[Daryl2106]

HI,

I cna't attach the file. It is 27 MB as a zipped file; is it too large? I have already sent the webmaster a question regarding attachments. I can seem to upload it.

Any thoughts on another way to have you look at it?

Thanks and take care,

Daryl

[alansidman]

Copy the file to a new db and strip out all but a few records for sampling purposes. Run a compact and repair. Zip the new file and try to upload.

You may try to run a compact and repair on the existing file and zip it and see if the file size diminishes significantly enough to upload before you try the above.

[June7]

If it is still too large then upload to a fileshare site such as Box.com and post link to the file.

[Daryl2106]

Thanks everyone. I think I was experiencing some propblems with my PC as well. The DB is now attached. Any help and/or direction you can provide will be greatly appreciated. This attendance thing has really been challenging for me.

Take care,

Daryl

[June7]

Are ClassNumbers and CourseNames related by junction table AnnualClassNumbers? If the ClassNumber is saved to StudentEnrollment and StudentAttendance why is the CourseName needed in Attendance?

Need to include ClassNumber field in the StudentAttendanceByFacultyQuery.

The APPEND query is very wrong. Try:
INSERT INTO StudentAttendance (StudentID, ClassNumber, AttendancePeriod, ClassesAttended, ClassesOffered )
SELECT StudentNumber, [ClassNumber] AS Expr1, CDate([Forms]![StudentAttendanceByFacultyQF].[combo1147]) AS AttDate, TempClassessAttended, TempClassessOffered
FROM StudentAttendanceByFacultyQuery
WHERE TempClassessAttended Is Not Null;

[Daryl2106]

Thanks June,

That really helped. I noticed when the date is saved it does not save the date from the combobox. The CDate function converts the the date from the LU_AttendanceDatePeriod into one of seven dates beginning with Jan 01, 1900, etc. I think I need an actual "date" stored that I can use in calculations later (or at least I think I do; I may just need individual entries rather than a range). How can I get the date to be saved as the date as it appears when selected from the combobox.

Also, I need a way to ensure faculty don't enter the same data twice (i.e. for the same attendance period). This would include some sort of key that combines StudentID with CourseNumber and AttendancePeriod and a way to check the AttendanceTable for data that matches first, before the records are actually written. This is way beyond may knowledge at this point, but with some direction I may be able to figure it out.

Thanks for all your help.

Take care,

Daryl

[June7]

The date combobox BoundColumn is the ID field from LU_AttendanceReportDates. Either change the BoundColumn to 2 or change the RowSource to be an SQL that pulls only the date field then set other properties of combobox (ColumnCount, ColumnWidths).

[Daryl2106]

Thnaks,

What is a good source for learning the SQL that I would need to change the row source? I tried simply changing the bound coulumn, by it displayed the AttendanceReportDates key and not the date in the form.

Daryl

[June7]

One source for learning SQL http://www.w3schools.com/sql/default.asp

Don't understand your issue now. I changed the bound column to 2 and the combobox shows date. In what form and what control are you seeing the key?

[Daryl2106]

Hi,

It is saving dates as "January #, 1900" in the StudentAttendanceTable instead of the actual date. I am creating a second form and query for faculty to go into to edit previously addedd attendance. The criteria will be FacultyID and AttendanceReportDate. With the date being recorded as January... I can't select from that field. Thanks for the link; I need it.

Take care,

Daryl

[June7]

Can't replicate the issue with the version of db I have. Records are appended to table with correct date as selected in combobox.

[msoffice.vidyarthi]

Try replacing the AppendAttendanceQ query to below :

INSERT INTO StudentAttendance ( ClassesAttended, ClassesOffered, StudentID, CourseName, AttendancePeriod, ClassNumber )

SELECT
t1.TempClassessAttended, t1.TempClassessOffered, t1.StudentNumber, t1.[Course Title], t2.AttReportingDate,
t3.ClassNumber

FROM StudentAttendanceByFacultyQuery T1, LU_AttendanceReportingDates T2, AnnualClassNumbers T3

INNER JOIN StudentEnrollmentTable ON t3.ID = StudentEnrollmentTable.ClassNumber

WHERE (((t1.TempClassessAttended) Is Not Null) AND ((t2.AttReportingDate)=(SELECT attreportingdate from LU_AttendanceReportingDates where id = [Forms]![StudentAttendanceByFacultyQF].[Combo1147])));


Does it solver your issue