Unanswered: Populate new record with data from a query
I am very new at database design - so please forgive me if this is an easy answer.
We are a custom cabinet shop. I have a piece of software that generates an Access database table for the currently open job. This database is dynamic - it changes every time I load a new cabinet job file. It has a table in it called "Doors." It is a list of every door style used on the job, but it lists every door on the job - even if they are the same door style. I need to compile and sum that table so that it tells me how many of each door style there is.
I've accomplished this by writing a query that has a summary function in it that counts unique door names and returns the number of doors of each unique door style there is on the job. In my new database, I've created a linked table in my new database file that links to that "doors" table. Up to this point it is working.
The wall I've run into is that, because my cabinet software only generates this one database file, every job I open overwrites the previous data within that "Doors" table. I need to be able to copy the data from the linked "doors" table (data for whatever the currently opened cabinet job is) and paste it into a new static record in my database.
Is there some internal function in Access that would allow this? Or is this going to require some coding?
Thank you to anyone that can point me in the right direction.
You would import that 'doors' table into the db with an 'order' key(?) (or some unique ID (autonumber) with a datestamp to show its unique door order)
Youd have a Master Client key, then import that 1 door order into the client order.