From: sparks on
I had from 1 to 3 records under each person.
to figure out the calculations based on how many records and the value
of each one I did a union query and a crosstab so I could transpose
the records.

1--1000
1--2000
etc

2--500
2--200
2--100
etc

1---1000---2000---
2---500---200---100
etc
these values are the dia and sys values in the switch statement


then I used a query to put tbldemo the information about each person
together with this data so I had 1 record to calc on.


then based on these values I did this in the query
LETTERS: Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90 And
[sys2]<90) Or ([dia3]<90 And
[sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95)) And
([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
[sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
[sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4))

so I could determine which letter they received.
that is QryLetter1.

So if I am reading your answers correctly the querys I used to
transpose the data makes any work I do from that step foreward un
updateable.

from the time the data was used in the union query or the crosstab
query it is not longer usable for any type of update?

I can not think of any way to do the calculations based on the data
besides this.



I thought about putting the letter calc back into tbldemo.
BUT as I said this would also be an update query and since its built
around this I can't build something like letternum in tbldemo based on
the value I receive since it will be another invalid query.

------------------------






On Mon, 2 Aug 2010 16:53:06 -0400, "Bob Barrows"
<reb01501(a)NOyahoo.SPAMcom> wrote:

>sparks wrote:
>> I am starting to see this in anything new we build.
>> Everything that is already built is working fine.
>>
>> Is this some security update on the machines themselves.
>> Most people are running windows 7 but some are still on XP.
>>
>>
>>
>> looked on the ms pages
>>
>> Operation must use and updatable query
>>
>> The most common reason is that the Internet Guest account
>> (IUSR_MACHINE), which is by default part of the "Everyone" group, does
>> not have Write permissions on the database file (.mdb). To fix this
>> problem, use the Security tab in Explorer to adjust the properties for
>> this file so that the Internet Guest account has the correct
>> permissions.
>
>That is incomplete. All users require Modify permissions on the _folder_
>containing the mdb file. That is because users need to create, modify
>and delete the .ldb file that is used to control multi-user activity.
>
>I'm not saying that is the cause of your problem. Are you using a .mdb
>file as the backend for an ASP application? Is that why you cited that
>solution? In ASP, identifying the user whose account is being used is
>the key. If your site is set to allow Anonymous access, and you have not
>set up an account for the IUSR account to impersonate, then the relevant
>user account is indeed, the IUSR_Machine account. In addition, the
>IWAM_Machine account could also be relevant if you are accessing the
>database in application or server start or end events. If you are using
>Integrated Security and Anonymous is turned off, then the account of the
>user who requested the page is the relevant account.
>
>As others have said, permissions is not the only cause of this error.
>Certain queries can not be used for updates. GROUP BY is a prime
>example. Read Allen's article as sited by the other Bob :-)
>> study http://allenbrowne.com/ser-61.html
>> titled Why is my query read-only?
From: Bob Quintal on
Hi,

there are several alternative methods to getting your answer.

One way is to put your letters calculations into a user defined
public function.

Another would be to create a temporary table containing the key to
each record you wish to update and the value, then create an update
query using the target table and this temporary table.

A third way would be to create a procedure that uses a pair of
recordsets, the first holding the query, the second your table to be
updated and use vb code to update the table recordset from the query
recordset.

Bob

sparks <sparks(a)home.com> wrote in
news:m01g56l9elq1tiil35rc1mff06kd3je8sh(a)4ax.com:

> I had from 1 to 3 records under each person.
> to figure out the calculations based on how many records and the
> value of each one I did a union query and a crosstab so I could
> transpose the records.
>
> 1--1000
> 1--2000
> etc
>
> 2--500
> 2--200
> 2--100
> etc
>
> 1---1000---2000---
> 2---500---200---100
> etc
> these values are the dia and sys values in the switch statement
>
>
> then I used a query to put tbldemo the information about each
> person together with this data so I had 1 record to calc on.
>
>
> then based on these values I did this in the query
> LETTERS: Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90 And
> [sys2]<90) Or ([dia3]<90 And
> [sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95))
> And ([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
> [sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
> [sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4))
>
> so I could determine which letter they received.
> that is QryLetter1.
>
> So if I am reading your answers correctly the querys I used to
> transpose the data makes any work I do from that step foreward un
> updateable.
>
> from the time the data was used in the union query or the crosstab
> query it is not longer usable for any type of update?
>
> I can not think of any way to do the calculations based on the
> data besides this.
>
>
>
> I thought about putting the letter calc back into tbldemo.
> BUT as I said this would also be an update query and since its
> built around this I can't build something like letternum in
> tbldemo based on the value I receive since it will be another
> invalid query.
>
> ------------------------
>
>
>
>
>
>
> On Mon, 2 Aug 2010 16:53:06 -0400, "Bob Barrows"
> <reb01501(a)NOyahoo.SPAMcom> wrote:
>
>>sparks wrote:
>>> I am starting to see this in anything new we build.
>>> Everything that is already built is working fine.
>>>
>>> Is this some security update on the machines themselves.
>>> Most people are running windows 7 but some are still on XP.
>>>
>>>
>>>
>>> looked on the ms pages
>>>
>>> Operation must use and updatable query
>>>
>>> The most common reason is that the Internet Guest account
>>> (IUSR_MACHINE), which is by default part of the "Everyone"
>>> group, does not have Write permissions on the database file
>>> (.mdb). To fix this problem, use the Security tab in Explorer to
>>> adjust the properties for this file so that the Internet Guest
>>> account has the correct permissions.
>>
>>That is incomplete. All users require Modify permissions on the
>>_folder_ containing the mdb file. That is because users need to
>>create, modify and delete the .ldb file that is used to control
>>multi-user activity.
>>
>>I'm not saying that is the cause of your problem. Are you using a
>>.mdb file as the backend for an ASP application? Is that why you
>>cited that solution? In ASP, identifying the user whose account is
>>being used is the key. If your site is set to allow Anonymous
>>access, and you have not set up an account for the IUSR account to
>>impersonate, then the relevant user account is indeed, the
>>IUSR_Machine account. In addition, the IWAM_Machine account could
>>also be relevant if you are accessing the database in application
>>or server start or end events. If you are using Integrated
>>Security and Anonymous is turned off, then the account of the user
>>who requested the page is the relevant account.
>>
>>As others have said, permissions is not the only cause of this
>>error. Certain queries can not be used for updates. GROUP BY is a
>>prime example. Read Allen's article as sited by the other Bob :-)
>>> study http://allenbrowne.com/ser-61.html
>>> titled Why is my query read-only?
>

From: sparks on
Thanks big time to all of you for your help.
I was convinced I was going to do it no matter what.
YEP that no matter what was the killer LOL

I just make a table, printed out and updated on that and then deleted
it.

It works fine now

thanks again for all your help with this




On Tue, 03 Aug 2010 13:20:07 GMT, Bob Quintal <rquintal(a)sPAmpatico.ca>
wrote:

>Hi,
>
>there are several alternative methods to getting your answer.
>
>One way is to put your letters calculations into a user defined
>public function.
>
>Another would be to create a temporary table containing the key to
>each record you wish to update and the value, then create an update
>query using the target table and this temporary table.
>
>A third way would be to create a procedure that uses a pair of
>recordsets, the first holding the query, the second your table to be
>updated and use vb code to update the table recordset from the query
>recordset.
>
>Bob
>
>sparks <sparks(a)home.com> wrote in
>news:m01g56l9elq1tiil35rc1mff06kd3je8sh(a)4ax.com:
>
>> I had from 1 to 3 records under each person.
>> to figure out the calculations based on how many records and the
>> value of each one I did a union query and a crosstab so I could
>> transpose the records.
>>
>> 1--1000
>> 1--2000
>> etc
>>
>> 2--500
>> 2--200
>> 2--100
>> etc
>>
>> 1---1000---2000---
>> 2---500---200---100
>> etc
>> these values are the dia and sys values in the switch statement
>>
>>
>> then I used a query to put tbldemo the information about each
>> person together with this data so I had 1 record to calc on.
>>
>>
>> then based on these values I did this in the query
>> LETTERS: Int(Switch(([Dia1]<90 And [sys1]<90),1,(([Dia2]<90 And
>> [sys2]<90) Or ([dia3]<90 And
>> [sys3]<90)),2,[MissedVisitmark]=1,3,(([dia1]>=95 Or [sys1]>=95))
>> And ([dia2]>=95 Or [sys2]>=95) And ([dia3]>=95 Or
>> [sys3]>=95),5,(([dia1]>=90 Or [sys1]>=90)) And ([dia2]>=90 Or
>> [sys2]>=90) Or ([dia3]>=90 Or [sys3]>=90),4))
>>
>> so I could determine which letter they received.
>> that is QryLetter1.
>>
>> So if I am reading your answers correctly the querys I used to
>> transpose the data makes any work I do from that step foreward un
>> updateable.
>>
>> from the time the data was used in the union query or the crosstab
>> query it is not longer usable for any type of update?
>>
>> I can not think of any way to do the calculations based on the
>> data besides this.
>>
>>
>>
>> I thought about putting the letter calc back into tbldemo.
>> BUT as I said this would also be an update query and since its
>> built around this I can't build something like letternum in
>> tbldemo based on the value I receive since it will be another
>> invalid query.
>>
>> ------------------------
>>
>>
>>
>>
>>
>>
>> On Mon, 2 Aug 2010 16:53:06 -0400, "Bob Barrows"
>> <reb01501(a)NOyahoo.SPAMcom> wrote:
>>
>>>sparks wrote:
>>>> I am starting to see this in anything new we build.
>>>> Everything that is already built is working fine.
>>>>
>>>> Is this some security update on the machines themselves.
>>>> Most people are running windows 7 but some are still on XP.
>>>>
>>>>
>>>>
>>>> looked on the ms pages
>>>>
>>>> Operation must use and updatable query
>>>>
>>>> The most common reason is that the Internet Guest account
>>>> (IUSR_MACHINE), which is by default part of the "Everyone"
>>>> group, does not have Write permissions on the database file
>>>> (.mdb). To fix this problem, use the Security tab in Explorer to
>>>> adjust the properties for this file so that the Internet Guest
>>>> account has the correct permissions.
>>>
>>>That is incomplete. All users require Modify permissions on the
>>>_folder_ containing the mdb file. That is because users need to
>>>create, modify and delete the .ldb file that is used to control
>>>multi-user activity.
>>>
>>>I'm not saying that is the cause of your problem. Are you using a
>>>.mdb file as the backend for an ASP application? Is that why you
>>>cited that solution? In ASP, identifying the user whose account is
>>>being used is the key. If your site is set to allow Anonymous
>>>access, and you have not set up an account for the IUSR account to
>>>impersonate, then the relevant user account is indeed, the
>>>IUSR_Machine account. In addition, the IWAM_Machine account could
>>>also be relevant if you are accessing the database in application
>>>or server start or end events. If you are using Integrated
>>>Security and Anonymous is turned off, then the account of the user
>>>who requested the page is the relevant account.
>>>
>>>As others have said, permissions is not the only cause of this
>>>error. Certain queries can not be used for updates. GROUP BY is a
>>>prime example. Read Allen's article as sited by the other Bob :-)
>>>> study http://allenbrowne.com/ser-61.html
>>>> titled Why is my query read-only?
>>