Need sub-/query for anniversary of hire date

[kmkland]

I have tried to create a subquery which will produce a list of employees who have anniversary hire dates within a certain date parameter ([Start Date],[End Date]). The table I'm querying from is Employee Info; the field name is Hire Date. I think I may be having a problem with the new year (switching from 2004 to 2005????), although all of my dates are formatted as mm/dd/yyyy. I attempted to use the following SQL Select statement.

Annual Review: (Select [Hire Date] From [Employee Info] Where DateSerial(2000,DatePart("m",[Hire Date],0,0),DatePart("d",[Hire Date],0,0)) Between DateSerial(2000,DatePart("m",[Start Date],0,0),DatePart("d",[Start Date],0,0)) And DateSerial(2000,DatePart("m",[End Date],0,0),DatePart("d",[End Date],0,0)))

However, the error I received was: "At most, one record can be returned by this subquery." I entered the date parameters (for the entire year, just to test) in the long format with a 4-digit year.

I want to be able to produce a report on a monthly basis with all of the anniversaries for employee hire dates.

I have little training in SQL Select statements and extremely limited knowledge of VB, but I am willing to use whatever knowledge any of you may have to offer!
Thanks in advance for your help!!!!
Kim

 

[PHV]

Your subquery must be used in the WHERE clause, not the SELECT list:
SELECT ... FROM ...
WHERE ... AND [Hire Date] IN (Select ...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

My apologies PH.
I meant to say query, and I am utilising Access.
So, this would essentially go into the "Field" location on the grid???
Kim

 

[PHV]

In the criteria location of field [Hire Date]:
In (Select ....)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

PH,
I'm still getting the same error message as before: "At most, one record can be returned by this subquery."
Kim

 

[PHV]

Try to modify the SQL code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kmkland]

PH,
How so? Someone else actually wrote a code similar to this one, and I modified it according to my needs. My knowledge is limited in SQL.
Kim

 

[lespaul]

Did you add the 'IN' to the beginning of the query grid criteria?

Annual Review: [COLOR=red]IN[/color](Select [Hire Date] From [Employee Info] Where DateSerial(2000,DatePart("m",[Hire Date],0,0),DatePart("d",[Hire Date],0,0)) Between DateSerial(2000,DatePart("m",[Start Date],0,0),DatePart("d",[Start Date],0,0)) And DateSerial(2000,DatePart("m",[End Date],0,0),DatePart("d",[End Date],0,0)))

The IN clause will allow multiple records in a subquery.

What is the rest of your SQL? (switch to the SQL view and post the query)



Leslie

 

[kmkland]

Leslie,
I added 'In' to my statement, and I'm prompted for date parameters, but there are no results.
I've tried several different options but am still coming up empty-handed. I'm not sure where to go from here. I do appreciate your help with this matter.
Kim

 

[lespaul]

Ok, could you post the rest of the SQL?

Leslie

 

[kmkland]

Hi Leslie,
Ok, here is the SQL statment. (In my posts, I had stated that the table name=Employee Info, to save confusion because the actual name is Hire Dates and the field name is Hire Date. But, nonetheless, here is the SQL statement.)

SELECT [Employee Information].[SS#], [Hire Dates].[Hire Date], (Select [Hire Date] From [Hire Dates] Where DateSerial(2000,DatePart("m",[Hire Date],0,0),DatePart("d",[Hire Date],0,0)) Between DateSerial(2000,DatePart("m",[Start Date],0,0),DatePart("d",[Start Date],0,0)) And DateSerial(2000,DatePart("m",[End Date],0,0),DatePart("d",[End Date],0,0))) AS [Annual Review]
FROM [Employee Information] LEFT JOIN [Hire Dates] ON [Employee Information].[SS#] = [Hire Dates].[SS#];

Thanks so much for helping me with this!
Kind regards,
Kim

 

[kmkland]

Leslie,
With this statement, I get results in all of my other columns but no result in the Annual Review column....
Kim

 

[kmkland]

Hmm...I had sent a reply a few minutes ago (shortly after I had sent the SQL statement) stating that to you, but I was also having problems with my server so the message did not go through.
Kim

 

[lespaul]

Here's a starting point, this will get you the latest hire date for each employee:

SELECT [Employee Information].[SS#], [Hire Dates].[Hire Date]
FROM [Employee Information] LEFT JOIN [Hire Dates] H1 ON [Employee Information].[SS#] = H1.[SS#];
WHERE HIREDATE =
(Select Max([Hire Date]) From [Hire Dates] H2 Where H2.[SS#] = H1.[SS#])

Like PHV said in the 2nd or 3rd posting, you need to put the criteria in the WHERE clause, not the SELECT clause.

Leslie

 

[kmkland]

Hi Leslie,
This statement doesn't produce anniversary hire dates within a certain parameter. It just lists the hire dates. I'm not sure how I would approach constructing the statement so I can extract the dates I need.
Do you possibly have anymore ideas on the matter? I do appreciate your help.
Kim

 

[lespaul]

one option is to save this query and use it as the source for your parameter query:

SELECT * FROM qryHireDates WHERE Hiredate > [Enter Date here]

leslie

 

[kmkland]

Thank you all for your help!
I have my queries up and running just fine!
Thanks again.
Kim