Thank you. Helpful and reassuring.
"Pavel Romashkin" <pavel_romashkin@hotmail.com> wrote in message
news:3FA00037.7DDCB2C5@hotmail.com...[color=blue]
> I was suspecting that you simplified the case, and yes, I said you need
> no joins becasue of the meaningful PKs.
> I think that your design is good, and PKs need to be autonumbers. And
> yes, in the real case you do need to use joins.
> I always in cases like this build the quereis in the query builder,
> making sure I get the right records with complex joins. Then I simply
> copy the SQL into VBA.
> I disagree that you should make long composite keys. Try to keep the DB
> logically organized - it will be easier to understand and work with. I
> mean, CountryOfOrigin definitely is a property of a Student, not
> tlbCouseProgress. If you ever need CountryOfOrigin from
> tlbCouseProgress, you will use a join. But with the design you have the
> problem is still easily solved:
>
> Dim StrSQL as String
> StrSQL = "SELECT tblCourseProgress.Grade FROM (tblCourseProgress INNER
> JOIN tblStudent ON tblStudent.ID = tblCourseProgress.StudentID) INNER
> JOIN tblCourse ON tblCourseProgress .CourseID = tblCourse.ID WHERE
> tblStudent.Name = 'Maya' AND tblCourse.Name = 'History'"
> Rst.Open StrSQL, CurrentProject.Connection
>
> (air code -please check in query builder)
> and so on. Now you can insert values if Rst.RecordCount is zero, or
> update Grade if its not. To insert a record, build a query to return
> CourseID, StudentID and Grade:
>
> Dim Qry As DAO.Querydef
> StrSQL = "INSERT INTO tlbCouseProgress SELECT * FROM (SELECT
> tblCourses.ID AS CourseID, tblStudents.ID AS StudentID, 64 AS Grade FROM
> tblCourses, tblStudents WHERE tblCourses .Name = 'History' AND
> tblStudents.Name = 'Maya')"
> Set Qry = CurrentDB.CreateQueryDef(vbNullString, StrSQL)
> Qry.Execute
> Set Qry = Nothing
>
> Build the UPDATE SQL similarly. Check it - its all aircode.
> Good luck,
> Pavel
>
> Ilan Sebba wrote:[color=green]
> >
> > Wow, I think you are finally helping me solve a far more serious problem[/color][/color]
I[color=blue][color=green]
> > am having.
> >
> > You write "Also, what is the point of the joins." Allow me to take you[/color][/color]
up[color=blue][color=green]
> > on this.
> >
> > At first I thought 'Idiot me - why have the joins' but then I realised[/color][/color]
why I[color=blue][color=green]
> > do have them. The tables I offered in my original posting where a
> > simplification, perhaps an over simplification. In my true database,[/color][/color]
the[color=blue][color=green]
> > tables are more complex. I think you were able to suggest that I 'do[/color][/color]
away'[color=blue][color=green]
> > with the joins because the primary key of Course Progress was a[/color][/color]
combination[color=blue][color=green]
> > of two 'meaningful' (ie not autonumbers). But what if the compound[/color][/color]
primary[color=blue][color=green]
> > key is a combination of two foreigh keys, each of which being an[/color][/color]
autonumber[color=blue][color=green]
> > in their original table? Allow me to illustrate:
> >
> > tblCourse
> > University[PK1]
> > CourseName [PK2]
> > Trimester [PK3]
> > Name (eg 'history')
> > ID [autonumber]
> >
> > tblStudent
> > CountryOfOrigin [PK1]
> > ID_NumberInCountryOfOrigin [PK2]
> > Name
> > ID [autonumber]
> >
> > tblCourseProgress
> > StudentID
> > CourseID
> > Grade
> >
> > Now, when I am writing the VB code, I have a pretty good idea of what[/color][/color]
course[color=blue][color=green]
> > I am dealing with, and which student I am dealing with, but as far as[/color][/color]
these[color=blue][color=green]
> > autonumbers go, the serve one purpose: to facilitate relationships. I[/color][/color]
have[color=blue][color=green]
> > no idea what these numbers may be.
> >
> > In your original post you wrote "Check if there is a record for "Maya"[/color][/color]
and[color=blue][color=green]
> > "History" in tblStudentProgress". This is now much more complicated to[/color][/color]
do.[color=blue][color=green]
> > If my understanding is correct I can do one of the following two:
> >
> > 1. Run two queries, on on the tblStudent, and one on tblCourse, to[/color][/color]
extract[color=blue][color=green]
> > the autonumber ID's of 'Maya' and 'History', and then procede to do a[/color][/color]
search[color=blue][color=green]
> > as you suggested, (only using Maya's ID and History's ID rather than[/color][/color]
their[color=blue][color=green]
> > names), or
> >
> > 2. Use the joins.
> >
> > My view is this: don't use autonumber ID's. Use really big composite ke[/color][/color]
ys[color=blue][color=green]
> > so that in this example the composite key for tblCoursePropgress would[/color][/color]
be[color=blue][color=green]
> > this:
> >
> > tlbCouseProgress
> > University[PK1]
> > CourseName [PK2]
> > Trimester [PK3]
> > StudentCountryOfOrigin [PK4]
> > StudentID_NumberInCountryOfOrigin [PK5]
> > Grade
> >
> > If I am to find "Maya"'s ID and "History"'s ID, then I have to go[/color][/color]
through[color=blue][color=green]
> > all 5 primary keys anyway. And if I have to go through all 5 primary[/color][/color]
keys,[color=blue][color=green]
> > then extracting the ID number becomes an unecessary complication as I[/color][/color]
can[color=blue][color=green]
> > remove the ID numbers and extract the CourseProgress record directly[/color][/color]
from[color=blue][color=green]
> > the same 5 primary keys. However, all the word (that is, newsgroups)[/color][/color]
tell[color=blue][color=green]
> > me that it is much better to use the autonumber thingy. But if that is[/color][/color]
the[color=blue][color=green]
> > case, then the only way of extracting records efficiently must be[/color][/color]
through[color=blue][color=green]
> > JOINs?
> >
> > Now it could be that I am confusing two issues, and that IDs really have
> > nothing to do with extracting data from tables. I must admit, I am a[/color][/color]
bit[color=blue][color=green]
> > lost.
> >
> > I am eager for your reply, or a comment from anyone else regarding this.
> >
> > Many thanks
> >
> > Ilan
> >
> > "Pavel Romashkin" <pavel_romashkin@hotmail.com> wrote in message
> > news:3F9EB9F4.97D08BD0@hotmail.com...[color=darkred]
> > > I get a feeling that you are returning a non-updateable ADO recordset
> > > from your RunSQL procedure. Also, what is the point of the joins? It
> > > seems that the process should be as simple as the following 2 steps:
> > > 1. Check if there is a record for "Maya" and "History" in[/color]
> > tblStudentProgress.[color=darkred]
> > > 2. Append or modify that record in tblStudentProgress.
> > >
> > > Dim Rst as ADODB.Recordset
> > > Set Rst = New ADODB.Recordset, CourseID as String, StudentID as String
> > > CourseID = "History"
> > > StudentID = "Maya"
> > > Rst.Open "SELECT* FROM tblStudentProgress WHERE CourseFK = '" &[/color][/color][/color]
CourseID[color=blue][color=green][color=darkred]
> > > & "' AND StudentFK = '" & StudentID & "'", CurrentProject.Connection
> > > IF Rst.RecordCount = 0 then
> > > RunSQL "INSERT INTO tblStudentProgress VALUES ('History', 'Maya', 64)"
> > > Else
> > > RunSQL "UPDATE tblStudentProgress SET Grade = 64 WHERE CourseFK =
> > > 'History' AND StudentFK = 'Maya'"
> > > endif
> > > Rst.Close
> > > Set Rst = Nothing
> > >
> > > This is air code - please debug.
> > > Cheers,
> > > Pavel
> > >
> > > Ilan Sebba wrote:
> > > >
> > > > When it comes to adding records in related tables, Access is really[/color]
> > smart.[color=darkred]
> > > > But when I try to do the same using ADO, I am really stupid.
> > > >
> > > > Say I have two parent tables (eg Course, Student) and one child[/color][/color][/color]
table[color=blue][color=green][color=darkred]
> > > > (StudentProgress). The course progress records how a student[/color][/color][/color]
progresses[color=blue][color=green]
> > on[color=darkred]
> > > > a course. I have one course (History) and one student called Maya.[/color][/color][/color]
I[color=blue][color=green]
> > now[color=darkred]
> > > > want to record her grade (64). If I do this in Access using a form,[/color]
> > then[color=darkred]
> > > > the form will magically display her progress record.
> > > >
> > > > If however I do so in VBA, then I must first distinguish between two[/color]
> > cases:[color=darkred]
> > > > (1) A record in StudentProgress does exist for Maya, in which case I[/color]
> > have to[color=darkred]
> > > > modify the grade to 64.
> > > > (2) No record exists for Maya in StudentProgress, so I have to[/color][/color][/color]
create[color=blue][color=green]
> > it.[color=darkred]
> > > >
> > > > Unfortunately, when I try to create the record, I am told that I[/color][/color][/color]
will[color=blue][color=green]
> > create[color=darkred]
> > > > duplicate data in an indexed or primary field. I think that what I[/color][/color][/color]
am[color=blue][color=green][color=darkred]
> > > > accidentally trying to do is create a new record in student, and a[/color][/color][/color]
new[color=blue][color=green][color=darkred]
> > > > record in course, as opposed to a new record in StudentProgress.
> > > >
> > > > Here are my tables, and the code
> > > >
> > > > tblCourse
> > > > Subject [PK]
> > > >
> > > > tblStudent
> > > > Name [PK]
> > > >
> > > > tblStudentProgress
> > > > CourseFK [PK1]
> > > > StudentFK [PK2]
> > > > Grade
> > > >
> > > > Here is the code which is sopposed to do the following:
> > > > (1) tries to see if a record for course progress already exists for[/color][/color][/color]
Maya[color=blue][color=green]
> > in[color=darkred]
> > > > History
> > > > (2) if not, tries to create it
> > > >
> > > > Private Sub TestAddingRecordInChildTable()
> > > > Dim rcsCourseProgress As ADODB.Recordset
> > > > Dim strSelect As String
> > > > Dim strFrom As String
> > > > Dim strWhere As String
> > > > Dim strSQL As String
> > > >
> > > > strSelect = "SELECT tblCourse.Subject, tblStudent.Name,
> > > > tblStudentProgress.Grade "
> > > > strFrom = " FROM tblStudent INNER JOIN (tblCourse INNER JOIN
> > > > tblStudentProgress ON tblCourse.Subject =[/color][/color][/color]
tblStudentProgress.CourseFK)[color=blue][color=green]
> > ON[color=darkred]
> > > > tblStudent.Name = tblStudentProgress.StudentFK"
> > > > strWhere = " WHERE tblStudent.Name = 'Maya' AND
> > > > tblCourse.Subject='History'"
> > > >
> > > > strSQL = strSelect & strFrom & strWhere
> > > >
> > > > Set rcsCourseProgress = RunSQL(strSQL) 'RunSQL is my own[/color][/color][/color]
procedure[color=blue][color=green][color=darkred]
> > > > which works OK.
> > > >
> > > > If rcsCourseProgress.RecordCount = 0 Then
> > > > 'no record exists for Maya in History.
> > > > rcsCourseProgress.AddNew
> > > > rcsCourseProgress.Fields("Name") = "Maya"
> > > > rcsCourseProgress.Fields("Subject") = "History"
> > > > rcsCourseProgress.Fields("Grade") = 64
> > > > rcsCourseProgress.Update
> > > > End If
> > > >
> > > > End Sub[/color][/color][/color]
