A form with textbox to search names in three diffrent tables (1 Viewer)

[musmoniem]

i had three tables of members data in a database .. i want to make a form in it a textbox i type a name in it it searches in these three tables .. what could i do ?? I need help urgently

 

[plog]

Why is member data in 3 tables? The correct solution is to get all your data into one.

You could also use a UNION query (https://www.w3schools.com/sql/sql_union.asp) to get all the data together.

 

[isladogs]

First of all, you could redesign your database so you don't have three tables of members data.

Failing that, create a UNION query to combine all members names into one query. Now base your search form on that query

 

[musmoniem]

First of all, you could redesign your database so you don't have three tables of members data.

Failing that, create a UNION query to combine all members names into one query. Now base your search form on that query

how to base the search query

 

[The_Doc_Man]

First, understand that it is extremely rare for it to be correct to have three separate fully-detailed lists of people in the same database. Your design IS going to cause you problems on that factor alone, and I can say that without any other knowledge of your table structure. If all three tables WERE identical in structure, they should merged into one table, in which case you would do a much simpler search later. This is a case of "pay me now or pay me later" but TRUST me - there is a price to be paid.

Having offered the obligatory warning, you still have the problem right now of implementing your search before you can fix the database. I have to assume for the sake of this discussion that for some reason, you think you cannot immediately implement that merger of tables.

If so, it would imply that these three tables are structurally different in some way, so much different that you cannot use the same exact search on all three tables. This is where your UNION query comes into play.

To accomplish a UNION-based search, you must prepare to search ONLY on the parts they have in common. So if the point of your search is to find the person but then look at table-specific details, you are STILL being driven by the structure. In my region of the USA, this is called "the tail wagging the dog" because you are being forced into extreme measures by the structure.

To search these tables through their COMMON parts, you do something like this (and it is intended to be ILLUSTRATIVE, not precise). You said three tables, so you would have to build a static, named query that is the UNION of three similarly structured SELECT statements:

SELECT FirstName as FName, MiddleName as MName, LastName as LName, IDNumber AS PrsnID, "A" AS SourceTable FROM tableA
UNION
SELECT NameF, NameM,  NameL, PersonID, "B" FROM tableB
UNION
SELECT FNm, MNm, LNm, PID, "C" FROM tableC ;

What the above shows is that:
(1) It doesn't matter that the three different tables have three different sets of field names, you reference them by using the field names in each table. (AND if the field names are the same, it still doesn't matter.)
(2) Because of different structures, you might end up having to know from which table the reference came so that you can look up your details from the appropriate table. (That is why field SourceTable is present.)
(3) You probably should assert names via the AS clauses for the first of the SELECT statements but don't need to do that for the subsequent SELECT statements.
(4) IF it is not possible to find even this much commonality so that you COULD do this kind of UNION query, then you have already boxed yourself into a corner from which there is little chance of escape.
(5) When you do the search, you write it against the named UNION query using the names presented in the AS clauses for that first SELECT, so that would be fields FName, MName, LName, PrsnID, SourceTable.

 

[arnelgp]

instead of letter, used the real tablename so you can use it when you construct a New query to fetch additional data.