Queries... (1 Viewer)

[Lissymd]

Can someone please help me with this problem.

I am designing an employment database and need to create a query that searches an application date against an employee and if there is no date registered over a 3 month period, it displays the employee's name.

If you don't understand my problem, let me know and i'll try and explain it better!

Any help appreciated.

Lissy

 

[RV]

Lissy,

your question is:

Show me(only?, question) those employeenames from all employees which have become employee more than three months ago and who don't have an application date filled in, right?

You'll need a Hire date for an employee (the date they started to work for the company).
Are Hire dates being registrated?
How do you registrate applications?
How do you registrate employees?

Greetings,

RV

 

[Lissymd]

Ok, your on the right track. I want to display a list of all the employee's who have not applied for a job (job application) within 3 months. Also, if possible, change the account status of an employee to "Hold" if it fits this criteria. (Not totally essential for functionality as the user can go through and manually change the employee's status if required)

An employee can also just be a client registered with the job agency. ie Job seeker


The 2 tables I need to use (i think) are Employee's and Job Application. In the Job Application table there is a field called Job Application Date.

If a person wants to apply for a job they fill in a job application form and the details get stored in the database. ie name, Job record ID (foreign key), Application Date, Registration No etc.


Does this make the picture clearer?

Thanks

Lissy

 

[RV]

Lissy,

>I want to display a list of all the employee's who have not applied for a job (job application) within 3 months.<

OK, this means your primary criterium for your query would be changed. You would not check against an application datE but against application datA.

Here's a basic SQL statement you could use.
In this statement the tables Employee and Application are linked by EmpID (EmployeeNumber).

SELECT EmpID, EmployeeName
FROM Employee
WHERE NOT EXISTS
(SELECT EmpID, MAX(ApplDate)
FROM Application
WHERE Application.EmpID=Employee.EmpID
AND DateAdd("m",-3,Date())<=ApplDate
GROUP BY EmpID);

Greetings,

RV

 

[Lissymd]

Hi

I seem to be getting a syntax error from the "FROM" statement down.

Here is the code I'm using, (The only thing I have modified is the table and field names to match mine.)


SELECT EmpeID, EmpeSurname
FROM tblEmployee
WHERE NOT EXISTS
(SELECT EmpeID, MAX(Jap_Date)
FROM tblJob Application
WHERE tblJob Application.EmpeID=tblEmployee.EmpeID
AND DateAdd("m",-3,Date())<=Jap_Date
GROUP BY EmpeID);


??

Thanks

Lissy

 

[RV]

Lissy,

>FROM tblJob Application<

This it causing the error.
You have to use square brackets around your tablename.
It's adviced NOT to use table names like this (names consisting of several seperated words).

Your SQL Statement should be:

SELECT EmpeID, EmpeSurname
FROM tblEmployee
WHERE NOT EXISTS
(SELECT EmpeID, MAX(Jap_Date)
FROM [tblJob Application]
WHERE [tblJob Application].EmpeID
=tblEmployee.EmpeID
AND DateAdd("m",-3,Date())<=Jap_Date
GROUP BY EmpeID);

RV

 

[Lissymd]

Hi

I've copied the code you posted and now I just get input boxes popping up asking for parameter value????.

Lissy

 

[RV]

Lissy,

the statement I posted should work correctly.
The cause of the message you get will is caused by the WHERE clause when you refer to a non-existing column.
The query is then recognized as a so-called Parameter Query.
A Parameter Query asks for user input to fill in a condition.

RV