[AccessD] Updateable query

Dear Group,

I've inherited an application that I'm working (slowly) into better shape. One of the things that has happened to some of the customers for this application (all domestic violence shelters) is that there are some orphaned records in a service log (tblServiceLog); that is a table that shows which service, how much of that service (in hours), when it occured, and who received it; like this:

FieldName      FieldType
===========    ==========
LogID          AutoNumber
ClientID       LongInt
ServiceDate    Date/Time
ServiceCode    Text
Hours          Number

The "orphans" are those tblServiceLog records that have been entered with no ClientID.

Some of the records were re-entered upon the discovery that a previously entered log entry failed to show up on-screen; some were not.

I created an audit report that showed all the fields in tblServiceLog that were missing the ClientID. The customer has tracked down which Clients should have received those services and is ready to enter the ClientIDs manually (with my help).

What I would like to create is a query that not only shows the tblServiceLog records that are missing ClientIDs, but also those records that were entered a second time so that the ClientIDs "took." I figure that I'd look for records that matched date, service code, and hours.

I first did a MakeTable query that lists ServiceDate, ServiceCode, and Hours just for those "orphaned" records. Then I made a query like this:

SELECT T.LogID, T.ClientID, T.ServiceDate, T.ServiceCode, T.Hours
FROM tblServiceLog AS T 
INNER JOIN _tmpTblOrphans AS O 
ON (T.Hours = O.Hours) 
AND (T.ServiceCode = O.ServiceCode) 
AND (T.ServiceDate = O.ServiceDate);

This gives me the orphans as well as all records with ClientIDs that match date, service, and hours on the days that the orphans received services.

Of course, the recordset returned by this query is NOT updateable. I'm drawing a blank as to how I could work this so that the recordset IS updateable. Might this be something I could do with a filter?

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
920-969-0504

"Don't light a match 'til ya know which end of the dog is barkin'." - Dave Barry