Can't figure this out

[DUNNER7]

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

 

[KARL DEWEY]

You need left joins from student table to the other two.

 

[John W. Vinson]

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 default type of join - INNER JOIN - will return records only if all tables
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.

 

[DUNNER7]

Thanks, I sort of figured it out on my own, but that explained it well.

Del

 

[Richard Lukin]

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

 

[John W. Vinson]

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

If this is a list of names in an Access Table, you can very simply create a
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).