I have a table that is like

Employee Salary
Smith 1000
Jones 2000
Davis 1000
Williams 3000

I basically need a query that will return the records with the 3rd lowest values, so in this case

Smith 1000
Davis 1000


Any clues? I am pretty new to Access, as it may be obvious.

I guess you mean the third highest?

First create a query that lists the top three. You can do this in the query editor by setting the sort order of Salary to descending and setting the Top Values in the query property sheet to 3.

Then create a second query using the first query as the source. This time you want the bottom 1 record so set the salary to ascending and set Top Values to 1.

hth
Chris

although, thinking about it, if you have salaries

2000
1500
1500
1000
1000

this will give you down to 1500 only

========
so you might need to make the first query show UNIQUE VALUES, so you find the 3rd highest value, rather than the third listing in the whole table - if you see what I mean. Depends what you want exactly

Hey Thanks.

Just another quick question if you want to return all the values that are within 1000 of the lowest value, how would you do that exactly?

So for example for this table the results would be

Employee Salary
Smith 1000
Davis 1000
Jones 2000

Lookup the MIN() function and once you have done thatand understand the syntax just add 1000 to it.

Hey I'm aware of how to display the minimum salary values in the table, I'm just not sure how to get the conditional of displaying records that are within +1000 or -1000 of the minimum value. Any ideas of how to write it in the query editor.

Sorry I meant DMIN() function. Something like this:

DMin("[fieldName]", "tableName") + 1000

Is that in the SQL? Or is that what you write as the criteria in the query editor? I tried it out and it wasn't returning anything.

The SQL statement looks something like this:

SELECT employee.NAME, employee.SALARY
FROM employee
HAVING (((employee.SALARY)=DMin("[Salary]","employee")+1000));

This does not seem to be returning any results.

You use the HAVING clause with a group by statement:
http://www.techonthenet.com/sql/having.php

All you need for this is a WHERE clause. Put that in the CRITERIA part under SALARY in the query builder.

DMin("[Salary]","employee")+1000;

Hey I'm aware of how to display the minimum salary values in the table, I'm just not sure how to get the conditional of displaying records that are within +1000 or -1000 of the minimum value. Any ideas of how to write it in the query editor.

How can anything be less than the minimum. :confused:

Brian

How can anything be less than the minimum. :confused:

Brian:DHehe! It's the same as finding the next occurence after infinity. That has actually been proven.:eek:

Haha, yes that is true, syntactic error there, it should just be +1000.

I followed your instructions VBA but it's still producing a blank table, I'll do some reading up on the dmin function though.

Haha, yes that is true, syntactic error there, it should just be +1000.

I followed your instructions VBA but it's still producing a blank table, I'll do some reading up on the dmin function though.
Can you post your db?

I only want to return the results from 1 table and the database is very big, so I'll try to explain it again.

There is a table 'employee' with fields 'NAME' and 'SALARY'

I am trying to form a query that returns the people with the lowest salary plus anyone who earns within $1000 of the lowest salary. I wrote the following SQL statement to get the names and salaries of people who have the lowest salary

SELECT TOP 1 employee.NAME, Min(employee.SALARY) AS MinOfSALARY
FROM employee
GROUP BY employee.NAME
ORDER BY Min(employee.SALARY);

I just am not sure how to add into that the employees who earn within 1000 of the minimum value. Any idea of how to do it in SQL maybe?

I don't see why you need Top if you use Min.

Having got the Min run another query

SELECT employee.NAME, employee.SALARY
FROM employee, query1
Where employee.SALARY < query1.MinOfSALARY +1000;

Brian

Two ways:

SELECT NAME, SALARY
FROM employee
WHERE SALARY >= DMin("[Salary]","employee")+1000
GROUP BY employee.NAME
ORDER BY employee.SALARY;

OR

SELECT NAME, SALARY
FROM employee
GROUP BY employee.NAME
HAVING Min(SALARY) >= 1000
ORDER BY employee.SALARY;

Something like that.

Thanks Brian, I also don't know why I had to use the top 1 function, but when I ran the query without it, it returned all the employees and salaries in the table, instead of just the ones with the minimum salary, I have absolutely no idea why.

My Bad, of course Min is a field level action, to find the min of salary just select salary then to find the employees on it run a second query, which is what we are doing now. :o

Brian

Two ways:

SELECT NAME, SALARY
FROM employee
WHERE SALARY >= DMin("[Salary]","employee")+1000
GROUP BY employee.NAME
ORDER BY employee.SALARY;

OR

SELECT NAME, SALARY
FROM employee
GROUP BY employee.NAME
HAVING Min(SALARY) >= 1000
ORDER BY employee.SALARY;

Something like that.

Hey thanks for the help but both of these queries are returning the error:

You tried to execute a query that does not include the specified expression SALARY as part of an aggregate function.

Append all the fields with "employee.".

Well I tried:

SELECT employee.NAME, employee.SALARY
FROM employee
WHERE employee.SALARY >= DMin("[Salary]","employee")+1000
GROUP BY employee.NAME
ORDER BY employee.SALARY;

And it's still returning the same error.

I don't understand why you're grouping by Name? Surely you want to group by salary and order by name? Are you sure SALARY is in your EMPLOYEE table?

SELECT employee.NAME, employee.SALARY
FROM employee
WHERE employee.SALARY >= DMin("[Salary]","employee")+1000
GROUP BY employee.SALARY
ORDER BY employee.NAME;

Why group by anything?

Brian

Why group by anything?

BrianThat eludes me Brian:confused: Seems the OP wants to. Maybe there are employees within a certain salary band who receive the same figure.