Temporary Tables

[ajzpop]

Hi all, newbie here...


In fact, im new to access also. Havent done a whole lot with it, but i have done a few things.

Anyway, i have been tasked at work to fix a database here at work that doesnt work right. I lobbied to fix it so I want to improve it as well. It is a database that keps track of "widgets". Where they are located, what pocketbook they are paid for, what kind of widget it is, etc. People input via a form all of the required info and hit submit and it is entered into the database. Pretty simple i would think..Some how, some of the info (not all of it) gets duplicated. sometimes once, sometimes 10 times. There is no pattern to it.

I'm gonna start it from scratch. Soooo, my first question is:

based on discusions with the previous builder of the database, he has used temporary tables. why would he have done that?

I plan on pulling info from other databases, would that require a temp table?

[pbaldy]

He didn't tell you? In general, I don't use them. I would in cases where I was importing data and it needed to be "massaged" before being put in the actual table. I've also used one where the users wanted to enter several records and review them in total before committing them.

[ajzpop]

Even if he did tell me, I dont know if i would listen. He almost seems like he really doesnt understand it himself. Maybe he was using it as a way of reviewing before submitting?? Would it be wise to use one for reviewing the data in a form after it has been submitted? I am leaning towards not using one unless I have to. Not everyone is able to modify data.

[pbaldy]

I've certainly worked on apps where the original developer used them for no apparent reason. Like I said, I generally don't, and wouldn't unless there was a compelling reason to do so. I forgot that the "review before submitting" example I had in mind was also a case of the users wanting to enter the data in a de-normalized manner, which was another reason I used the temp table. When they are satisfied with the records, I copy them into normalized tables and empty the temp table.

[Dal Jeanis]

A temp table is useful if you are doing a multiple step process, and if the temp table makes that process much easier to follow. I have one export process that uses several temp tables, and I wrote it that way so that a less experienced programmer coming after me could understand each step independently. Also, a temp table is useful - almost mandatory - if you are importing data that needs to be validated before you append it to the actual table. That doesn't mean that a human will look at the data - it could just be a place where a VBA routine has a chance to automatically kill the bad data.

Rewriting a database from scratch because you don't understand how it works is a good way for you to learn many things, but is not necessarily an effective way to get your company's business needs met. And if you're asking such basic question about temp tables, you probably are aiming a large loaded weapon at your lower anatomy. Been there.

First, identify precisely what data is being duplicated. Where's it coming from? How does it get here? what steps happen in between? If you WANTED to create the duplicate data, where in the process would you put the nasty code? Treat it as a puzzle, and have fun with it. Think like a gremlin!

On the other end, if you really want to rewrite from scratch, start from basic design, go over to Roger's access library at http://www.rogersaccesslibrary.com/ and do his tutorials on database design and application design. Go over to Allen Browne's site and read all his tips http://www.allenbrowne.com/tips.html. Then take another whack at figuring out what's wrong with the prior version.

If you can't figure it out, then determine a modular (chunk by chunk) design, and steal code/forms/queries/etc from the old version one chunk at a time until you either complete your system or figure out what was wrong with the old one. I guarantee you that by the time you have a brand new system up and running, you'll be good enough at Access find the source of the bug in the old system.

[June7]

What Paul and Dal said ...

I seldom have need to import data but I have several temp tables to accomplish complex data manipulation and output that can't be handled only with queries.

I've never used intermediate temp tables for routine data entry/edit.