ACCGRPID is a unique identifier for each seperate account. Each account can
have many locations (tens of thousands sometimes). Each location has a value
EQSITEDEDAMT, which is the value to be updated for certain locations eg only
the ones in CA. This value is in a table with other information about the
locations which is not needed here. The primary key of that table is called
EQDETID.
Hope this clarifies!
"Ken Snell [MVP]" wrote:
ACCGRPID is or is not the same value as EQDETID? In other words, does
ACCGRPID have the value that we would use to find the matching EQDETID? If
not, what is the relationship between ACCGRPID and EQDETID?
What we're trying to do now is to identify the children record(s) that need
to be updated can be identified from what you select on your form.
--
Ken Snell
MS ACCESS MVP
"Mary Pode" wrote in message
...
Hi
The field to be updated is called SITEDEDAMT
The table it's in is called dbo_eqdet_QTE_05_01
The primary key of the table with the deductible is EQDETID
The values that corrospond to what I'm selecting on my form will be a
primary key identifier - called ACCGRPID
Thanks
"Ken Snell [MVP]" wrote:
OK - now we need you to identify the following fields from this query:
-- the field (and table that it is in) that holds the deductible
value
that is to be updated;
-- the primary key field of the table that holds the deductible
field;
-- which fields correspond to the values that you're selecting on
your
form so that you are "identifying" the appropriate person's records that
are
to be updated.
--
Ken Snell
MS ACCESS MVP
"Mary Pode" wrote in message
...
Hi
Is this what you wanted? This is the SQL view of my select query for
one
account showing all the locations in CA for that account whose
deductible
I'd
like to update.
SELECT dbo_accgrp_QTE05_01.ACCGRPID, dbo_loc_QTE05_01.COUNTRY,
dbo_loc_QTE05_01.STATECODE, dbo_loc_QTE05_01.LOCNUM,
dbo_loc_QTE05_01.ADDRESSNUM, dbo_loc_QTE05_01.STREETNAME,
dbo_loc_QTE05_01.POSTALCODE, dbo_eqdet_QTE05_01.SITEDEDAMT
FROM ((dbo_accgrp_QTE05_01 INNER JOIN dbo_loc_QTE05_01 ON
dbo_accgrp_QTE05_01.ACCGRPID = dbo_loc_QTE05_01.ACCGRPID) INNER JOIN
dbo_eqdet_QTE05_01 ON dbo_loc_QTE05_01.LOCID =
dbo_eqdet_QTE05_01.LOCID)
INNER JOIN [EQ US REGIONS] ON (dbo_loc_QTE05_01.COUNTRY = [EQ US
REGIONS].COUNTRYCODE) AND (dbo_loc_QTE05_01.STATECODE = [EQ US
REGIONS].STATECODE)
WHERE (((dbo_accgrp_QTE05_01.ACCGRPID)=208) AND
((dbo_loc_QTE05_01.COUNTRY)="US") AND
((dbo_loc_QTE05_01.STATECODE)="CA"));
Thanks
"Ken Snell [MVP]" wrote:
Post the SQL statement of the select query that you've created. We
need
to
modify it into an update query so that we can run it from your form.
--
Ken Snell
MS ACCESS MVP
"Mary Pode" wrote in message
...
" What you want to do is to create an update query that will read the
values
to be used in the query from your form.
"
yes - this is exactly what I want to do.
I can set up a select query easily to select the locations that I
want
to
update - I take it this query is kept in the original query format
and
isn't
changed to a form view or anything?
The name of the form that I'll be using is called
"FormUpdateQuoteValues"
The names of the textboxes are "FieldUpdateTo" which is where I
enter
the
value that I want the values to be updated to and "FieldWhere" which
is
where
I enter the criteria eg CA.
Thanks again
"Ken Snell [MVP]" wrote:
It appears, then, that what you want to do is simpler than I'd been
thinking.
What you want to do is to create an update query that will read the
values
to be used in the query from your form.
Note: Remember that, once run, an update query cannot be undone.
So
you'll
need to be sure that the user means to do the update before you run
it,
or
else your data will be changed.
First, let's have you create a select query (a normal query) that
will
select the records that you want to update. We will turn this query
into
an
update query after you post it -- it's easier to do it this way.
Also, what is the name of the form that you'll be using to enter
the
values
that will be used by the query? And what are the names of the
textboxes
into
which you'll type those values?
--
Ken Snell
MS ACCESS MVP
"Mary Pode" wrote in message
...
Thanks very much for your quick response.
At the moment what I have set up is a form that just shows the
main
details
of the account - account number, name of insured etc. This comes
from
a
query picking out certain information from an underlying
"Accounts"
table.
I don't really need to be able to see all the other information
such
as
the
locations that are part of the account because there could be
tens
of
thousands of them! I take it this is what you were thinking
would
be
on
the
subform?
So what I would like are two boxes somewhere on the form - one
that
I
would
fill out with the deductible amount to be updated eg 0.05 and the
other
with
the criteria eg CA for California locations only. And then a
button
that
you
press to essentially do the same as the run button in a regular
update
query.
I hope this is what you wanted to know - thanks again for helping
me
with
this!
"Ken Snell [MVP]" wrote:
What you seek to do can be done within a form, using unbound
controls
on
the
form and using programming behind the form that, when you click
a
button,
will update the data as you wish.
This is not a simple thing to set up, and talking you through it
will
take a
bit of iterations, so get ready for a few exchanges here! ;-)
First, let's start with what you've already set up. I assume
that
this
is
a
form that is bound to a query or table that is returning the
values
that
you're displaying. Are you using subforms for the children data?
Or
are
you
using separate forms that you open from this one main form? It
will
be
easier for your use if you use subforms.
--
Ken Snell
MS ACCESS MVP
"Mary Pode" Mary wrote in
message
...
Hi
I am trying to build a "front end" to an application and am
having
some
difficulty. Here's my problem:
I would basically like to be able to do via a form what an
update
query
does:
I work in insurance and I have a table that has all the
information
about
the insured accounts in it and each account has it's own
unique
identifier.
I have a second table with all the policy information in it.
A
third
table
has all the information about the locations insured for each
account,
and
each location also has a unique identifier.
I would like to have a form that displays all the basic
information
about
the insured account with some additional boxes that I can fill
out
to
update
information in the underlying location table.
eg, say I have an account that has locations all over the US.
I
would
like
to be able to find that account and then fill out additional
boxes
that
would
update the deductible values at all CA locations to 5%. So in
an
update
query the criteria would be CA and the update to field would
be
0.05.
I have tried so many things and just can't get this seemingly
basic
idea
to
work as part of a form.
Any help would be greatly appreciated.