I have a query that I use to generate a report that is based on information from 4 tables. Once the report has been run, I need to change all of the data from a “NO” to a “YES” in two different fields of the query. It would be very easy if I could just click in the field of the query and use the edit->replace. But, since it is using 4 tables, I can’t change the data.
The first time I run the report for the year, I have nearly 1,000 records that need the “NO” changed to a “YES”.
What I have been doing is then using this report and looking up each record and clicking the check box to set the value to “YES”. This gets tedious and time consuming.
Does anyone have any suggestions to what I could do to make this easier?
In your select from query you can just add an insert command that will change the data.
I’m a bit rusty in pure access, but it can definately made easier. I’m sure there is a conditional search and replace which is the doddle way to do it 
I could be thinking of something else though :cuckoo:
DT.
Yeah, the search and replace works great. I have queries based upon 3 tables that I can do this with. The problem is that once it is based upon 4 tables, you can’t make any changes to the data.
I think you’d need to construct a sql query then. especialy with parent>child relationships, if that is what I’m assuming you mean on four tables
DT.
Here is a pic of the relationship between the tables and here is the SQL view of the query. The two on the left are actually queries themselves, but all the data is coming from 4 tables (Operators, Certificates, Operator Assingment and System Classification).
SELECT renewal_operators.LName, renewal_operators.FName, [System Classification].[System Name], [Operator Assignment].[Primary Employer], renewal_operators.CertOp, Certif_ReimburseCard.EPA_Grant, Certif_ReimburseCard.Certnum, Certif_ReimburseCard.Grade, Certif_ReimburseCard.Kind, Certif_ReimburseCard.Valid, Certif_ReimburseCard.Grant, Certif_ReimburseCard.Fee, Certif_ReimburseCard.IssueDate, Certif_ReimburseCard.Op_ID_Num, Certif_ReimburseCard.Card_Sent, Certif_ReimburseCard.[Paid Year], [Operator Assignment].Sys_ID_Num, Certif_ReimburseCard.Grant_Card_Sent
FROM [System Classification] INNER JOIN (Certif_ReimburseCard INNER JOIN (renewal_operators INNER JOIN [Operator Assignment] ON renewal_operators.Op_ID_Num = [Operator Assignment].Op_ID_Num) ON Certif_ReimburseCard.Op_ID_Num = renewal_operators.Op_ID_Num) ON [System Classification].Sys_ID_Num = [Operator Assignment].Sys_ID_Num
WHERE ((([Operator Assignment].[Primary Employer])=Yes) AND ((renewal_operators.CertOp)=Yes) AND ((Certif_ReimburseCard.EPA_Grant)=Yes) AND ((Certif_ReimburseCard.Kind)=“water treatment”) AND ((Certif_ReimburseCard.IssueDate)<=DateSerial(Year(Now())-0,1,1)) AND ((Certif_ReimburseCard.Card_Sent)=No) AND ((Certif_ReimburseCard.[Paid Year])=2005) AND (([Operator Assignment].Sys_ID_Num)<>853)) OR ((([Operator Assignment].[Primary Employer])=Yes) AND ((renewal_operators.CertOp)=Yes) AND ((Certif_ReimburseCard.EPA_Grant)=Yes) AND ((Certif_ReimburseCard.Kind)=“water distribution”) AND ((Certif_ReimburseCard.IssueDate)<=DateSerial(Year(Now())-0,1,1)) AND ((Certif_ReimburseCard.Card_Sent)=No) AND ((Certif_ReimburseCard.[Paid Year])=2005) AND (([Operator Assignment].Sys_ID_Num)<>853))
ORDER BY renewal_operators.LName, renewal_operators.FName;
Dump the results to a make table query then update the other tables with updates where those files equal in the temp table