|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can't figure this outI have 3 tables, 1 with over 1,000 student names, student ID primary key, 2nd
table has discipline incidents each incident has a unique id and is has the student id as a link to the student demographic table, the 3rd table has 11th grade test scores it has about 200 records and each is identified by the student id number. When I try and create a form using the form wizard the form count for records only shows about 200 or so records. Why doesn't have the over 1,000 records and the records that have discipline and test data linked should show that. I do not know if I explained this clearly, but I cannot figure it out. Del Dobbs You need left joins from student table to the other two.
Show quoteHide quote "DUNNER7" wrote: > I have 3 tables, 1 with over 1,000 student names, student ID primary key, 2nd > table has discipline incidents each incident has a unique id and is has the > student id as a link to the student demographic table, the 3rd table has 11th > grade test scores it has about 200 records and each is identified by the > student id number. When I try and create a form using the form wizard the > form count for records only shows about 200 or so records. Why doesn't have > the over 1,000 records and the records that have discipline and test data > linked should show that. I do not know if I explained this clearly, but I > cannot figure it out. > > Del Dobbs On Thu, 18 Jun 2009 11:07:01 -0700, DUNNER7
<DUNN***@discussions.microsoft.com> wrote: >I have 3 tables, 1 with over 1,000 student names, student ID primary key, 2nd The default type of join - INNER JOIN - will return records only if all tables>table has discipline incidents each incident has a unique id and is has the >student id as a link to the student demographic table, the 3rd table has 11th >grade test scores it has about 200 records and each is identified by the >student id number. When I try and create a form using the form wizard the >form count for records only shows about 200 or so records. Why doesn't have >the over 1,000 records and the records that have discipline and test data >linked should show that. I do not know if I explained this clearly, but I >cannot figure it out. > >Del Dobbs in the query have matching records. Open the query in design view and right click the join line between Students and TestScores; view the join's Properties. Change it to option 2 (or 3) - "Show all records in Students and matching records in TestScores". You'll now get all students, with NULL values for all fields in the third table. Note that if a student appears multiple times in the second table AND in the third table you'll get duplicate records in the query, since there's no relationship between them. -- John W. Vinson [MVP] The default relationship (#1) between two tables links the primary key in
one of the tables to the foreign key in the other table. When you try and display the records in this relationship, you will only get the records in both tables where the primary key and the foreign key have the same value. For example, in your third table there are only 200 records so there are only 200 StudentIDs. So at a max, there are only 200 records where the primary key matches the foreign key. If you want to see student discipline records and test scores in one form, the proper construct is a form with two subforms. The main form should be based on the student table. One subform should be based on the discipline incidents table and the other subform should be based on the test scores table. When you open the main form in design view, you will then see two subform controls along with your other controls. Both subform controls need to have the Linkmaster and Linkchild properties set to StudentID. Steve san***@penn.com Show quoteHide quote "DUNNER7" <DUNN***@discussions.microsoft.com> wrote in message news:F4788418-995F-4C13-A783-C41C827291A3@microsoft.com... >I have 3 tables, 1 with over 1,000 student names, student ID primary key, >2nd > table has discipline incidents each incident has a unique id and is has > the > student id as a link to the student demographic table, the 3rd table has > 11th > grade test scores it has about 200 records and each is identified by the > student id number. When I try and create a form using the form wizard the > form count for records only shows about 200 or so records. Why doesn't > have > the over 1,000 records and the records that have discipline and test data > linked should show that. I do not know if I explained this clearly, but I > cannot figure it out. > > Del Dobbs Thanks, I sort of figured it out on my own, but that explained it well.
Del Show quoteHide quote "Steve" wrote: > The default relationship (#1) between two tables links the primary key in > one of the tables to the foreign key in the other table. When you try and > display the records in this relationship, you will only get the records in > both tables where the primary key and the foreign key have the same value. > For example, in your third table there are only 200 records so there are > only 200 StudentIDs. So at a max, there are only 200 records where the > primary key matches the foreign key. > > If you want to see student discipline records and test scores in one form, > the proper construct is a form with two subforms. The main form should be > based on the student table. One subform should be based on the discipline > incidents table and the other subform should be based on the test scores > table. When you open the main form in design view, you will then see two > subform controls along with your other controls. Both subform controls need > to have the Linkmaster and Linkchild properties set to StudentID. > > Steve > san***@penn.com > > > > "DUNNER7" <DUNN***@discussions.microsoft.com> wrote in message > news:F4788418-995F-4C13-A783-C41C827291A3@microsoft.com... > >I have 3 tables, 1 with over 1,000 student names, student ID primary key, > >2nd > > table has discipline incidents each incident has a unique id and is has > > the > > student id as a link to the student demographic table, the 3rd table has > > 11th > > grade test scores it has about 200 records and each is identified by the > > student id number. When I try and create a form using the form wizard the > > form count for records only shows about 200 or so records. Why doesn't > > have > > the over 1,000 records and the records that have discipline and test data > > linked should show that. I do not know if I explained this clearly, but I > > cannot figure it out. > > > > Del Dobbs > > > Your problem suggests if you have a moment that you might be able to help me
figure out how to alphabetize listrs of names, etc. MS Help is useless--doesn't even list the subject. "Sort" used to do it in XP, but..... Richard NYC Show quoteHide quote "DUNNER7" <DUNN***@discussions.microsoft.com> wrote in message news:F4788418-995F-4C13-A783-C41C827291A3@microsoft.com... >I have 3 tables, 1 with over 1,000 student names, student ID primary key, >2nd > table has discipline incidents each incident has a unique id and is has > the > student id as a link to the student demographic table, the 3rd table has > 11th > grade test scores it has about 200 records and each is identified by the > student id number. When I try and create a form using the form wizard the > form count for records only shows about 200 or so records. Why doesn't > have > the over 1,000 records and the records that have discipline and test data > linked should show that. I do not know if I explained this clearly, but I > cannot figure it out. > > Del Dobbs The standard table of names has LastName, FirstName and MiddleInitial. To
display the names in alphabetical order in a form or report you need to create a query based on that table. The query must have the LastName field with the soty set to ascending. Then you have the option to display the full name as three separate fields or you can use a calculated field to display the full name in one textbox: FullName:[FirstName] & " " [MiddleInitial] & " " & [LastName] Steve san***@penn.com Show quoteHide quote "Richard Lukin" <richardlu***@rcn.com> wrote in message news:9664C8EE-C310-4D90-A125-FD151A8DFD8D@microsoft.com... > > Your problem suggests if you have a moment that you might be able to help > me figure out how to alphabetize listrs of names, etc. MS Help is > useless--doesn't even list the subject. "Sort" used to do it in XP, > but..... > > Richard NYC > > > > "DUNNER7" <DUNN***@discussions.microsoft.com> wrote in message > news:F4788418-995F-4C13-A783-C41C827291A3@microsoft.com... >>I have 3 tables, 1 with over 1,000 student names, student ID primary key, >>2nd >> table has discipline incidents each incident has a unique id and is has >> the >> student id as a link to the student demographic table, the 3rd table has >> 11th >> grade test scores it has about 200 records and each is identified by the >> student id number. When I try and create a form using the form wizard >> the >> form count for records only shows about 200 or so records. Why doesn't >> have >> the over 1,000 records and the records that have discipline and test data >> linked should show that. I do not know if I explained this clearly, but >> I >> cannot figure it out. >> >> Del Dobbs > On Mon, 6 Jul 2009 09:22:30 -0400, "Richard Lukin" <richardlu***@rcn.com> If this is a list of names in an Access Table, you can very simply create awrote: >Your problem suggests if you have a moment that you might be able to help me >figure out how to alphabetize listrs of names, etc. MS Help is >useless--doesn't even list the subject. "Sort" used to do it in XP, >but..... > >Richard NYC query sorted by the name fields. Don't try to sort the data *in the table* - tables are for data storage, not for data presentation. (Well, you CAN do it in A2007, click the header above the field that you want to sort and choose the sort order; but Access is just creating a hidden query, not actually moving data around). -- John W. Vinson [MVP]  |
|||||||||||||||||||||||
Other interesting topics