Am I Expecting too Much - 2

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hi Roger Carlson,

further to my earlier post (Am I Expecting too much)
thanks for your reply.

I did indeed have records in the Clarifications
spreadsheet in which Clarifications.[Issue No] was
repeated. Unfortunately, removing the duplicate records in
Clarifications did not removed the error message.

What I'm trying to achieve is to update the Clarifications.
[CLAR STATUS] field based on the value of the
tblIssues.Status field, via a lookup in the
tblClar_Status_to_Issue_Status table, for those tblIssues
and Clarifications records for which tblIssues.Issue_ID =
Clarifications.[Issue No]. I thought that a join would be
the easiest way of achieving this. Do you have any other
ideas or can you spot any errors in my query, given my
stated aim ?

Thanks in advance,

Kevin
 
Hi Kevin,

I think that the important thing is whether or not Access
knows that your table or spreadsheet has unique values.
I would think that you can do what you want to do by
making sure that your tblIssues table is keyed, or
indexed without duplicates, by the status field.
Otherwise, Access cannot update based on a match to this
field because it doesn't know whether there are repeats
or not. I don't think that it is necessary to have any
kind of index on the other table/spreadsheet, just on
the "one" side so that Access knows that there will only
be one match. If that still doesn't work, you may want
to try changing from an inner join to a left join.

One other thing that I noticed, although maybe I'm
reading it wrong, it looks like your WHERE clause is
restricting the recordset to records where the
clarification status = issue status, but this seems
counter to what you want since if they were already equal
there would be no need to update.

Finally, regarding the SET statement, I would have
thought that you could just write Clarifications.[CLAR
STATUS] = tblIssues.Status

I'm not sure whether this helps, since I'm a little
confused by the sql statement, but maybe this will give
you some ideas to spot something.

-Ted
 
Ted,

thanks for your time. I've obviously not quite explained
myself.

I have two tables (tblIssues a native table and
Clarifications - a linked Excel spreadsheet) containing
records which are related to eachother, on a one to one
basis by the shared key, tblIssues.Issue_ID -->
Clarifications.[Issue No]. These two tables have two
further fields, tblIssues.Status and Clarifications.[CLAR
STATUS] which have a relationship whereby CLAR STATUS is
dependent on the value of tblIssues.Status. Given a value
of tblIssues.Status in the joined records/tables, I use a
mapping table, tblClar_Status_to_Issue_Status to get the
corresponding value to be used to update the
Clarifications.[CLAR STATUS] field. This lookup is done in
the SELECT subquery.

The query produces a result set but when I open the query
so that it attempts to update the Clarifications
spreadsheet, I get the "Operation must use an updatable
query" message as stated in my previous posting.

I've also just tried an extremely simple update query
which just changes the value of Clarifications.[CLAR
STATUS] to value Y if its equal to value X. This executes
OK so there is definitely something wrong with the way the
query is formed (see original posting under this heading)
although I cant see it.

Given the better (I hope) explanation of what I'm trying
to achieve, can anyone spot the discrepancy with what my
query is trying to do ?


Kevin
-----Original Message-----
Hi Kevin,

I think that the important thing is whether or not Access
knows that your table or spreadsheet has unique values.
I would think that you can do what you want to do by
making sure that your tblIssues table is keyed, or
indexed without duplicates, by the status field.
Otherwise, Access cannot update based on a match to this
field because it doesn't know whether there are repeats
or not. I don't think that it is necessary to have any
kind of index on the other table/spreadsheet, just on
the "one" side so that Access knows that there will only
be one match. If that still doesn't work, you may want
to try changing from an inner join to a left join.

One other thing that I noticed, although maybe I'm
reading it wrong, it looks like your WHERE clause is
restricting the recordset to records where the
clarification status = issue status, but this seems
counter to what you want since if they were already equal
there would be no need to update.

Finally, regarding the SET statement, I would have
thought that you could just write Clarifications.[CLAR
STATUS] = tblIssues.Status

I'm not sure whether this helps, since I'm a little
confused by the sql statement, but maybe this will give
you some ideas to spot something.

-Ted
-----Original Message-----



Hi Roger Carlson,

further to my earlier post (Am I Expecting too much)
thanks for your reply.

I did indeed have records in the Clarifications
spreadsheet in which Clarifications.[Issue No] was
repeated. Unfortunately, removing the duplicate records in
Clarifications did not removed the error message.

What I'm trying to achieve is to update the Clarifications.
[CLAR STATUS] field based on the value of the
tblIssues.Status field, via a lookup in the
tblClar_Status_to_Issue_Status table, for those tblIssues
and Clarifications records for which tblIssues.Issue_ID =
Clarifications.[Issue No]. I thought that a join would be
the easiest way of achieving this. Do you have any other
ideas or can you spot any errors in my query, given my
stated aim ?

Thanks in advance,

Kevin

.
Click to expand...
.
Click to expand...
 
Back
Top