select newest (or oldest) records from double records

[Tantetruus]

Hi,

Ive got a database with students.
Some of them have been on more then one school.


The unique number of each person (social security?Dont know the english phrase) will therefore appear more then once.

Within each person there is also a ID that IS unique, within this person.

I want to select each person from the table and if they are not unique, i want to use the ID to make a selection within the not unique persons.

Sorry for my explanation, but my english has seen better days...

[ranman256]

A student table should never have repeating persons. Key the socal-security# or the uniq number so nobody can repeat.
BUT a linked tSchool table CAN have repeating students.
Just like a student can have muiltiple phones. For multiple anything, it should be in its own table.

If you want to clean up, make a new tStudent table, keyed on ID. Add all persons to it. There will be no repeats.
Make a new tSchool table, add all students to it. All repeating students, with their uniq schools will get saved.

[June7]

Appears 3 tables are needed.

tStudents
StudentID (PK)
LastName
FirstName
BirthDate
Address

tSchools
SchoolID (PK)
SchoolName
Address

tStudentSchools
StudentID (FK)
SchoolID (FK)

[Tantetruus]

Of course youre right about a student database not having duplicates.
The duplicates originate from combining this table with the school "career" database where in many cases more then one schools are attended
so you get several occasions of one student, but with different schools.

What i try to get is get each student with the latest school they attended.
In this case theres an ID for every school the student visited and the higher the ID, the more recent the school,
so i want every student coupled with the latest school.

( i need both the social security number of the student and the unique code pf the school.

[June7]

Assume there is a data field somewhere. Could try TOP N. Review http://allenbrowne.com/subquery-01.html#TopN

[Tantetruus]

Assume there is a data field somewhere. Could try TOP N. Review http://allenbrowne.com/subquery-01.html#TopN

I have a bit of a problem with "pure" sql in access.
I usually "build" a query in the design view of a query.

The second problem is that i use a dutch version of access, so i have a bit of a problem to
"translate" the sql suggestions.
When i build it in the design view its easier for me to reproduce the suggestions..
Ergo, can you help me in absolute "dumbo" modus?

[June7]

I don't know your data structure and I can't translate into Dutch. You will have to make attempt following the Allen Browne tutorial. Post your attempted SQL for analysis if you have difficulty. Providing db is an option. Follow instructions at bottom of my post.

Another approach might be domain aggregate:

SELECT * FROM tablename WHERE [Date field]=DMax("DateField","tablename","StudentID=" & [StudentID]);