autonumber not distintc in access

Question

Is it possible to create a table where two columns represent a primary key and one of them is autonumber but not distinct. Something like (teacherID,schoolID(FK),teacherName) where first two are primary key and first one is autonumber. When teachers from different schools are inserted the teacherID autoincrement should depend from school.

Example for insertion

• 1, 1, Steven
• 2, 1, Leslie
• 1, 2, Maria

I know it's possible to make SQL query to insert like this, but is it possible in table settings?

Answer 1

That's not how autonumbers work. If you want to do something like this, you will have to calculate it yourself. But I have to caution you against this. I assume this is a teacher table and what you want to do is a bad idea because

1. Each teacher should have their own unique key.
2. It's a bad idea to make the key a composite key where part of the key is a foreign key that can change over time.