Creating related data

[Gazzieh]

I have a table where a single field holds a quantity. I want to create in another table related fields that match this quantity.

Let me explain. I have a field called Number of Days. If the user enters 3 I want to create in another table 3 fields, related to this record in this table, each new field to contain the next available 3 working days.

I enter 3 on Wednesday 24th January 2007 into the field Number of Days in the ORDER table. I want the other table to have 3 records, each one with the ORDER ID to form the relationship but each one containing the next working day along.

Thurs 25th Jan, Fri 26th Jan, Mon 29th Jan.

I can visualise this in VBA but I believe this should be undertaken in SQL. Anyone able to help?

 

[Remou]

If you wish to do this in SQL, I think the best bet may be to use R937's idea of a calendar table. The calendar table contains each day of the year and any relevant flags or notes, 'holiday', 'weekend' etc. You can then select the next three days from this table and use an append query to add them to the Order table.