Split record to multi record in new table

william_murray-44 · December 2nd, 2015, 06:18 PM

Hello I wonder if anyone can help with a challenge I have? I'm looking to take a currency value and divide it by a number and the assign the split values to a date variable (1 month). EG: Single record data with unique ID sales value Â£500,000.00, number of deliveries 10 and first delivery date 3/6/2016 (there is also other information the the table record) and create a new table with 10 rows for this unique ID.

I.E. Row 1 = ID number, Â£50,000.00, 3/6/2016
Row 2 = ID Number, Â£50,000.00, 3/7/2016
Row 3 = ID Number, Â£50,000.00, 3/8/2016

Etc

Is there an easy way to do this?

Sorry if the explanation isn't great.

Regards

malcolmdixon · December 3rd, 2015, 04:12 PM

Hi William,

If you create a table that contains just numbers [Number] from 0 to maximum number then create an append query similar to the following:

Code:

INSERT INTO Deliveries ( SaleID, DeliveryDate, DeliveryValue, [<NoOfDeliveries] )
SELECT Sales.SaleID, DateAdd("m",[Number],[FirstDeliveryDate]) AS DeliveryDate, [SaleValue]/[NoOfDeliveries] AS DeliveryValue, Numbers.Number
FROM Sales, Numbers;

If NoOfDeliveries is not stored in the Sales table then you can still use this and be prompted.

The alternative is to use VBA to create the records using a For loop and DAO commands or just execute a built up string of SQL.

HTH.

Malc.

william_murray-44 · December 3rd, 2015, 07:48 PM

Thanks Malc, I'll give it a try at the weekend, I think it might be a loop though so may be back for some more tips

cheers W