Query results change record? (1 Viewer)

I have a form where I push a button and some code runs and generates the SQL for a query.


I'm looking for a way to automate a check in a yes/No "completed" field in a sales table based upon the results of that query.



For example the query generates a table of

Product A, Room 1, Job Z.

Product B, Room 1 Job Z
Product A, Room 2, Job Z



Theres a sales table that has sales amount info for line items that match each of these "Rows" (not sure how you say it in access talk- criteria?)


How do I get the Sales Table to add a check mark in the "completed" field where each matching line item in the query matches the table? Ideally it happens when I check a control on the form.


Is this an append query?

That would be an UPDATE action.

What is it that makes a line item "complete"?

Completion of a work order. Updates the Estimate Data table. Which then I can use to generate invoices (Construction- monthly billings)


At least that's my theory


I'll take a look at that update query. Any tips are appreciated!

Still don't understand. How do you know a work order is complete? Why do you need to mark each line item as complete? This sounds like something that can be calculated when needed.

mickmullen, June7's questions are leading you to analyze your criteria for that check mark to be "earned." What you need is to write a formula for yourself that says (A=true + B=true + C>10) = checkmark or something like that. Generally, when you have a desired goal, your best bet is to write down IN DETAIL what it takes from your business viewpoint (not from Access - yet) to reach that goal. Each detailed component will contribute to a query that you can write to UPDATE the table that has the check box in question.

Once you have a firm documentation of the business rules, KEEP IT. (It's a form of logical gold.) NOW take a look at writing a query such as


Of course, you could have other actions first. This would record the date/time at which the checkmark was checked for you. The "checkmark = FALSE" portion prevents you from updating the date of the action a second time.

This was just an example to show ONE way to look at the problem. There are many ways to skin this cat.