searching same field data over 6 tables

[ImLost]

Hello Ladies and Gents -

I am always appreciative of your help and hope you can help me out of this hole I've gotten myself into.

I have six seperate, unrelated tables (with correlating forms) pertaining to police data, and need to create a search field on a form on which the officer can enter an existing police report number and then open the appropriate form. Each police report number will be unique, but I cannot figure out how to do this. Searching across related tables or single tables? Fine - but I'm pulling my hair out on this one. The best I've been able to come up with, and feel free to laugh, is to create seperate queries for each report number on each table to equal the form field [text4] and then run a macro with 6 entries for OpenForm set to each form and corresponding query. The problem with that is that all 6 forms open - five blank and one with the appropriate record. I tried setting conditions on the macros so that the form would open only if the criteria is met. However, since I'm searching across a query's-worth of fields per "openform" request, all it does is prevent that entry in the macro from working at all. I'm sure I've made this much harder than it needs to be, but after staring at it this long, I'm ready for a deep breath and some much needed help.

So my question, after all that, is how can I make an end-user search feature search a field that appears in 6 seperate, unrelated tables? I need to do more than just this, but figured if I can get point back in an upwardly direction, I can take it from there. I'm not quite sure which way I'm pointing right now.

Thank you for your time and help - you guys are always great. Hopefully I don't sound like a total dumba$$. ^_- lol

Kastie

 

[Beetle]

I have six seperate, unrelated tables (with correlating forms) pertaining to police data, and need to create a search field on a form on which the officer can enter an existing police report number and then open the appropriate form.

Why do you have six separate, unrelated tables with (what sounds like) the same type of data?!

 

[plog]

I second what Beetle says. And of course have my own comments as well as a solution.

There should exist in your database a table that has all your police report numbers. Anything else is improperly structured.

Onto the solution.You can create a UNION query (http://www.tizag.com/sqlTutorial/sqlunion.php) of Police report numbers. Essentially its a query that merges data from various sources into one source. This is an outline of what yours would look like:

SELECT PoliceReportNumber, "TableName1" AS Source FROM TableName1
UNION ALL
SELECT PoliceReportNumber, "TableName2" AS Source FROM TableName2
UNION ALL
SELECT PoliceReportNumber, "TableName3" AS Source FROM TableName3;

This will give you a listing of all report numbers as well as the table that data is in. Then use that query to perform your search on.

 

[ImLost]

Hindsight is 20/20. This was rush job, with a lot of pressure from the client to get it done as quickly as possible. I needed certain things to work certain in ways, so seperate tables seemed the best way to do it. I pretty much put it in according to the paperwork that was given to me and the client didn't indicate a need for anything in-depth. Everything worked until I needed to search. Everything else is done - all the reports, queries, macros, and VBA coding, so I have to weigh the time of patching this search feature together vs. going back and doing the database the way it should have been done properly from the get go. I've never done one this way - I always plan it out ahead of time without replicating data across tables. Shame on me, I know. Lesson learned, that's for sure. But I hope I can get this search to work with the union query. I need to get this out the door to them to use.

Thank you guys - I'll check out the union query and if all else fails...I guess I start over and do it right like I should have done.

 

[ImLost]

You guys are awesome as always. Thank you, thank you, thank you! The Union Query worked perfectly.