updating records,

[etk]

Two months ago something happened to a server that caused a bunch of data collected by an application to corrupt a field. The database is a collection of information about employees, some served from another database (where the problem originated) and some collected in a form.

To simplify, there are really only 4 fields that come into play here on a table called HDData:

ID (assigned integer primary key)
EmpID
Campus
Dt

The problem with the data is that for 2 months 'Campus' has been showing 'unknown', causing much grief in sorting. I want to find the update all the 'unknowns' for a particular EmpID, but based on the most recent date for which Campus was not unknown.

The pseudocode for what I want to do is roughly this:

For each EmpID
where the campus is not "unknown"
And date is nearest date
select the campus
update all instances of campus for that EmpID where it is 'unknown' to the value selected

The pseudocode does not really match SQL, so I am worried that I might have to use VBA for this, or maybe I am just writing it out of order.

Can this be done without using VBA? Any hints on how to accomplish something like this or a link to a similar problem on another thread?

 

[Brianwarnock]

What do you mean by nearest date?

Brian

 

[etk]

by nearest date I mean the most recent date (dmax?) for that EmpId where City <> 'unknown'

Basically there is a possibility that employees move cities, though small. So I want to find the most recent date to have the 'unknowns' replaced with the most accurate records possible. It's a case of replacing with the best-case data, not absolutely accurate data.

 

[etk]

I should also say that this would be much easier if I had access to an employee database where there was a relation with the EmpID as primary key, but that is not the case. So I guess my problem is finding a way to loop the SQL for many instances of that EmpID. Since it is not a unique identifier in this relation I can't figure out how to set up a query to replace the city. Hence the question: can this be done by SQL alone or will I need to do it with VBA and SQL?

 

[Brianwarnock]

You can do this with SQL, but I think that it involves creating a temporary table.
Unfortunately I called my table Tblemp before I noticed that you had given us your table name.

query1 finds the max date per empid for known campus

SELECT tblemp.empid, Max(tblemp.dt) AS MaxOfdt
FROM tblemp
WHERE (((tblemp.campus)<>"unknown"))
GROUP BY tblemp.empid;

query2 joins this to the table to extract the campus name for the max date, one would think that you could use this query in query3 but it is not updateable and you cant, so it is a make table query

SELECT tblemp.* INTO tbltc
FROM tblemp INNER JOIN Qryemp1 ON (tblemp.empid = Qryemp1.empid) AND (tblemp.dt = Qryemp1.MaxOfdt);

query3 joins the two tables together to do the update

UPDATE tblemp INNER JOIN tbltc ON tblemp.empid = tbltc.empid SET tblemp.campus = tbltc.campus
WHERE (((tblemp.campus)="unknown"));

thus you have to run query2 and then query3

Hope this helps

Brian