Update using max from another table

[rmarquez5]

Greetings to all. I need to pick your brains, please.

I have a movie database. I am adding an enhancement that keeps track of the ID of the last movie that was printed off in a given category, and current max ID found in that same category. If the IDs are the same, then you don't have new movies to print off. If the IDs are different, then there are movies that haven't been printed off. Simple enough, I hope.

I have a table that simply has the columns category, currentID, and maxID. I manually determined and entered the currentID's. I have queries to determine the maxID for each category. What I want to do is to use the value in these "maxID queries" and update the maxID in the appropriate row in my table.

I have tried to enter SQL into "update to" but to no avail. I have also tried referencing the column that is returned in my maxID query, but that doesn't seem to take either.

Any input would be much appreciated. I hope to hear from you.
Thanks in advance.

Richard

 

[Jack Cowley]

This all sounds very complicated and maybe more complicated than necessary, but I am really not sure. Can't you have a Yes/No called "Printed" field for each recorded and when you print records update the Printed field? All records that do not have Printed checked have not been printed....

Jack

 

[rmarquez5]

Actually... that's a pretty good idea! I may try that. But is there a way to implement my original thought? Or is Access limited in that respect??

Thanks for your reply, though! If nothing pans out, that may be the simpliest way to achieve the same thing.

Richard

 

[Jack Cowley]

I think that Access is only limited by your imagination and moving large amounts of data over a network.... To tell you the truth I was having trouble following what it is you are trying to do so I took the easy way out. Doing things the easy way in Access is generally the way you want to go. No need to make something complicated if you don't need to....

Jack

 

[rmarquez5]

That's very sound advice, Jack. Thanks a bunch for your input!
Happy programming.

Richard

 

[Jack Cowley]

Richard -

You are very welcome. Good luck with your project!

Jack