update query results when report is made.

[Homegrownandy]

I have a query which will produce a list of all the jobs we have approved. (shown below)

SELECT SiteT.Site_Name, [StatusO&MT].[O&M Status], JobT.[O&M Charged]
FROM SiteT INNER JOIN ([StatusO&MT] INNER JOIN JobT ON [StatusO&MT].[O&M_Status_ID] = JobT.[O&M_Status_ID]) ON SiteT.Site_ID = JobT.Site_ID
WHERE ((([StatusO&MT].[O&M Status])="approved"));

I would like to apply this to ONLY the jobs which are returned from the query:

UPDATE [JobT] SET [JobT].[ O_M_Charged] = '1';

I want the UPDATE code to be in a separate query so I can run it when I produce the report. That way I can make sure every time I make a report, we're not charging for the same job twice.

Obviously the second piece of code there will change the whole table not the query. Is there any special way to reference a query? or is it the same as referencing a table?

Or is there a different way to do this?

thanks in advance, Andy.


EDIT: after this works I can easily not include the ticked boxes.

[JamesDeckert]

you reference a query just like you reference a table.

[Homegrownandy]

Okay, So, if create a query using the second code I provided (referencing the query).

Then I'll make a macro to produce the report then update using the new query. Then add that to a button in an admin area...

Does that sound like the right way to go about this to you?

thanks for your answer.

[JamesDeckert]

I'm having a hard time understanding what you're trying to accomplish. When doing a SELECT query, you can reference a query just like a table. But you're talking about an UPDATE query. Are you trying to dynamically change a query? I'm lost.

[Homegrownandy]

Each job has a manual and we charge for the production of the manual but only after its approved. The report I generate shows all jobs that have had an approved manual. I then send this to the client and they pay for "x" amount of completed manuals basically.

The update is changing checkboxes to true. So I only want to apply it to the ones I have put into a report (once a month).

I'm just trying to ensure we don't charge them twice.

[JamesDeckert]

Your second query needs to do something like UPDATE [JobT] SET [JobT].[ O_M_Charged] = '1' WHERE [primarykey] in [firstquery].[primarykey];

[Homegrownandy]

looking into this now James, thanks.

[Homegrownandy]

Am I right in thinking I can run reports on click event of a button? I can also assign this code to that button too?

[Homegrownandy]

http://prntscr.com/8w9q6a please see the screenshot. I have the query I'm referencing open, But it still asks me to define the ID.

[Homegrownandy]

http://prntscr.com/8w9slq here is the design view also. I cant see what's wrong. although obviously something is. Hopefully someone here can help!

[Homegrownandy]

I have it working! thanks James.