update query (Max(Date)) 1

[prettitoni]

Can someone help me write an UPDATE query that sets the Date of Table1 equal to the Max(Date) in Table2 where the tables are joined on Table1.ID = Table2.ID?

 

[amorous]

Try this:

Update Table1 SET Table1.yourdate1=(SELECT MAX(yourdate2) FROM Table2) WHERE Table1.ID = Table2.ID

-VJ

 

[prettitoni]

It prompts me for the Table2.ID when I do that...and if I do enter the parameter, it tells me "Operation must use an updateable query

 

[john76]

What do you mean it "prompts you for the Table2.ID"?
Just to verify, are you using SQL Server? If you are using something other than SQL Server, you probably will want to post your questing in a forum related to your DBMS.

John

 

[prettitoni]

Oh my bad...I didn't realize....I should be in the Access forum. *blush* Sorry...

 

[john76]

Not a problem, you may still get an answer to your question here as there are quite a few of us (myself excluded in this case) who are familiar with Access as well.

John

 

[prettitoni]

Thanks....can u help me John? I am using Access and it's asking me for the ID field like its a parameter. The query thinks the field ID is missing.

 

[john76]

As I indicated in my last post, I'm not terribly familiar with Access, but I would try:

Update Table1
  Set  table1date = ( Select Max(table2date)
                        From Table2
                        Where Table2.ID = Table1.ID
                    )

It's the same query as amourus, but with the parenthesis moved to (hopefully) the correct location.

John

 

[ESquared]

You can't do subqueries like that in Access. The question is complicated enough that you're best asking the good folks in the Microsoft: Access Queries and JET SQL forum.

 

[john76]

Thanks E². prettitoni, didn't I tell you? ;-)

John

 

[prettitoni]

Yup...I misread your post John...I thought you said you were included with those that know Access.

Thanks everyone.