By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,609 Members | 3,812 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,609 IT Pros & Developers. It's quick & easy.

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

P: 52
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
Jan 9 '10 #1

✓ answered by ADezii

The code assumes that there is only a single Record in qryChase relating to each Student:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rst = MyDB.OpenRecordset("qryChase", dbOpenForwardOnly)
  6.  
  7. With rst
  8.   Do While Not .EOF
  9.     'Insert Only if StudentID does not already exist in tblNotes
  10.     If DCount("*", "tblNotes", "[NoteStudentID] = " & ![StudentID]) = 0 Then
  11.       CurrentDb.Execute "INSERT INTO tblNotes ([NoteStudentID]) VALUES (" & _
  12.                          ![StudentID] & ")", dbFailOnError
  13.     End If
  14.       .MoveNext
  15.   Loop
  16. End With
  17.  
  18. rst.Close
  19. Set rst = Nothing

Share this Question
Share on Google+
33 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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=tblBookings
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Jan 9 '10 #2

ADezii
Expert 5K+
P: 8,638
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
Jan 9 '10 #3

P: 52
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblStudents.StudentID, tblStudents.StudentName, tblStudents.DateFormsSent, tblStudents.DateFormsRecd
  2. FROM tblSchools INNER JOIN tblStudents ON tblSchools.IdCode = tblStudents.StudSchCode
  3. 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,
Jan 9 '10 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 10 '10 #5

P: 52
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
ReasonNotProceed 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
Jan 10 '10 #6

ADezii
Expert 5K+
P: 8,638
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 Interrelationship between tblSchools and tblStudents, once you have Updated the Notes Table, how can you possibly know to which Student the Updates apply?
Jan 10 '10 #7

P: 52
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.

:)
Jan 10 '10 #8

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 11 '10 #9

P: 52
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.
Jan 11 '10 #10

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 11 '10 #11

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 11 '10 #12

P: 52
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
Jan 13 '10 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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 :)
Jan 13 '10 #14

P: 52
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?
Jan 13 '10 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
OK now having read through the thread again this is what I think is happening. You have data somewhere probably excel which contains the Notes data as in it is a record of all the notes so far. However, this information is not tied to the studentid in the database and therefore when you upload it there is no way to tie a note to a student.

If that is the case this takes us back to NeoPa's post no. 9. You have to have some way to relate the data you are uploading to the student or you just can't do what you want to do. As I see it your options are ...
  • Add the studentid manually to each note record before uploading it or after.
  • Find another field or fields which tie the records together like student name.

Otherwise you just can't do this.
Jan 13 '10 #16

P: 52
Thank you.

As I struggled to explain what I needed, I was starting to think it might not be possible.

Thank you MSquared, NeoPa and Adezii for your time and patience - and your skills!
Jan 13 '10 #17

P: 52
An alternative solution - would be to export the qryChase to excel including StudentID (from tblStudents), then add the notes information that I want, and import back to Access into tblNotes, renaming StudentID to NotesStudentID so that it provides the reference.
? Maybe
Jan 13 '10 #18

MMcCarthy
Expert Mod 10K+
P: 14,534
That should work but remember if you have more than one note for a student that is a separate record. You only need to add studentid to notes information.
Jan 13 '10 #19

P: 52
yep - I get that.

Thank you and till my next challenge........ I hope you all keep well!
Jan 13 '10 #20

ADezii
Expert 5K+
P: 8,638
How about for every [StudentID] in qryChase, you programmatically Add a corresponding Record in tblNotes consisting of that [StudentID], then it is simply a matter of Updating tblNotes?

P.S. - tblNotes will now contain Records consisting of [NotesStudentID] for every [StudentID] in qryChase.
Jan 13 '10 #21

P: 52
That sounds like a good idea, but I don't know how to do that. Is it difficult or complicated to code?

Cheers,
Marcella
Jan 13 '10 #22

ADezii
Expert 5K+
P: 8,638
The code assumes that there is only a single Record in qryChase relating to each Student:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rst As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb
  5. Set rst = MyDB.OpenRecordset("qryChase", dbOpenForwardOnly)
  6.  
  7. With rst
  8.   Do While Not .EOF
  9.     'Insert Only if StudentID does not already exist in tblNotes
  10.     If DCount("*", "tblNotes", "[NoteStudentID] = " & ![StudentID]) = 0 Then
  11.       CurrentDb.Execute "INSERT INTO tblNotes ([NoteStudentID]) VALUES (" & _
  12.                          ![StudentID] & ")", dbFailOnError
  13.     End If
  14.       .MoveNext
  15.   Loop
  16. End With
  17.  
  18. rst.Close
  19. Set rst = Nothing
Jan 14 '10 #23

NeoPa
Expert Mod 15k+
P: 31,494
@mjvm
Sometimes (read nearly always) the biggest problem involved is the communication of the problem between member and experts.

Perhaps we should have a forum for problem expression (Only half joking). I'm saying you're certainly not alone in finding that difficult.
Jan 14 '10 #24

ADezii
Expert 5K+
P: 8,638
I thought that it was only me that found this Thread difficult to pick apart.
Jan 14 '10 #25

P: 52
I am about to have a go at using the code - wish me luck!

I really thought that I was being as clear as I possibly could - written communication shouldn't be this hard!

:)
Jan 15 '10 #26

ADezii
Expert 5K+
P: 8,638
You have to appreciate how difficult, as least for me, it is at times to understand a problem, then to come up with a viable solution at the other end of a Web Page.
Jan 15 '10 #27

P: 52
Of absolutely - I was completely aware that it was me not getting my message across and that I wasn't being clear or detailed enough for you guys.

What is difficult is not having the right words - the terminology - to ask the question. You don't get the answer you need if you don't - or can't - write the right question
Jan 15 '10 #28

NeoPa
Expert Mod 15k+
P: 31,494
On the other hand, though sometimes it's strained, I think we're all getting better at reading between the lines. I feel positively psychic sometimes.

Best results are always achieved when the question is expressed clearly though of course.

A technique I always use (Yes. I post technical questions too sometimes.) is to build up a logical structure. I avoid referring to anything in my explanation that is not already explained. Think of a pyramid. Very solid and stable. That's because everything that is not on the ground is on all the other stuff that's already solidly founded.

I always refer to items by the same name too. Never "... tblAccount ... The Accounts table ..." as this interferes with the reader's ability to catalogue their understanding internally.
Jan 15 '10 #29

P: 52
Oh my goodness! It worked.

Thank you so much for the code ADezii, and thank you to NeoPa, MSquared and ADezii for your patience and persistence in "unpicking the thread".

Regards,
marcella
Jan 15 '10 #30

P: 52
NeoPa - just saw your post and I will keep those points in mind the next time I post. However, I will now turn the computer off and go to bed (I'm in Melbourne).

Thanks again!
Jan 15 '10 #31

P: 52
HI - back again!

this is a response to the message from Adezii that is listed as best answer. Now that the database is up and running and I am testing this process, I have hit a hurdle.

Adezii - I have used your code without changing anything. There is only one record per student in qryChase, but if a student already has a note in tblNotes, then it doesn't create a new note (I can see how this happens in line 9), and then if I go through with the update, it overwrites the existing note.
Is it possible to alter the code so that it creates a new record in tblNotes for all the records in qryChase?

tblStudents and tblNotes is linked with a One to Many relationship.

Regards and thanks in advance.
Marcella
Feb 16 '10 #32

ADezii
Expert 5K+
P: 8,638
Eliminating Lines 10 and 13 should do the trick as long as [NoteStudentID] in tblNotes is not the Primary Key or a Unique Index.
Feb 16 '10 #33

P: 52
Thank you Adezii, that has fixed my problem. I also had made a mistake in setting up the Update query, which I have also fixed and the whole process is now working perfectly.

Thank you so much for your time!

regards.
Feb 16 '10 #34

Post your reply

Sign in to post your reply or Sign up for a free account.