I have a set of 66 tables which are the 66 books of the bible. When I perform a "search" I want the search to go through all the 66 tables. What category is this considered?

If you can do a stored procedure, that would be best. But why do you have to go through all 66 tables? Shouldn't the user select what types of data they want to search?

In SQL, you can also use the "UNION" clause between statements to join the result sets together. The only catch is that each statement must have the same result string (same number of columns with the same column names).

this is in the category of "sub-optimal design"

you will need 66 queries, which could be combined with UNION, but nevertheless each table wouyld be queries separately

by far, a better approach here would be one table instead of 66

I would rather have 66 tables because I want to leave the option or set of options to the viewer.

Can the come in an "if" statement? If so how would the code be written?

leave the option to do what? why don't you put them all in one table, and have a column that designates what type of data this is.. then you can simply include that data code, or combination of data codes (based upon user selections), in your query.. I like that idea much better...

How would that work?
I have a db like that and the search page is like this:

http://wheelofgod.tripod.com/ASPSearchamos2.htm

How would I be able to use the radio buttons (to search the entire db or to select where to search)?

that page works very easily with one table

each book gets its own id, and i would suggest sequential numbers 1 through 66, if in fact that's how many there are (sorry, don't know)

your radio buttons (The entire King James Bible, or Your selection of books) would be used to tailor the sql

(nice, by the way, how the "entire" option grays out the checkboxes for the various books)

if selected individual books are checked, you pass those id numbers to the query, as in

SELECT ...
FROM bibletable
WHERE ...
AND bookid in ( 3, 24, 47 )

Ok.. take all 66 tables, find a common schema between them all, and create a new table (based on that common schema), and add a new column called "recordType". It'll be a character field.. how long is up to you, but 2 characters should be enough. Now when you insert from the 66 tables into the one table, you'll include the record type (Example: table "book" may have a recordType of "bk", and psalm might be "ps", etc).

Now take your radio buttons and apply the recordType values to their respective radio buttons (or checkboxes for that matter - this is what you'd use if you want to be able to query multiple record types at the same time).

In your ASP, you'd take the recordType from the form and apply that to your SQL:

SQL = "select * from bibleTable where query LIKE '%" & Request.Form("Query") & "%' AND recordType = '" & Request.Form("recType") & "'"

Now, if you use checkboxes, you'll have to Split the Request.Form("recType") value on the "," character into an array, then loop through it adding multiple "OR recordType..." clauses to the SQL

Edit: I may be off on my radio button names, my office blocks tripod, but the idea is roughly the same

nice post, Seppuku

book number, book code, recordType -- it doesn't really matter, does it

(omigod, did i just accidentally open up the surrogate-versus-natural can of worms? )

.
OR, shurely

Eeps! Righty-o...

I want to convert the column number to 001,002,003, 004 but it automatically converts to 1,2,3,4. If I use the search to search for numbers example:
search 1
result would be 1, 10,11,12,13...

But if I would search 001
result would be 001

the field is probably a character field then... you should make sure it's a number field..

Is there a easy way to fix that by Excel or MSAccess?

By the way I'm doing what you said and it's easier than I thought. Thanks.

If you're doing this in Access, all you should need to do is change the column type to an integer.

Isn't normalization a wonderful thing? You'll save yourself a lot of headache this way.

I'm a newbie and I don't know the terms like looping.

This is what I have so far: