[Jezzabeanz]

Hi,

I have created a query which updates a customer ID in an order table where the customer's first name and surname are in the customer database.

UPDATE       tblOrder
SET                CustomerID = 
(SELECT CustomerID 
FROM tblCustomer 
WHERE First_Name ='Fname' AND Surname='Sname')
WHERE EXISTS
(SELECT CustomerID 
FROM tblCustomer 
WHERE First_Name ='Fname' AND Surname='Sname')

The reason I have posted this in MS Access is that I aim to use it for VB.Net though I cannot seem to get this query to work.

EDIT: The update values 'Fname' AND 'Sname' are basically place holders as the actual values will be variables.

This post has been edited by Jezzabeanz: 08 June 2012 - 12:47 PM

[June7]

Why doesn't it work - error message, wrong results, nothing?

I hope there won't be more than one John Smith in the Customers dataset

This post has been edited by June7: 09 June 2012 - 05:12 PM

[Jezzabeanz]

The issue is resolved now, though that was my primary concern, though I found a way around it.

Using the SQL MAX() function, I did 3 queries directly after each other.

The first, was the insertion of the customer information, the second was the SELECT MAX(CustomerID FROM tblCustomer [and stored it into a variable] and the final variable was the insertion of the order information.

I think my approach was flawed due to myself being a PHP developer (primarily) and having access to the UserID from an authenticated session, opposed to finding an alternative means of getting a customerID.