Anytime you search a table and not a field, the search is going to take a very long time. I agree with you that searching 13 separate tables will take longer, but you are probably going to have to prove it to him. I would setup a test where I tried his example with only 2 or 3 tables and extrapolate on the results.
Stick in a timer in your code at the beginning and the end. Debug.print Now() Dawn Crosier Application Specialist "Education Lasts a Lifetime" This message was posted to a newsgroup. Please post replies and questions to the group so that others can learn as well. -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Cathy Sent: Wednesday, March 01, 2006 8:39 AM To: [email protected] Subject: [ms_access] does this scenario make any sense? Hi, I am supposed to create a database that makes searching 13 tables with a total of 650,000 records quicker. The tables do not all have the same fields or field names unfortunately. What I have done through a query is add two new fields called source (the name of the table) and autonumber to make each record unique, rename the fields with a consistent field name that need to be searched (and not all need to be), and then combined all those records with the new fields into a master table with a union query. A main search form takes the search input from the user and displays the list of sources (tables) that have records meeting the criteria. A zoom button next to each source opens up another form that shows the original records in that table by either continuous forms or datasheet. There are buttons to print, exit, etc. This basically meets the needs of the users I was told, except that the query takes too long to run! But I'm not sure anything can be done to speed up searching that master table, not significantly anyhow. The suggestion from my boss was from the main search form to have each table searched individually. I guess he meant that the search criteria would be entered once and then 13 separate queries performed? Before I even attempt this my intuition is that it would not cut down on the time in fact it would probably be longer! Can anyone tell me if his suggestion makes sense? I'm not trying to avoid work, I just hate wasting time but I haven't got a good enough argument against his idea. Thanks! Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
