SELECT (INSERT) subqueries

[curtiss]

I have just recently found out about subqueries, and I am curious about something. I know that you can place a SELECT query inside of an INSERT query, in order to pull information from one table and insert it into another. However, I am looking to see if there is any way to place an INSERT query inside of a SELECT query.

Let me explain what I am doing, in order to help clarify:

I have a table set up for my employees' contact information. When you add an employee to the table, an employee number is automatically generated (an Auto-Number column in Access 2003). That column is the only column within my table that will be completely unique. I need to have a reliable, accurate way to pull that employee number back out of the database in order to use it in another table.

The query I have tried (that didn't work) is:

SELECT `employeeid` FROM `employees`
WHERE (
INSERT INTO `employees`
(`firstname`, `lastname`, `emailusername`, `location`, `roomnumber`, `officephone`, `position`)
VALUES ('Curtiss', 'Grymala', 'cgrymala', 1, 'MC6', 5408698564, 'Webmaster')
);

Any suggestions on how to do that correctly?

[flann]

I don't think you're going to be able to do that. The reason being, I don't think you can put a WHERE condition on something that doesn't exist yet. I've never tried to do this and this is just my best guess.