[AccessD] Updateable query

Lonnie Johnson prodevmg at yahoo.com
Mon Mar 8 09:51:54 CST 2004


One thing you could try instead of a join is a sub query. In the criteria of the ClientID you could say...
 
 Not In (Select ClientID FROM tblOrphans)

 

Hope this helps!


"Steven W. Erbach" <serbach at new.rr.com> wrote:
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

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


Lonnie Johnson
ProDev, Professional Development of MS Access Databases
Visit me at ==> http://www.prodev.us




 




---------------------------------
Do you Yahoo!?
Yahoo! Search - Find what you’re looking for faster.


More information about the AccessD mailing list