Create New Records

[suzreid]

I have a table that includes a 'from date' and a 'to date'. If the difference between these two dates is greater than one day then a record has to be created for each day and the subsequent new records will be appended to another table. The ID of the 'parent' record must be stored in the secondary table as well as a unique id for the new record.

I have no idea where to start with this, can anyone help?

Suz [sig][/sig]

 

[TheInsider]

Hi,
The first question that one should ask is: Do I really need to create a record for each day? Or do I already have the ability to compute this information at a later time, even if it is a little more difficult?

I don't know what data you will be storing in these daily entries, other than the parent ID and the unique ID, but a good rule of thumb is:

If records can be created without a single field requiring user input, then you either are storing redundant data or the results of calculations.

(Probably true 99% of the time. The only exception that I can think of is if you had a table that held an autonumber and the current system date and time.)

You could get this information at a later time from the data that you have already collected. If you had 10 parent records and each had an elapse of 10 days; you will end up with 110 records! It may be easier to calculate the information now than later on, but at what cost? 100 records?

I would suggest that you re-think your design... but if you find that I am wrong then...

-Find the elapsed time in days with the DateDiff() function
-Create a loop that appends a record for each day returned

That is about as much as one can suggest without knowing more details.
Rob Marriott
CCTC1
rob@career-connections.net
[sig][/sig]

 

[suzreid]

Rob

The new records that I am creating split a value entered in the parent table into proportional slices. The users are then given the opportunity to change values in the child records which then recalculate the total value in the parent record.

Thanks for the suggestion about how to do it though. It has given me a starting point.

Suz [sig][/sig]