Can an inserst statement do this

[PeteMares]

Ok I don't know if this is possible, I don't really know much about coldfusion.
but where is what I want to happen.
With out going into to much details and elimintating table fields that don't
concern this here is the table.

Order information:
CompID (autonum)
ServiceTag

Employees
EmployeeId(autonum)
Name

ComputerAssignments
Assignmentnum (autonum)
ComputerID
EmployeeID

Here is what I want...I want a form that when you put in thier Name and
ServiceTag it will look up thier computerID and employeeID and then insert that
into the computerassignment table.

[Jochem van Dieten - TMM]

PeteMares wrote:

>
> Order information:
> CompID (autonum)
> ServiceTag
>
> Employees
> EmployeeId(autonum)
> Name
>
> ComputerAssignments
> Assignmentnum (autonum)
> ComputerID
> EmployeeID
>
> Here is what I want...I want a form that when you put in thier Name and
> ServiceTag it will look up thier computerID and employeeID and then insert that
> into the computerassignment table.

This should get you started:

INSERT INTO computerassignments (computerID, employeeID)
SELECT oi.compID, e.employeeID
FROM "Order Information" oi, Employees e
WHERE oi.ServiceTag = <cfqueryparam value="#form.servicetag#">
AND e.Name = <cfqueryparam value="#form.name#">

Jochem

--
Jochem van Dieten
Team Macromedia Volunteer for ColdFusion, beer and fun.

[PeteMares]

Thank you