Multiple rows are returned

  • Thread starter Thread starter Phillip
  • Start date Start date
P

Phillip

I am getting mulitle rows when I run the following code in
my query:

(SELECT MAX(Table1.[Table1_chg_date]) FROM Table1
WHERE (Table1.[Table1_emp_num] = [Employee Number?]) AND
(Table1.[Table1_chg_date] <= [Change Date?]))

I am working from the View mode for the query so I have
this code in the Criteria selection where I select
Table1_chg_date. I am also selecting an employee number.
(I get the same results if I only select the chg_date
field)

I would think because I am asking for the MAX date that is
<= to the date I provide([Change Date?]) and where the
emp_num is = to the number I give ([Employee Number?])
that it would return one row. However, when it finds a
date and employee number that matches the WHERE clause it
gives me that PLUS every other row with the same date
reguardless of employee number.

So if I ask for employee number 123 with the date of
20040212 I get:
emp_num chg_date
123 20040212
456 20040212
987 20040212

Its like it ignores the code for the employee number.

If anyone has any ideas what I am doing wrong that would
be great.

Thanks!
 
Try:


SELECT Max(Table1.[Table1_chg_date]) AS MaxDate
FROM Table1
GROUP BY Table1.[Table1_emp_num]
HAVING (((Table1.[Table1_emp_num]) = [Employee Number?])
AND ((Table1.[Table1_chg_date]) <= [Change Date?]))
 
Back
Top