MS Access 2010 Multiple Search Form

[tb199]

Happy New Year Everyone!



I am pretty new to MS Access as I have not had to use it since 2006!

I have the tables set up but need to create a form which is able to search multiple tables. The idea behind my database is to have a form which can search peoples skills. The database will contain records of individuals and the skills they hold. E.G I want users to be able to search Phone and then everyone with phone skills appears. I also have other things I want people to be able to search for. Please see below the names of my tables and the fields below. If someone could help me it would be greatly appreciated:

COULD SOMEONE ALSO HELP ME OUT WITH THE RELATIONSHIPS TOO PLEASE??

Staff Table:
Staff ID
Title
First Name
Surname
Tel
Email
Location ID

Location Table:
Location ID
Location Descriptor

Skills Table:
Skill ID
Skill Descriptor

Theme Table:
Theme ID
Theme Descriptor

Junction Theme Staff Table:
Staff ID
Theme ID

Junction Staff Skills Table:
Staff ID
Skills ID

[rpeare]

From this description your tables look fine, assuming your ID fields are autonumbers and not something people can change through data entry.

You don't need a multiple table lookup, however. If you're looking for what skills a person possesses or, alternately, what people possess a certain skill you can create a query for either and add criteria to that query (once you have it working) to limit what it displays using something like

[Enter staff surname]

in the criteria if you're looking for a specific person's information

[A S MANN]

Dynamic Multi Search.zipFor dynamic Search Try the DB as sample

[tb199]

From this description your tables look fine, assuming your ID fields are autonumbers and not something people can change through data entry.

You don't need a multiple table lookup, however. If you're looking for what skills a person possesses or, alternately, what people possess a certain skill you can create a query for either and add criteria to that query (once you have it working) to limit what it displays using something like

[Enter staff surname]

in the criteria if you're looking for a specific person's information

Thanks for the reply so fast! So the tables are fine? What relationships do I need? Are you able to help me with that? I want it to be searchable by everything apart from the surname.

[tb199]

Thanks for getting back to me on this. The multi search looks amazing and is exactly what I require, howevere I have no idea how to incorporate my requirements above into that format

[rpeare]

You don't really need relationships to be set up in your database, some people prefer them, but I do not. I never have a relationship diagram set up in my databases.

The only real issue I see with a search of all of your data is that, from your setup, a person can have multiple themes and multiple skills but the themes and skills are not related to one another which will likely make your query results blow up (reflect a larger number than it should)

You may have to build 2 or 3 different queries to show what you want and pull up the appropriate query based on selections made through a form.

If there's a 1 to 1 relationship between staff and themes or there is some relationship between themes and skills you can probably still do it with one query, I'm just not sure what your themes are since they aren't mention except as part of your table structure in the original post.

[tb199]

You don't really need relationships to be set up in your database, some people prefer them, but I do not. I never have a relationship diagram set up in my databases.

The only real issue I see with a search of all of your data is that, from your setup, a person can have multiple themes and multiple skills but the themes and skills are not related to one another which will likely make your query results blow up (reflect a larger number than it should)

You may have to build 2 or 3 different queries to show what you want and pull up the appropriate query based on selections made through a form.

If there's a 1 to 1 relationship between staff and themes or there is some relationship between themes and skills you can probably still do it with one query, I'm just not sure what your themes are since they aren't mention except as part of your table structure in the original post.

Thanks for the reply. I will get rid of the relationships for now then. Lets call the themes A,B,C,D,E,F for now as it is confidential. The searches I want is for someone to say for example they want to see everyone who has skill 1 and it shows all those people. I also want it to show say everyone in theme A and a list displaying all those people. I would also like maybe a query saying show people with skill 1 in theme A.

I am a total novice in Access so have very little knowledge on where to go. Is there any chance you could help me out? I have atached what I have so far

[rpeare]

Are you looking to perform these searches on a form, or to be able to print a report? what's your intended output, the method varies.

[rpeare]

Yuu have no data in your tables, nor any sort of mechanism to get it in there (a form bound or unbound) if you want to put some fake data into into it so I have some idea of what your data might actually look like on your live data that would be more helpful.

[tb199]

It needs to be a form. The data is to be input at a later date due to its sensitivity. People will email their info and I will put it in the database. The form will simply be the front standing page where people can search. The rest will be locked.

[rpeare]

your actual data may be sensitive, but you can certainly populate your tables with a simulation of what your data will look like, I do not want to have to populate it just to work on your example

[tb199]

your actual data may be sensitive, but you can certainly populate your tables with a simulation of what your data will look like, I do not want to have to populate it just to work on your example

Sorry, I hadn't uploaded the mock data one. Please find attached.

[rpeare]

Ok your two junction tables need a primary key of their own so you can refer to them unqiuely.

Now my question is this (I don't see that it's been answered in your posts), what is the relationship between themes and skills?

If there is no relationship can a person have more than one theme? Your table doesn't have multiple people with the same theme but it really makes a difference in how you would construct a query/queries to handle it. I have to assume that a single person CAN have more than one theme because you have the junction table. If each person can only have one theme why are you carrying it in a separate table? and not on the staff table?.

Based on your current structure and assuming a single person CAN have more than one theme this is the type of query you would want:

SELECT [Staff Table].[Staff ID], [Staff Table].Title, [Staff Table].[First Name], [Staff Table].Surname, [Staff Table].Tel, [Staff Table].Email, [Staff Table].[Location ID], [Location Table].[Location Descriptor], [Junction Theme Staff Table].[Theme ID], [Theme Table].[Theme Descriptor], [Junction Staff Skills Table].[Skills ID], [Skills Table].[Skill Descriptor]
FROM (((([Staff Table] LEFT JOIN [Location Table] ON [Staff Table].[Location ID] = [Location Table].[Location ID]) LEFT JOIN [Junction Theme Staff Table] ON [Staff Table].[Staff ID] = [Junction Theme Staff Table].[Staff ID]) LEFT JOIN [Theme Table] ON [Junction Theme Staff Table].[Theme ID] = [Theme Table].[Theme ID]) LEFT JOIN [Junction Staff Skills Table] ON [Staff Table].[Staff ID] = [Junction Staff Skills Table].[Staff ID]) LEFT JOIN [Skills Table] ON [Junction Staff Skills Table].[Skills ID] = [Skills Table].[Skill ID];

You'll notice in this query that there is a record for each person for each skill that person possesses (if you are going to set it up like A S MANN's example). Notice that you will get # THEME items TIMES # SKILL items as a result for each person because there is no direct correlation between skills and themes.

It doesn't change the basic query though (again assuming you're going to use A S MANN's example) and you should be able to apply criteria against it that will limit your searches for instance if you are searching for a particular skill it will only show the same person multiple times if and only if they belong to multiple themes. if you further search for a particular theme those kinds of duplicates will fall out as well.

[tb199]

Ok your two junction tables need a primary key of their own so you can refer to them unqiuely.

Now my question is this (I don't see that it's been answered in your posts), what is the relationship between themes and skills?

If there is no relationship can a person have more than one theme? Your table doesn't have multiple people with the same theme but it really makes a difference in how you would construct a query/queries to handle it. I have to assume that a single person CAN have more than one theme because you have the junction table. If each person can only have one theme why are you carrying it in a separate table? and not on the staff table?.

Based on your current structure and assuming a single person CAN have more than one theme this is the type of query you would want:

SELECT [Staff Table].[Staff ID], [Staff Table].Title, [Staff Table].[First Name], [Staff Table].Surname, [Staff Table].Tel, [Staff Table].Email, [Staff Table].[Location ID], [Location Table].[Location Descriptor], [Junction Theme Staff Table].[Theme ID], [Theme Table].[Theme Descriptor], [Junction Staff Skills Table].[Skills ID], [Skills Table].[Skill Descriptor]
FROM (((([Staff Table] LEFT JOIN [Location Table] ON [Staff Table].[Location ID] = [Location Table].[Location ID]) LEFT JOIN [Junction Theme Staff Table] ON [Staff Table].[Staff ID] = [Junction Theme Staff Table].[Staff ID]) LEFT JOIN [Theme Table] ON [Junction Theme Staff Table].[Theme ID] = [Theme Table].[Theme ID]) LEFT JOIN [Junction Staff Skills Table] ON [Staff Table].[Staff ID] = [Junction Staff Skills Table].[Staff ID]) LEFT JOIN [Skills Table] ON [Junction Staff Skills Table].[Skills ID] = [Skills Table].[Skill ID];

You'll notice in this query that there is a record for each person for each skill that person possesses (if you are going to set it up like A S MANN's example). Notice that you will get # THEME items TIMES # SKILL items as a result for each person because there is no direct correlation between skills and themes.

It doesn't change the basic query though (again assuming you're going to use A S MANN's example) and you should be able to apply criteria against it that will limit your searches for instance if you are searching for a particular skill it will only show the same person multiple times if and only if they belong to multiple themes. if you further search for a particular theme those kinds of duplicates will fall out as well.

Hi,

Thanks for the reply, its really useful and helps me quite a lot. The difference between a theme and a skill is a theme is basically a department, but each person can belong to one or more themes. I.e one person may belong to 3 themes. Skills are going to be scientific skills that each person possesses and they may have 10, 15, or even just 4.

Is there any chance that you could help me put this together in my latest upload of the database? I would really appreciate it.

I basically want to be able to type a skill into a form and it displays all people with the typed skill. I would also like to be able to search by location too so put in Nottingham and it displays everyone in Nottingham. One thing I do not want people to be able to search for is by name if at all possible.

On each query I would like it to also bring up the persons contact details to.

Please can you help me, it would be greatly appreciated and make my month!

[tb199]

Also basically themes and skills arent related!