Search using 1 textbox through multiple fields

[convexcube]

Hi everyone,

I am developing a database which amongst other things records contacts and related information. This database is structured so that a person can be an 'Employee', 'User', 'Customer' etc or any combination of those. So I have a Persons table, a Users table, an Employees table and a Customers table all related by Person_ID. Persons can also have multiple tags (such as Electrician, Plumber etc), so this is in a separate table also related by Person_ID.

What I would like is to be able to search in a single textbox, the name (or part of name of a person), or the tag of a person and have the results shown in a subform below (actually multiple subforms - in order to separate it out by category - Customer, User, Employee etc)

I have been able to do this but have run into a problem, when searching by name, I will get multiple results in any one category if they have more than one tag. I think I need to restructure the query significantly. I realise that this might not be specific enough for a definitive answer but I'm just after a pointer in the right direction, if necessary I can provide more details.

Any help is much appreciated,

Thanks, Ken.

[mshmyob]

Hello Ken,

Not sure if you have your table structure proper. Sounds to me like you have data redundancy. Could you show your exact table structures with fields and primary and foreign keys.

cheers,

Hi everyone,

I am developing a database which amongst other things records contacts and related information. This database is structured so that a person can be an 'Employee', 'User', 'Customer' etc or any combination of those. So I have a Persons table, a Users table, an Employees table and a Customers table all related by Person_ID. Persons can also have multiple tags (such as Electrician, Plumber etc), so this is in a separate table also related by Person_ID.

What I would like is to be able to search in a single textbox, the name (or part of name of a person), or the tag of a person and have the results shown in a subform below (actually multiple subforms - in order to separate it out by category - Customer, User, Employee etc)

I have been able to do this but have run into a problem, when searching by name, I will get multiple results in any one category if they have more than one tag. I think I need to restructure the query significantly. I realise that this might not be specific enough for a definitive answer but I'm just after a pointer in the right direction, if necessary I can provide more details.

Any help is much appreciated,

Thanks, Ken.

[convexcube]

Hello Ken,

Not sure if you have your table structure proper. Sounds to me like you have data redundancy. Could you show your exact table structures with fields and primary and foreign keys.

cheers,

Thanks for your response mshmyob,

My Table structure is as follows

Persons
Person_ID (PK)
Person_NameFirs t
Person_NameLast

Users
User_ID (PK)
User_Password
User_Level
Person_ID (FK)

Customers
Customer_ID (PK)
Person_ID (FK)

Employees
Employee_ID (PK)
Employee_DOB
Person_ID (FK)

Tags
Tag_ID (PK)
Tag_Detail
Person_ID (FK)

Form_Central Query
SELECT Persons.Person_ NameFirst, Persons.Person_ NameLast, Tags.Tags_Detai l
FROM Persons INNER JOIN Tags ON Persons.Person_ ID = Tags.Person_ID;

Form_Central Filter
Person_NameFirs t Like Forms!Central.t xtSearch & "*" Or Person_NameLast Like Forms!Central.t xtSearch & "*" Or Tags_Detail Like Forms!Central.t xtSearch & "*"

There are other tables that are related to these but I didn't include them because they don't affect this query (which is why the 'Customers' table may look pointless, and in fact only the Persons & Tags table are relevant to the query). As far as I can see my data has been normalised and there is no redundancy. I do understand why I am getting multiple results when searching by a persons name if they have more than 1 tag (because multiple records match the criteria), but what I would like to know is how I can display only 1 of those matching records (since I only see the name, I only need to see it once).

Please let me know if you need more info and thanks for your time, it is much appreciated.

Regards,
Ken.

[mshmyob]

Try the following

[code=vb]
SELECT DISTINCT tblPerson.Perso nFName, tblPerson.Perso nLName
FROM tblPerson INNER JOIN tblTag ON tblPerson.Perso nID = tblTag.PersonID
WHERE tblPerson.Perso nID= tblTag.PersonID ;
[/code]

Again looking at it I still think you have the design a little messed up. For instance Your two tables PERSONS and TAGS. Sounds to me like this should be a many to many. For instance

Each PERSON can have many TAGS
and
Each TAG can be had by many PERSONS

Therefore a Briodge table is needed between them.

The way you have it you are creating a new record in the TAGS table for every person that is an Electrician, Plumber, etc.. You are therefore duplicating TAGS. If this is correct then putting the bridge table in will solve redundancy problems and make your queries work without problems.

I haven't looked at the other tables.

cheers,

Thanks for your response mshmyob,

My Table structure is as follows

Persons
Person_ID (PK)
Person_NameFirs t
Person_NameLast

Users
User_ID (PK)
User_Password
User_Level
Person_ID (FK)

Customers
Customer_ID (PK)
Person_ID (FK)

Employees
Employee_ID (PK)
Employee_DOB
Person_ID (FK)

Tags
Tag_ID (PK)
Tag_Detail
Person_ID (FK)

Form_Central Query
SELECT Persons.Person_ NameFirst, Persons.Person_ NameLast, Tags.Tags_Detai l
FROM Persons INNER JOIN Tags ON Persons.Person_ ID = Tags.Person_ID;

Form_Central Filter
Person_NameFirs t Like Forms!Central.t xtSearch & "*" Or Person_NameLast Like Forms!Central.t xtSearch & "*" Or Tags_Detail Like Forms!Central.t xtSearch & "*"

There are other tables that are related to these but I didn't include them because they don't affect this query (which is why the 'Customers' table may look pointless, and in fact only the Persons & Tags table are relevant to the query). As far as I can see my data has been normalised and there is no redundancy. I do understand why I am getting multiple results when searching by a persons name if they have more than 1 tag (because multiple records match the criteria), but what I would like to know is how I can display only 1 of those matching records (since I only see the name, I only need to see it once).

Please let me know if you need more info and thanks for your time, it is much appreciated.

Regards,
Ken.

[convexcube]

Try the following

[code=vb]
SELECT DISTINCT tblPerson.Perso nFName, tblPerson.Perso nLName
FROM tblPerson INNER JOIN tblTag ON tblPerson.Perso nID = tblTag.PersonID
WHERE tblPerson.Perso nID= tblTag.PersonID ;
[/code]

Again looking at it I still think you have the design a little messed up. For instance Your two tables PERSONS and TAGS. Sounds to me like this should be a many to many. For instance

Each PERSON can have many TAGS
and
Each TAG can be had by many PERSONS

Therefore a Briodge table is needed between them.

The way you have it you are creating a new record in the TAGS table for every person that is an Electrician, Plumber, etc.. You are therefore duplicating TAGS. If this is correct then putting the bridge table in will solve redundancy problems and make your queries work without problems.

I haven't looked at the other tables.

cheers,

Thanks for that, I can see how a many to many relationship can benefit my situation and I will try that. I'll post on this thread again if I still have problems.

Thanks very much for your help.

Regards,
Ken.