Results 1 to 5 of 5
  1. #1
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875

    Question About Temp Tables

    Hey all!



    Because of the HORRIBLE design of our primary work database, I've started using temporary Tables to store data on some of the more complex processes. I was wondering when, generally speaking, temporary Tables should be used and how they should be designed.

    • How difficult/complex do you let things get (in terms of programming/Querying) before you should use a temporary Table?


    • Should temporary Tables follow standard normalization rules or should you "cut out the middle-man" so to speak and set it up to hold EXACTLY what you're going to end up using? For example, if I have a 25 character text description field, should I use the ID pointing to that field and set up a relationship, or go ahead and just save the actual description? What if I end up using this same description in multiple (say virtually all of the) Records saved to the temporary Table?


    • What about saving user input to the temporary Table (instead of trying to save it to a variable - or keeping the input Form open and reading from there)?

    Since the database is already horrible designed, and this is a temp table that will be wiped after each use, being less than perfect isn't a huge issue, but I'd still like to hear your input.

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    Temp tables are avoided, in terms of not being the 1st method for a solution. But one can't be too dogmatic.

    I don't see normalization as an issue at all - they are tactical scratch pads designed to the need.

    I have found that making/deleting the table object itself causes bloat; and instead I keep the table permanent and do append/deletes of records only. Although I've seen advice just the opposite on this point.

    In one case I had situation where there was a very advanced query before a report would generate. The user had several versions of the report they viewed - and for each it would rerun this elaborate query. The delay annoyed them. So I instead wrote the query result to a temp table & resourced the results. Then after the 1st initial delay - all future report views were instantaneous.

    I also use temp tables regularly in imports where the data, once imported, needs some manipulation before final append to the real table.

    So when one is solution oriented sometimes a temp table is needed, but generally I will always work with query record sets alone. Hope this helps.

  3. #3
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    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?

  4. #4
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    they're there. so leave them. normalization is the ideal - starting with a clean slate. but you aren't starting with a clean slate. so simplicity counts too. to add extra manipulation to remove things for the sake of theoretical normalization doesn't strike me as wise. keeping it simple counts too. so I would just leave them.

    memory is no longer an issue and so there really is no down side to keeping them that I see.

  5. #5
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Good point. I hadn't really thought about it that way. . .

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query on related tables question
    By jpkeller55 in forum Access
    Replies: 12
    Last Post: 09-28-2010, 07:18 PM
  2. Query with related tables question
    By jpkeller55 in forum Access
    Replies: 4
    Last Post: 09-25-2010, 04:29 AM
  3. make a temp table from crosstab query
    By stigmatized in forum Programming
    Replies: 0
    Last Post: 07-26-2010, 03:01 PM
  4. database and tables question
    By aaronlalonde in forum Database Design
    Replies: 0
    Last Post: 08-03-2009, 06:51 PM
  5. Simple Question about Referring to tables
    By KIDRoach in forum Access
    Replies: 4
    Last Post: 06-19-2009, 07:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums