update query; want to also update the field that links the two tables

[mjvm]

HI,

I have a main table (tblStudents) that holds students where the unique field is StudentID; and a related table (tblNotes) on a one-to-many relationship, where the unique field is NotesID, and the linked field (relationship) is with NotesStudentID. That is one student can have many notes.

I run a query where we isolate a number of students (qryChase).

What I would like to be able to do is then run an update query using the dataset from qryChase. I can update the information I want to land in the Notes table, but don't know how to commit each of these records to the individual student records. That is - I run an update that enters the Notes details including allocating a NotesID, but the Notes table is missing the corresponding entry in NotesStudentID - so it is not linked to each individual record.

I tried entering =StudentID in the Update line of the the NotesStudentID field but that gave me a type conversion failure and still didn't work. I really have no idea if what I want to do is possible, or if the answer is really simple.

I have built a form and subform linking the student records with notes and that works, so I think my relationship setup is ok.

I hope someone is able to either help me or tell me I'm dreaming. I hope this is not a complicated task.....
Thanks for your help in advance,
Regards,
Marcella

[MMcCarthy]

Can you post the full sql of the query qryChase and the metadata for tblStudents and tblNotes.

Here is an example of how to post table MetaData :

Table Name=tblBooking s

[i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[ADezii]

I'm a little confused as to exactly what you are trying to accomplish, and I may be oversimplifying , but couldn't you:

1. Create a New Query
2. Add qryChase and tblNotes to the design Grid
3. Link qryChase.[StudentID] to tblNotes.[NotesStudentID]
4. Any Updating would be a simple matter at this point

[mjvm]

HI MSquared and Adezii,

Firstly thank you for looking at my problem.

In answer to Adezzi first - that is what I have tried to do, but I don't know how to get it to update the NotesStudentID field because it has to be the same as the StudentID field from tblStudents, and is different for each record.

To MSquared, here is the SQL for qryChase:

SELECT tblStudents.StudentID, tblStudents.StudentName, tblStudents.DateFormsSent, tblStudents.DateFormsRecd
FROM tblSchools INNER JOIN tblStudents ON tblSchools.IdCode = tblStudents.StudSchCode
WHERE (((tblStudents.DateFormsSent) Like "*") AND ((tblStudents.DateFormsRecd) Not Like "*"));

How do I get to the Metadata to paste it? I went into Database documenter and got a PDF with the info that I think you are asking for, but it was too big to attach.

Thanks,

[MMcCarthy]

To get the metadata just change the tables to design view and type the information you see. Otherwise use the documenter but only select the tables tblStudent and tblNotes. When the report previews in Access export it to rtf.

[mjvm]

I have used the documenter to collect the info, and then copied and pasted what I think you are asking for. I made a copy of tblStudents so that I could delete most of the fields and have made sure that you have the fields that relate to my question (i hope :) )

StudentID (tblStudents) and NoteID (tblNotes) are both Autonumber, and the NoteStudentID Field in tblNotes is the link.

Thanks again.

Table: Copy Of tblStudents

StudentID Long Integer 4
StudentName Text 30
DOB Date/Time 8
StudSchCode Text 10
Year Text 6
KISS Yes/No 1
PreviousRef Yes/No 1
RefType Text 50
RefWorker Text 5
DateRef Date/Time 8
Category Text 10
PhoneAction Text 30
ReasonNotProcee d Text 30
DateFormsSent Date/Time 8
DateFormsRecd Date/Time 8
DateProceedAsst Date/Time 8
FormsAction Text 30
SOReason Text 30
DateAllocated Date/Time 8
AllocatedWorker Text 5


Table: tblNotes

NoteID Long Integer 4
NoteStudentID Long Integer 4
ContactNote Text 255
NoteWorker Text 5
NoteDate Date/Time 8
NoteType Text 10
Duration Text 15

[ADezii]

I can update the information I want to land in the Notes table, but don't know how to commit each of these records to the individual student records. That is - I run an update that enters the Notes details including allocating a NotesID, but the Notes table is missing the corresponding entry in NotesStudentID

Forgive me if I appear confused.
Since qryChase is based on the Interrelationsh ip between tblSchools and tblStudents, once you have Updated the Notes Table, how can you possibly know to which Student the Updates apply?

[mjvm]

This is a new database so the only data in it is what I have put in to test it. If I try the update, I go back to the Notes table where there is a set of new entries but they are not tied to the Student.

I don't know if what I want to do is possible, but figure you guys will know - you have been so incredibly helpful in the past.

:)

[NeoPa]

I think what ADezii was expressing Marcella, is that while we have the experience and understanding to extract (or help you extract) data from out of your database, that does rely on the data being there in the first place, in one form or another. The suspicion here (and only you can confirm or deny this as we have no direct access to your database) is that the data is not there in at all. This would make it impossible to extract it or process it in any way.

[mjvm]

Maybe by thinking in terms of an Update query, that is my mistake. Adezii's question - "how can you possibly know to which Student the Updates apply?" - is exactly my question.

To do it by update, the information in the update line would need to be something like " =StudentID for each individual record ". So I'm pretty sure that's not the answer.

The database has data - information I have entered to make sure that my code is working. The database manages an assessment service - where a school makes a referral, we send out forms, the school sends them back. We have to chase up referral forms that are not returned - hence the query to 'Chase'.

I could have a date field in the student table that records when this happens - but I think there will be students where we do it more than once, so would like to be able to record a number of occurrences of this event.

NeoPa - I noticed that you edited an earlier response to Msquared, and said to use [Code] tags provided - can you please explain where I find them, I'm not sure what you mean.

thanks for your time.

[NeoPa]

The hash (#) button in the posting page (The basic Edit frame doesn't have them at this time. Nor does the Quick Reply frame. You need to select Go Advanced to get this facility.) helps with this. Simply select your code and click on the hash button to have it enveloped automatically.

Doing it manually is achieved by putting [CODE] at the start of the section of code and [/CODE] at the end. Most people just use the hash button though.

[NeoPa]

As for your (main) question, can you explain how you, as a human, recognise which student each note is supposed to be associated with? From there we may be able to find a method to translate that into code.

[mjvm]

Hi NeoPa,

I can only recognise the group of students which all need the same note. As a human, I run qryChase and that's the group we need to allocate the Notes to.

I have been trying to think of a different way of solving the problem. I thought maybe having a table that records each time we send the email, but haven't worked out how that links to the student records.

Maybe I need to stay with using a reminder date field in the student table, and our processes will be that the chase process occurs each week (or whatever) and if we have to chase this information for a student, we make assumptions about how many times they were chased based on the dates entered on their record.

Thanks for your patience,
Marcella

[MMcCarthy]

Marcella

I think you missed what NeoPa was asking. Forget about the database for the moment and try to explain in detail the process whereby a note gets applied to a student and what is a note exactly. The more detail no matter how obvious to you that you give us about this process the better we can help. Think about what is involved if you were doing everything by hand with no computer :)

[mjvm]

Clearly I am missing something! :)

A school rings in and refers a student. The date is recorded, and the forms are sent. When forms are not returned, we email the schools to prompt them to return the forms.

If this was happening on paper, we'd have a running sheet with each student's name at the top, and record the date that we sent the form. The running sheets would sit in an in-tray waiting for forms to be returned. When forms are returned the running sheet would go into the next stage of the process.

When forms are not returned, we would send an email to the school and write a file "note" on that sheet, that on a particular date, we sent them an email prompting them to return the forms. The Note would record the date, the method (email) and the message.

If after the required time, they had still not returned the forms ie: this poor kid is still sitting in the in-tray, we would repeat the process, making another file "note" on the running sheet.

Does that make it clearer - I do hope so?