I can't seem to figure this one out...

[cnaccio]

I've written an application for a client which handles the labeling of products on a conveyor. I'm currently writing a report that will tell you how many of each product was processed on a given day of the week. This report is generated by item, by scheduled ship date. The problem is that some orders will have processing dates that span several weeks. The report works perfectly fine if the processing dates are within the same week as the scheduled ship date; however if the processing dates go beyond that given week the report is invalid.

I'd like it to simply create an additional table for each week of processing. In the database I have a table for scheduled ship date, and processing date which is stamped upon processing. I've been able to get the number of processing weeks for a given order, however I'm stumped as to the next step. Any information is much appreciated. Please let me know what additional info will help you guys help me. Thanks!

[etully]

You don't need to create any additional tables or fields. In fact, you shouldn't because this is just redundant information.

All you need to do is look for any items that started before (or during) the week (or day) in question and ended after (or during) the week (or day) in question.

Not only does this simplify your task of finding appropriate matches, it keeps your database clean. You should never be adding extra junk to your database for the sole purpose of making your coding easier. It will make your coding easier ONE TIME and then it will make maintenance of your application a nightmare for years onward.