CODEWITHSUNDEEP
ms-accessms-access-2016
Todd Brannon
Todd Brannon

Reputation: 182

Transform data in an Access table to multiple rows in a new table

I have a table in Access similar to the first one below with rows for transactions. These transactions can be for two separate products: ProductOne and ProductTwo. I have two transactions shown - one for a single product and one for both products.

My existing table contains columns for Cost and Sale for each of the two products available. My goal is to transform what I have to a table that lists COS and Payable (from the product Cost amount in the first table) on two separate rows. Then I need to do the same for Retail (from the product Sale column and amount in the first table).

For each row in the first (existing) table, there would be three separate rows for each product in the resulting table (COS, Payable, and Retail). For instance, customer Smith would have 3 rows in the resulting table since they only purchased ProductOne. But for customer Jones, there would be 6 rows in the resulting table since they purchased both products.

Table1 to Table2 map

Is there a query that will accomplish this? I feel like this is either fairly complex or I am overthinking it.

Upvotes: 0

Views: 42

Answers (1)

Applecore
Applecore

Reputation: 4099

I think that you will need to run a series of queries (to insert ProductOne Cost/Payable/Retail and ProductTwo Cost/Payable/Retail).

Looking just at Product1, your queries should look like:

INSERT INTO tblCustomer2 
(CustomerNumber, CustomerName, PostingCategory, PostingAmount, Description) 
SELECT
CustomerNumber, CustomerName, "COS", ProductOneCost, "ProductOne"
FROM tblCustomer1
WHERE ProductOneCost>0

INSERT INTO tblCustomer2 
(CustomerNumber, CustomerName, PostingCategory, PostingAmount, Description) 
SELECT
CustomerNumber, CustomerName, "Payable", -ProductOneCost, "ProductOne"
FROM tblCustomer1
WHERE ProductOneCost>0

INSERT INTO tblCustomer2 
(CustomerNumber, CustomerName, PostingCategory, PostingAmount, Description) 
SELECT
CustomerNumber, CustomerName, "Retail", -ProductOneSale, "ProductOne"
FROM tblCustomer1
WHERE ProductOneCost>0

And then follow a similar process for ProductTwo.

Regards,

Upvotes: 1

Related Questions