Quote:
Originally posted by Jethro
I'm with you on this one James .... if running large databases, then they are large by nature no way around it. Which db you using, could generate state tables, and then programmatically generate a new one in the unlikely event of a new state being created......
We run a huge client database, and have fields for country and region. Searches run pretty quickly regardless..... maybe if your frontend is a web page or something could be a problem, other than that l don't really see any other approach. Splitting tables into increasingly smaller units will actually lead you to at some stage having to do joins, which are a) hard to maintain b) don't really allow ad hoc reporting c) making coding a pain in the arse..
As James pointed out, use an SQL on the table to extract just the state records you are interested in.
Umm, joins? Why? Just create a temp table (Select * into ##temptable from permanentTable) and then dump all the other tables into it (Insert into ##temptable select * from permanentTable2). Use ## temptable (for those unaware, that's how you define a global temporary table in SQLServer. For Access, just don't use the ##) in any select statements and drop when done.