>Thomas,
>
>Thanks.
>
>I have heard of xcase and heard it is great. Haven't ever yet had time to check it out. It's on my list to do. If it helps me visualize what I am trying to accomplish that will be great.. because in this case I'm thinking there must be a 'standard' way to do something like this.. i.e. a standard methodology, or most efficient approach, but I don't know what that is.. maybe the tool will help me figure it out on my own.
>
>For now I am starting to write code based upon the example I gave and trying to figure out if I have the right data structure to do what I want. I will write back when I've fleshed it out a little further.
I've tried xCase once, maybe seven years ago, and it was OK then for a moderate number of tables. For anything over, say, two dozen tables, ANY visual tool would be too unwieldy.
Back to your case - you may just have about three transaction tables (sales header/detail, projections) and a few lookups (periods like weeks, quarters, years; regions, salespersons) and that'd be pretty much it. However, what you may need more is a flexible way to build queries and save them. You may need a bunch of similar SQL statements, maybe even chopped into pieces and assembled as needed - pieces for the list of fields, pieces for the where clause, pieces for the group by. Then run the assembled pieces as scripts (or SQL server scripts - it would work pretty much the same).