Compare field Value With Latest Transaction Value

[DarrenBoyer]

I have three tables Customer, Vehicles, and Employees.
Each customer has an Sales Consultant. This is an EmpID field in the Customer's table.

Each Vehicle was sold by a Sales Consultant. Sales Consultant field name = EmpID in the Vehicles table.

After importing a few thousand records I have several Customer's who have the default Sales Consultant "444" as the value in the Customer's table. This value is incorrect in many cases. The value should be the most recent EmpID. (The last rep to sell the customer a vehicle.)

What I would like to have is a query to view the customers where the Sales Consultant Value is 444 and the most recent transaction or vehicle sold was sold by an Active Employee. I would like the CustomerID value to be updateable so I can change it to the active Sales Consultant.

If someone could offer some direction I would be most grateful. If I could convert your suggestion to an update query and update one active employee at a time that would be fantastic.

 

[PHV]

How are the Vehicles related to a Customer ?
I guess by CustID in Customer and Vehicles.
Create a saved query named, say, qryGetLastSold:
SELECT CustID,EmpID
FROM Vehicles V
WHERE DateOfSold=(SELECT Max(DateOfSold) FROM Vehicles W WHERE W.CustID=V.CustID)

Then, create your update query like this:
UPDATE Customer C INNER JOIN qryGetLastSold L ON C.CustID=L.CustID
SET C.EmpID=L.EmpID
WHERE C.CustID=444

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DarrenBoyer]

PHV,

This looks like a great suggestion. Thank you.

I need to modify the qryGetLastSold to filter out Customer Records where the EmployeeID is no longer active. I don't want to update these records. That is also why I defaulted everything to EmpID = 444 when I was importing my data. The Active field is a Yes/No value in the Employees Table. However, I haven't gotten any success.

Below are the modifications I thought would make sense. Could I please recieve some suggestions as now I am getting a syntax error where the italics are and I'm not sure why.

SELECT [Manual-Vehicles].CustomerID, [Manual-Vehicl[i][b]es].E[/b][/i]mployeeID
FROM [Manual-Vehicles] INNER JOIN Customers ON [Manual-Vehicles].CustomerID=Customers.CustomerID
[b]INNER JOIN Employees ON [Manual-Vehicles].EmployeeID=Employees.EmployeeID [/b]
WHERE ((([Manual-Vehicles].TransactionDate)=(SELECT MAX (TransactionDate) FROM [Manual-Vehicles] WHERE Customers.CustomerID = [Manual-Vehicles].CustomerID [b] AND (Employees.Active = False)[/b])));

 

[PHV]

SELECT CustomerID, EmployeeID
FROM [Manual-Vehicles] V
WHERE TransactionDate=(SELECT Max(TransactionDate)
FROM [Manual-Vehicles] W INNER JOIN Employees E ON W.EmployeeID=E.EmployeeID
WHERE W.CustomerID=V.CustomerID AND E.Active=True);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DarrenBoyer]

Thanks PHV

You made working with SQL fun again. It was a great sense of satisfaction when I pressed the run query icon and updated 1197 records in 3 seconds.

For anyone looking through this post here is the working code.

SELECT Customers.OrgName, Customers.IndivLastName, Customers.IndivFirstName, Customers.CustomerID, [Manual-Vehicles].EmployeeID
FROM Customers INNER JOIN [Manual-Vehicles] ON Customers.CustomerID = [Manual-Vehicles].CustomerID
WHERE ((([Manual-Vehicles].TransactionDate)=(SELECT MAX (TransactionDate) FROM [Manual-Vehicles]  INNER JOIN Employees ON [Manual-Vehicles].EmployeeID = Employees.EmployeeID WHERE Customers.CustomerID = [Manual-Vehicles].CustomerID AND Employees.Active = True)))
ORDER BY Customers.OrgName, Customers.IndivLastName;

Here is the final update code:

UPDATE Customers INNER JOIN qGetLastSold ON Customers.CustomerID=qGetLastSold.CustomerID SET Customers.EmployeeID = qGetLastSold.EmployeeID
WHERE (((Customers.EmployeeID)=444));