Query Help

[tb199]

Dear All,

I am relatively new to Access 2010 and have put together a database which I feel is only partly working. I have zipped and attached for those of you who could maybe help me.

The main aim of the table is to allow people to search for others with skills they wish to know about. E.G I want someone to be able to search in the query 'Phone' and it brings up everyone with Phone as a skill. Currently this works but I cant get the query to also let me show the location of the person also. I think this is to do with my relationships. Could someone check these for me please?

I would also like a query to search by skill and location as one query so all people in 'Center' who have 'Phone' as a skill. Another is search for people in a Theme (This is a department) who have a skill. So search for those with 'Phone' in theme 'A'.

Please can someone help me out, it would be massively appreciated!

 

[jdraw]

Please provide more info on the "business".
What exactly is a Theme? How does it relate to Staff and/or Skill.

Your tables may be correct,but we don't know WHAT you are trying to do (o what your business is). We only see HOW you have something set up--which apparently isn't doing what you want/need.

 

[tb199]

Please provide more info on the "business".
What exactly is a Theme? How does it relate to Staff and/or Skill.

Your tables may be correct,but we don't know WHAT you are trying to do (o what your business is). We only see HOW you have something set up--which apparently isn't doing what you want/need.

The business is a university and a theme is basiclaly a department. The skills table will contain many many skills as will the staff table. The main aim of the database is to allow people to search for others with skills they wish to know about. E.G I want someone to be able to search in the query 'Phone' and it brings up everyone with Phone as a skill. Currently this works but I cant get the query to also let me show the location of the person also. I think this is to do with my relationships. Could someone check these for me please?

I would also like a query to search by skill and location as one query so all people in 'Center' who have 'Phone' as a skill. Another is search for people in a Theme (This is a department) who have a skill. So search for those with 'Phone' in theme 'A'.

Does this help?

 

[jdraw]

Why do you use the term theme, if it's a Department?
You seem to be dealing with
Staff
Skill
Department
Location

So Staff represent People.
A Staff can have 0,1 or more Skills
(Is that true? What about Staff with no Skill?)
A Skill can be associated with Many Staff


Can you please tell us about Location and Department?

 

[tb199]

I use the term department as it is what we use here.

You are correct in what we are dealing with. Staff represent people also yes.

To be on the database staff have to have 1 or more skills but one person can have 1 skill and another can have say 9.

Location is where the person is based as in what building they are based in. This is in the database so that if someone searches for someone they can see where they are to go and speak to them about it.

I would also like a query to search by skill and location as one query so all people in 'Center' who have 'Phone' as a skill. Another is search for people in a department (theme) who have a skill. So search for those with 'Phone' in theme 'A'.

 

[jdraw]

Please tell us more about Location and Department(theme).

I'm looking at your database and I see both tables but do not understand the difference between them.

For reference:
You should not have embedded spaces in your field and/or object names. Get yourself and use a consistent naming convention. It will save you hours of syntax error debugging and make your code more maintainable.

Each of your tables should have a defined PrimaryKey.

The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

 

[tb199]

Hi,

I dont know what else to tell you about location. Its simply the building where their office is based. Department (theme) is just the department they work for.

Please help me out. I will be so grateful. The database needs to contain each staff members details, their contact details, where they are based, what department they belong to.

Queries need to be able to find all people with a certain skill; all people with a certain skill in a searched for location, all people with a certain skill in a searched for department (theme).

Please help me.

 

[jdraw]

Here is a revised database.
Save it with a new name.
Good luck

 

[tb199]

Thats fantastic, thank you so much! There is one thing I possibly may need and on the skill search I need it to also display the location as another field such as tel, email etc. Can you do that for me. I couldnt get it to link.

P.S did you have to alter any relationships?