I have the situation where I need to create a temporary table (I think) that holds records with calculated amounts. I am using data from 2 tables with existing data to create records with similar information. The first table has similar information listed up to 5 times on one row. The second table has similar information listed in other rows. This sounds strange, I know, but that is the way the data feed comes in - and the information is really all different, but I need to break it up, rename it, and create individual rows. So the first table can have up to 5 rows created from one row, and then there may be another row from the second table. The first table can have many original rows that are broken up. I have accomplished this with a series of queries - first a maketable query that makes the table and takes the first set of info from the first table. Then a series of append queries that breaks up the rest of the row,appends it, and then finally, an append query that appends info from the second table, if it meets the criteria. This process brings in existing information. Then I have new data that is input directly into that table through a form.
This method works well - that is, my data is correct, and I have built my forms around this new table, and the reports all work well. But here is the dilemma - sometimes the "old" data is changed by the end-user. This new table is only holding a limited number of fields, mostly calculated. If one change is made to the original table, all of the data really needs to be recalculated in the new table. I know I really do not want to and should not be storing these values in a table.
So here's the question - what is a good way to create some sort of temporary storage place for the information run from this series of queries for just the [ClaimNumber] the user is working on? I have a multi-user environment, with the data stored in a separate database on the server. I guess I am worried that if I use a temporary make-table for one user, as soon as another user goes to run the data for their ClaimNumber, the table will be deleted. Is that true? How else can I do this?