I agree that temp Tables should be avoided whenever possible. Unfortunately, the database we use here is so unspeakably - horribly - designed, that I routinely have problems unless I use a temp Table. This will be the fourth temp Table solution that I've set up since I started working here.
As for the deleting/recreating the entire Table itself, I agree with you. I always have the Table in the system, I just delete the Records.
As for the question of normalization, what about a situation like this:
Example TempTable
Code:
ShopOrder|LineNumber|GroupNumber|PartNumber|OptNumber|Description
123456|1|12345|123|01|1/8in Clear Glass
123456|2|12345|123|01|1/8in Clear Glass
123456|3|12345|123|01|1/8in Clear Glass
123456|4|12345|123|01|1/8in Clear Glass
123457|2|12345|123|01|1/8in Clear Glass
In the above example, the first three fields of each Record are combined to form the primary key (so they would have to be there no matter what). And, because the PartNumber may not actually be the PartNumber listed in the shop order (long story), it really needs to be there too. But what about the remaining fields?
OptNumber is a number that the user enters at execution time. And, as long as GroupNumber doesn't change, OptNumber won't change either. Should OptNumber be included in the Table or not?
Also, what about the Description Field? That can be found by looking up the appropriate PartNumber in the GlassMaster Table. And, like OptNumber, it won't change either.
Should I leave those out and just "calculate" them by referencing the appropriate Tables/Forms or should I go ahead and include them?