I have another update query problem. This time it should be relatively
straightforward.
(a) I want to automatically update the "phol" field in the "department"
table with 1's.
I also want to include two criteria,
(b) one is where the "workstatus" field in the "employee" field is
equal
to
"perm"
(c) the date field will be an open criteria specified in a date field
in a
form.
For (a) I am assuming "sheet1.Rate" would simply be replaced with "1"?
Not too sure about (b) and (c)
UPDATE Department INNER JOIN Sheet1 ON Department.employeeID =
sheet1.employeeID
SET Department.Rate=sheet1.Rate
What I want to do is press a button that will run the query and update
the
fields.
:
Hi,
From the User Interface, in the toolbar, or the menu, when you edit a
query,
you should find a button that allows you to change the query "type".
Right
now, you have a SELECT type. Change it to an update query type (not
making a
new query, BUT using the one that works right now). The grid will
change.
Bring the field to be update in the first line (by drag and drop, if
you
prefer, that will do fine to). That is ***probably*** the field rate
from
the table Department, from what I assume.
Next, in the update to line, of the grid, type the table name and
field
name
that actually holds the data that will update the field in the first
line
of
the grid. Use the syntax with [ ] like: [sheet1].[rate]
and back in SQL view, you should now have something like:
UPDATE Department INNER JOIN Sheet1 ON Department.employeeID =
sheet1.employeeID
SET Department.Rate=sheet1.Rate
Note that if you run that query, ALL the records in Department will be
updated! If this is what you want, fine, else, add a WHERE clause to
limit
the records to be updated.
Hoping it may help,
Vanderghast, Access MVP
okay, I have now changed it to the following and it works fine.
SELECT Department.*, sheet1.*
FROM Department INNER JOIN Sheet1 ON Department.employeeID =
sheet1.employeeID;
The "rate" from "sheet1" is now attached correctly and the query is
restricted only to those employees I want to update.
I am bit confused about the "update to" line. In your previous
reply,
you
state
and now, change the SELECT query to an update query. In the grid,
under
the
field Department.Rate, at the line Update To, add
[OtherTableName].[FieldNameSupplyingTheValue]
Shouldn't it be the other way round?
thanks
:
Hi,
You keep the * in the first try so you can get all the fields of
the
table(s).
Only the records from Department that have a rate value present in
sheet1,
under its rate column, will be kept;
only the records from Sheet1 that have a rate value present in
Department,
under its rate column, will be kept.
That is how an INNER JOIN works when there is no duplicated values
(in
one
of the table): it acts like an INTERSECTION, only keeping rows that
matches
the ON clause. Here, you used Department.rate=Sheet1.rate, so the
"criteria" to define the match is just that. If something else is
required
to "match up *correctly*", either you change the ON clause
criteria,
either
you add an extra WHERE clause ( to remove even more rows ). We
generally
write the criteria in the ON clause if it implies two tables, and
the
WHERE
clause if it implies just one table (two fields of the same table,
as
table1.starting<table1.ending, or a field of a table and a
constant,
as
table2.qty=0), but the main point (for outer join) is that the ON
clause
is
evaluated first while the WHERE clause is evaluated after the join
is
made.
Hoping it may help,
Vanderghast, Access MVP
message
Hello,
thanks for the help. I am a novice SQL user so
I am wanting to update the table (and I appreciate "sheet1"
probably
isn't
the most informative name but I will keep it for the time
being!).
In the select statement, do the asterisks remain?
SELECT Department.*, sheet1.*
FROM Department INNER JOIN Sheet1 ON Department.rate =
sheet1.rate;
A word of warning is that not all the employee numbers in
"sheet1"
are
in
"department". Should they be? After I have run the above code,
the
two
fields
from "sheet1" are attached to "department" in the query but (a)
not
all
of
the employee numbers from "sheet1" are in the query and (b) they
don't
match
up correctly.
:
Hi,
Remove the last parenthesis in the code I supplied, should be
DoCmd.RunSQL " UPDATE Department
SET Department.rate = [forms]![employee]![department
subform].Form.controls![rate]
WHERE Department.employeeID=[forms]![employee]![department
subform].Form.controls![employeeID]; "
With an SQL statement, you update the table, not the VBA Form,
so,
the
left
side of the SET *MUST* be a table and a field:
SET tableName.FieldName = ...
***** If the right part of the = is a field from another table,
then
you
use
an inner join,
but it is probably preferable to start with a SELECT clause:
SELECT Department.*, OtherTable.*
FROM Department INNER JOIN OtherTable ON Department.SomeFieldA =
OtherTable.SomeFieldB
where "OtherTable" is "the other table" you mention, but not
supplied
ita
name (unless it is sheet1 ? ), and "SomeFieldA" and "SomeFieldB"
are
the
appropriate fields making the "link" between the two tables, the
two
fields
defining the "lookup". I don't know what they are, given the
information
you
supplied.
Once you get that right (in design view, if you prefer), add the
WHERE
clause to limit the records in table Department that are
selected.
Once the right records are displayed, with the select, limit the
selected
fields to the one to be updated:
SELECT Department.Rate
FROM ... WHERE...
and now, change the SELECT query to an update query. In the
grid,
under
the
field Department.Rate, at the line Update To, add
[OtherTableName].[FieldNameSupplyingTheValue]
And then you have your query.
*****If the right side of the = is a control in a form, not a
value
from
another table, then use the appropriate syntax as previously
suggested
(without the ending mismatch closing parenthesis)
Hoping it may help,
Vanderghast, Access MVP
message
You are correct that the table to be updated is "department"
but
I
need
to
take the information I need from "sheet1". I thought the 2nd
line
would
something along the lines of:
