473,288 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

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

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

33 2621
MMcCarthy
14,534 Expert Mod 8TB
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
8,834 Expert 8TB
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
mjvm
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
14,534 Expert Mod 8TB
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
mjvm
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
8,834 Expert 8TB
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
mjvm
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
32,554 Expert Mod 16PB
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
mjvm
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
32,554 Expert Mod 16PB
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
32,554 Expert Mod 16PB
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
mjvm
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
14,534 Expert Mod 8TB
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
mjvm
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
14,534 Expert Mod 8TB
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
mjvm
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
mjvm
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
14,534 Expert Mod 8TB
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
mjvm
52
yep - I get that.

Thank you and till my next challenge........ I hope you all keep well!
Jan 13 '10 #20
ADezii
8,834 Expert 8TB
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
mjvm
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
8,834 Expert 8TB
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
32,554 Expert Mod 16PB
@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
8,834 Expert 8TB
I thought that it was only me that found this Thread difficult to pick apart.
Jan 14 '10 #25
mjvm
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
8,834 Expert 8TB
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
mjvm
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
32,554 Expert Mod 16PB
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
mjvm
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
mjvm
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
mjvm
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
8,834 Expert 8TB
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
mjvm
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

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

Similar topics

4
by: sheree | last post by:
I have 3 tables (amoung a few others) in a small access database. The tables are as follows: == AEReport -------- AEID (PK) RptCatelog GCRCID PatientID EvntDate
2
by: Dejan Pujic | last post by:
Hello, I'm having difficulty figuring out how to update values in a certain column, where the new information depends on the previous. Here's a further description: The whole database is for...
2
by: dskillingstad | last post by:
I would really appreciate someone's help on this, or at least point me in the right direction.... I'm working on a permit database that contains 12 tables, and rather than list all of the...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
2
by: NigelMThomas | last post by:
I have an especially challenging problem. I know there are a few geniuses in this group so perhaps; you can advise me whether or not this can be done as an update query in Access. Thanks. I am...
2
by: Brett | last post by:
My database has 2 tables: Table1 & Table2. If a field is not null on a record in table2, then the not null fields in table1 that correspond to the records in table1 needs to be updated to match the...
3
by: turtle | last post by:
I have Two tables (Table1 and Table2). Both tables have a common field called part number. Table 1 contains an extra field that i would like to update table 2 to match if the part number matches....
4
by: Doris | last post by:
It does not look like my message is posting....if this is a 2nd or 3rd message, please forgive me as I really don't know how this site works. I want to apologize ahead of time for being a novice...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.