Combining multiple queries into one

[MilaK]

Hi Steve,


I expect a lot of data in the database and worried that the dlookup method will not work.

I'm trying a different method. I converted your query to "make a table" query and use that table as the row source for the form. However, I'm worried since the table will be constantly deleted and recreated each time the query is executed the relationship to other tables will be impossible to establish.

I'm not sure how this will work if the form is a subform that is liked to another table (e,g, tblSamples). Do I need to establish the relationship programmatically between the main form and the subform (bound to "make a table" query table). What do you think?

Thanks,

Mila

 

[sneuberg]

I expect a lot of data in the database and worried that the dlookup method will not work.

About how much? I can simulate the situation and test it for speed. I really think this should be tried first as the alternatives are really nasty.

I'm trying a different method. I converted your query to "make a table" query and use that table as the row source for the form. However, I'm worried since the table will be constantly deleted and recreated each time the query is executed the relationship to other tables will be impossible to establish.

The bloat problem is nothing and can be corrected by compact and repair on close. The real problem is the code involved in updating the real table from this temporary table. Also if you have a multiuser system then you really can't do it this way as updates by one user will overwrite those of another without warning.

I'm not sure how this will work if the form is a subform that is liked to another table (e,g, tblSamples). Do I need to establish the relationship programmatically between the main form and the subform (bound to "make a table" query table). What do you think?

I don't understand this. Please trying to explain this a different way.

 

[MilaK]

The bloat problem is nothing and can be corrected by compact and repair on close. The real problem is the code involved in updating the real table from this temporary table. Also if you have a multiuser system then you really can't do it this way as updates by one user will overwrite those of another without warning.

Could you please explain what the "bloat problem" is? Also, what's wrong with the code that updates the real table? I'm using an update query that seems to work ok.

There could be a maximum of two users making changes to the "real" table and they will enter into separate user-specific fields (e.g. first reviewer, second reviewer). Each will see either "first reviewer" or "second reviewer" fields on the form based on their log in. Will this work?

Another reason I really like the temp table is because one of the forms will have 8 different subforms bound to it and if the row source will be the temp table I can limit the records to a specific project vs all of the projects and it will load much faster even with a lot of data.

This is a serious project and I'm really worried this set up will fail. I’m new to Access but hoping to make this work.

There is no way to tell at this point how much data we will get in the lab.

Thanks, Mila

 

[sneuberg]

Could you please explain what the "bloat problem" is?.

Let's say the make table query increases the size of the database by one MB. The database will increase in size by 1 MB each time the make table query is run. Deleting the table doesn't return the space. You only get it back when you compact and repair. But given how cheap storage is nowadays 1 MB probably wouldn't be a problem. Even 100 MB might not be a problem except that you would run into the 2 GB limit after 20 runs of the make table query. I suggest you make sure that doesn't happen as there have been reports that exceeding the 2 GB limit corrupts the database beyond repair. If you have a split system and the temp table is in the frontend the this might not be such a big deal as you can easily replace the frontend.

Also, what's wrong with the code that updates the real table? I'm using an update query that seems to work ok.

Nothing I guess. I thought it might be more complicated.

There could be a maximum of two users making changes to the "real" table and they will enter into separate user-specific fields (e.g. first reviewer, second reviewer). Each will see either "first reviewer" or "second reviewer" fields on the form based on their log in. Will this work?

I don't see any problems in that case.

Another reason I really like the temp table is because one of the forms will have 8 different subforms bound to it and if the row source will be the temp table I can limit the records to a specific project vs all of the projects and it will load much faster even with a lot of data.

Well if the bloat can be handled then I guess this way would be best for you,

This is a serious project and I'm really worried this set up will fail. I’m new to Access but hoping to make this work.

There is no way to tell at this point how much data we will get in the lab.

Good luck with your project.

PS: I just had another thought that you might consider. It's probably just the aggregates (the counts) that are cause the update problems. You could probably limit the temp table to just those fields and the fields need to joined it with the real tables.

 

[MilaK]

Thanks Steve,

You are saying that I can find vba code somewhere that will compact and repair database safely each time it closes and thus eliminate the bloat problem?

 

[sneuberg]

Another thought. In this temp table design, yours or the one I just suggested, the counts won't be updated before the make table query is rerun. So unless you are going to rerun it each time the users change data that affect the counts they won't change when the users make those changes.

 

[MilaK]

Another thought. In this temp table design, yours or the one I just suggested, the counts won't be updated before the make table query is rerun. So unless you are going to rerun it each time the users change data that affect the counts they won't change when the users make those changes.

Yes, I was thinking of rerunning the temp table code each time the user accesses the data, but now I'm really worried about the bloat problem and not sure what to do.

 

[sneuberg]

Thanks Steve,

You are saying that I can find vba code somewhere that will compact and repair database safely each time it closes and thus eliminate the bloat problem?

For the frontend that's just an option you need to choose. If you need to compact and repair the backend it gets more complicated as you need to make sure no one has any tables open. If you need to compact and repair the backend let me know and I'll post the code we use.

 

[MilaK]

I'm planning on splitting the database and storing the tables on a network drive. About five people will get a copy of the front end. I'm not sure where the temp table will be stored front/back end and which end I need to repair.

 

[MilaK]

I've done some reading and many suggest creating a side end.

http://www.granite.ab.ca/access/temptables.htm

Is this a good idea?

Thanks,

Mila

 

[sneuberg]

I don't like them as that just one more thing that has to be linked. Also that WEB page is suggesting that your actually create the side end each time you use it and delete it after you are done with it. Somehow I can't picture how that would work in a multiuser environment. Also I think this would be a significant amount of time involved in doing that. If you go that route I suggest leave the side end in place and just do a compact and repair on it occasionally.

I'd put the temp table in the frontend and compact and repair on close. What problems do you see with that approach.

I wonder if it wouldn't be easier just to have permanent tables with these counts and just do what you need to do to keep them up to date. I understand if you are a purist this suggestion might make you gag as it really violates normal form.

I suggest starting a new thread with a question about this current problem and get some input from the other forum members. You should probably put in a reference to this thread.