Hello all,

Hope all is well

I'm building a database for our record label to manage the catalog as well as sales and royalty reporting to artists.
I'm not a big expert of Access but am learning along the way. I'm using Access 2007.

I'm now at the stage of how to store the sales data from various digital shops.

On my 'Distribution' form I have a command button working where i can automatically import and append an Excel report to an existing table.
Through this I will be able to automatically import new sales reports from various stores periodically. Each store having their own table. Access adding a primary key to each record. All fine.

I have attached a picture of relationships of the tables I have set up, included 1 imported table called 'Statements - Beatport'



Question:

How do I get some of the data from the 'Statements - Beatport' table into the 'Distribution Statements' table adding the PortalID automatically?

PortalID = 4 (for Beatport)
Period = Transaction_Date field from 'Statements - Beatport'
Total = Total sum of Net_Total field (per import)

AND then update the 'Distribution Statements Details' table, including the StatementID for this particular import??

Am I on the right track? Or should I take another direction?

Thanks a lot for your help in advance.

Kind regards, Iris