Macros to create and then update records

[Nevsky78]

Hi all,

I have attached the database that I have been working on.

My ultimate goal is to have a record in tbl_customer_product_month for each combination of customer, product and month. I will want to add values in each instance of cpm_value.

I have tried to show the relationships in the qry_final_table, and the SQL for them is below.

SELECT tbl_customer_product_month.cpm_id, tbl_customer_product_month.cpm_value
FROM tbl_market INNER JOIN ((tbl_customer INNER JOIN (dbo_product INNER JOIN tbl_customer_product ON dbo_product.p_code = tbl_customer_product.cp_p_code) ON tbl_customer.c_id = tbl_customer_product.cp_c_id) INNER JOIN tbl_customer_product_month ON tbl_customer_product.cp_id = tbl_customer_product_month.cpm_cp_id) ON tbl_market.m_id = tbl_customer_product_month.cpm_m_id;

I have 2 parts to my query to you guys.

1) Does anyone know how I create VBA code to create a record for each instance of customer, product and month?

2)After the initial table has been created, would there be some VBA code I could create to add any new instances of customer, product and month (those tables will regularly change)?

Many Thanks in advance!
Nick

 

[Nevsky78]

I've also just realised this should be in the VBA and Modules Forum but I don't know how to move the post there.

Can anybody help?
Nick