Search Across Multiple Tables

[Tomfernandez1]

Not sure if this is the right place to post this, so forgive me if it is not.

I have a 2003 Access database that has multiple Tables. Each Table has similar fields titled "First Name"; "Last Name" in addition to other uncommon fields.

Is there a query or code that would search, across all Tables, for "Bill"; "Smith" respectively in those common fields?



I have a Form set up where I can set up the query/code if necessary.

Your attention to this post is much appreciated!

Tommy

[SteveF]

Not sure if this is the right place to post this, so forgive me if it is not.

I have a 2003 Access database that has multiple Tables. Each Table has similar fields titled "First Name"; "Last Name" in addition to other uncommon fields.

Is there a query or code that would search, across all Tables, for "Bill"; "Smith" respectively in those common fields?

I have a Form set up where I can set up the query/code if necessary.

Your attention to this post is much appreciated!

Tommy

A few questions, if you don't mind -- to start, is the "Bill Smith" in table #1 the same person as "Bill Smith" in table #2, table #3, and so on??

If so, then I'm going to strongly recommend a thorough table redesign, as there's a lot of redundancy here and a lot of chances for data errors that will really screw things up down the road!

Steve

[Tomfernandez1]

Hi Steve- "Bill Smith" is the same person in multiple Tables. I agree that my database has flawed Table designs, and I hope to rectify this in the coming months. I have been reading up on nomalization; I hope to get my database normalized soon!
-Tommy

[ajetrumpet]

Tom,

no one here has answered your question yet, soooo....the answer is YES. But what is the point? What do you want to do with all of it? Delete all the dups?

[Tomfernandez1]

Adam - the Tables represent different months of the year (again, bad design) so I want to know the number of months "Bill" "Smith" is found in a year, and review data from some of the different fields within the Tables that show "Bill Smith".
-Tommy

[ajetrumpet]

Adam - the Tables represent different months of the year (again, bad design) so I want to know the number of months "Bill" "Smith" is found in a year, and review data from some of the different fields within the Tables that show "Bill Smith".
-Tommy

I see. there isn't a query that does this. that would be 12 tables, rigth? you could write nested IIF() with dlookups in them for each table, but I'm guessing 1,000 records in each of 12 tables, that would take minutes to run. So that's out.

Why not a VBA call using dcount() to count the distinct values in one of the tables that you query? that might be an option, although speed might be an issue. depends on size.

regarding your 'bad design', forget it. I just get so sick of people preaching about normalization. It's like a PHD program...it shoulda shoulda shoulda been done this way. Well guess what? I've never had a customer say this to me:

I'm only gonna use your service if your database that you use for my info is normalized

but I HAVE had someone say this:

if you cant get it done in a hour, forget it

hmmm....yeah, forget the customer. normalization is more important to me.

[Tomfernandez1]

Adam - what is the preferred method for using dcount() in a query....would it be in SQL or query design view? Also, is there a VBA method for searching all tables for specific information (Bill Smith)? I was hoping for a code that would work like a 'ctrl+f' function, covering all tables, but I am discovering that such may not exist.. Thanks for your continued support!
-Tommy

[ajetrumpet]

i don't think ctrl+f works in all tables. just columns and/or one table.

as far as FINDING info in tables? that's really kind of pointless in vba unless you want to do something with it. you say you want 'review' some related info of all the Bill Smith's, so if you want to do that, I really don't think there's a fast way to do it.

you could use recordsets and iterate, but you'd have to do it in every table. you could also write one query per table, which is interface, so you don't have to mess with code.

and of course, if the tables could be joined somehow, you could use that join in a qry, but then again, from what it sounds like, the design problem will prevent you from doing it this way.

it doesn't take me much time to write vba code, so I would iterate the tables using an array of table names and recordsets, but it's up to you here though.

[pkstormy]

If you have multiple tables with First name and Last name fields and you want to match names between tables, have you considered creating a text field in each table to save a 'concatenated' type of value. For example, when updating the first or last name on the form (probably along with another field such as city or zip), you could combine and save characters from each of these fields into a single field to make matching across tables a little easier (ie. JSmithParkSt50322).

It could be a quick table/code modification without having to redo any table relationships and creating queries to find matching names would be a bit easier versus joining on mulitple fields (and writing vba to open recordsets to find matching records on a single field would be a little bit easier.)