In my new database, I have a table that shows agencies and gives their contact information.
I have another table that lists the possible addresses of premises that can be used by agencies.

Each Agency can occupy more than one address
Each address can accommodate a number of agencies
Therefore, I have a junction table that holds the primary keys of the Agency table and the addresses table as foreign keys, concatenated to make an exclusive key in the junction table.

I can write an append query that allows me to insert a new agency in the agencies table and a new address in the addresses table. I need an append query to copy the primary keys of the new record from the two tables into the junction table. The problem is that I cannot write to the junction table while I am running the append query that inserts the new records in the Agency and Address tables because their primary keys are autonum fields and do not appear until I close their append query. My problem then is, how to get the last primary key off each of the agency and address tables in a new query that appends them to the junction table.

I would have thought that this would be a very common procedure but I cannot find a single example! Can you help? Are there any examples of the complete append process for many to many tables?

There is a complication! I am visually impaired and use a screen reader. I cannot operate a form as it is unreadable to the screen reader. I can, however, work in MsSQL but that does not allow me to create or run a form.

I tried to view the video on concatenated keys but all I got was Richard's introduction to the lesson. I am completely stumped!

---

Reply from Alex Hedley:

Sounds like it is just a timing issue.
If you run your append query then close it.
You could use a DLAST (Expert 29) to get the Last Record inserted.
If you are using MSSQL you could use a TOP 1 to get the LAST Record.


If it is on a Form you could look at the .Dirty Property.
If you set that the AutoNumber will show then you can use it in your Append.