Multiple criteria in SQL statement

[aroma_kt]

I have the following tables:
employees general info
employees job info
employees leaves
employees medical info
employees penalties
employees Bonuses
employees Training Courses
employees Promotions
employees Insurance

Relationships are between the (employees general info) table and each table is one to many by the field (employeeNo).
Please see the attached image.

I need to let the user define multiple criterias in all of these tables:
for instance the user wants to display all employees that meet the following criteria:

Nationality: Egyptian (this field is in general info table) AND
Bonus(amount): >= 3000 AND
Training course destination: U.K
..... and so on

Of course I'm just giving an example, I want the user to be able to define whatever criteria he wants (even if the fields are in multiple tables as in my example).

is this possible, if it is, how can I achieve it?
What would the miracle SQL statement be?

 

[Mile-O]

How can you have a relationship (such as Employees to Training Courses) as a one to many?

It sounds like your database is not normalised (designed well) and, therefore, the SQL criteria should be the least of your worries.

 

[aroma_kt]

It's simple the relationship:
one:
====
general info (name, birth, address....) are entered only once of course (in general info table).

many:
=====
one employee can have more than one training course, the same goes for the other tables, one employee can have more than one leave and so on.

Please see attached photo.

 

[Pat Hartman]

You're going to need to build the queries with VBA. I think the easiest approach is to build a separate query for each relationship then create a final query that joins the ones you built.

If the criteria from separate relationships can be "OR'd", the problem is more complex. In this case, you'll need to union the queries.

If you are going to support both AND and OR relational operatiors, you'll need to support parentheses and now you're writing your own SQL parser.

 

[aroma_kt]

Thanks Pat Hartman for your response.

About your question:
No, I don't want to let the user define criteria with (AND, OR). I think the (AND) operator is more than enough.

I can easily build the SQL statements for each one of the tables through VBA, I hane no problem with that, but how can I write the final SQL statement that joins the ones I built? (I'm not advanced in SQL statements).

Suppose the SQL statements of my previous example are:

1) First SQL:
Select [Employee_ID] From [General_Info] Where [Nationality]='egyptian'

2) Second SQL:
Select First([Employee_ID]) From [Bonuses] Where [Bonus_Amount]>=3000

3) Third SQL:
Select First([Employee_ID]) From [Training_Courses] Where [Destination]='U.K'

P.S.
I used the (First) function because, as I have mentioned before, an employee can have more than one record in all of my tables (except for the general info table of course, which contains name, birth, address ....etc).

so what would the joining SQL statement be?? please help me and I'll be more than grateful.

 

[Pat Hartman]

You need to save the individual queries as querydefs. You can keep reusing the same names. Then build a query that does an inner join based on which queries were saved.

Select query1.Employee_ID From query1 Inner Join query2 on query1.Employee_ID = query2.Employee_ID inner join query3 on query1.Employee_ID = query3.Employee_ID .....

 

[aroma_kt]

Again thanks Pat Hartman, for your help.
Unfortunately I'm not familiar with querydefs.

If it's not too much to ask, could you tell me how to use them??

 

[Pat Hartman]

I looked up querydef in help and found this example:

Dim dbsCurrent As DAO.Database
Dim qryTest As DAO.QueryDef
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Employee List")
qryTest.SQL = "SELECT * FROM Employees;"

You can create the nine dummy querydefs by going to the query tab of the database window and choosing new. Create a basic query that selects the data you want without any selection criteria. Save each query with a meaningfulname.