operation must use an updateable query help!!

[greekpatriott]

Can anyone make the following sql work? I get the message operation must use an updateable query. Thanks.

UPDATE [earned mhrs update] INNER JOIN [Earned Mhrs Rollup] ON [earned mhrs update].PackageID = [Earned Mhrs Rollup].[Package ID] SET [Earned Mhrs Rollup].[Package ID] = [earned mhrs update].PackageID, [Earned Mhrs Rollup].[Earned Mhrs] = [earned mhrs update].[maxofearned mhrs];

 

[Crowley16]

you are trying to update something that's based on an join which is probably ambiguous...

Also, I'm assuming you just posted a part of the sql statement because what you posted doesn't make any sense, syntax wise...

--------------------
Procrastinate Now!

 

[greekpatriott]

This is the complete sql if I join the other field too.
Note that I have a max function in the query which is what is causing the problem. Is there a way to calculate the max of [earned mhrs] in the update query????

UPDATE [earned mhrs update] INNER JOIN [Earned Mhrs Rollup] ON ([earned mhrs update].[MaxOfEarned Mhrs] = [Earned Mhrs Rollup].[Earned Mhrs]) AND ([earned mhrs update].PackageID = [Earned Mhrs Rollup].[Package ID]) SET [Earned Mhrs Rollup].[Package ID] = [earned mhrs update].PackageID, [Earned Mhrs Rollup].[Earned Mhrs] = [earned mhrs update].[maxofearned mhrs];

 

[greekpatriott]

basicly one is a query the other is a table and in the query there is a max function.
query: [earned mhrs update]
table: [earned mhrs rollup]
cheers

 

[Crowley16]

this sort of error happens usually when it isn't clear what field needs to be updated, i.e. if there's 2 fields which are in different tables but with the same name...

from what I see, it seems what you want to do is to set the fields of the table based on the results of a query, if that's the case, then try something like:

UPDATE table
SET field1 = (SELECT field1 FROM query WHERE blah = blah)
SET field2 = (SELECT field2 FROM query WHERE blah = blah)
...
;

--------------------
Procrastinate Now!

 

[greekpatriott]

UPDATE [Earned Mhrs Rollup]
SET package id = (select package id from [earned mhrs update] where blah = blah)

what do I put in blah = blah???

 

[Crowley16]

eh, it's your data, you should know which field(s) you want to retrieve...

sorry, I don't do telepathy...

--------------------
Procrastinate Now!

 

[greekpatriott]

your telepathy is as limitted as mine is unfortunately how can I understand what you use in the place of blah blah if you dont explain better.

 

[Crowley16]

blah = blah is a condition which is used to identify which record you actually want to select...

without knowing what your data means, how they are joined and what makes sense, it's not feasable for me to know what should go there...

I can take random guesses, but that's what they'd be, Random...

only you can really know what data you actually want to put into those fields, unless you want to give me the specifications and requirements of your database...

--------------------
Procrastinate Now!

 

[greekpatriott]

ok I will use the make table query and then use an update query on the two tables. This works but I have many many tables now. I am getting lost in my own database. Specifications? hmmmmm
made in Cyprus.
it is 30mb

the specifications are on the sql at the beginning of this threat. Cheers