Re: update query: still having problems

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



"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E5B5056A-1ACF-4D7A-B167-30EB8C9E185B@xxxxxxxxxxxxxxxx
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.




"Michel Walsh" wrote:

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

"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:200C18BB-FC7C-4512-A141-6FA9EEF10DBC@xxxxxxxxxxxxxxxx
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:

SET [forms]![employee]![department subform].Form.controls![rate] =
[sheet1].[rate]

Your code throws up a syntax error: Invalid SQL statement.

"Michel Walsh" wrote:

Ha, you use CurrentDb? try from the User Interface or with DoCmd.
CurrentDb
does NOT solve FORMS!... syntax for you, while the UI and DoCmd does.

Also, for table and fields, preferable to use the dot syntax; the
bang
syntax is generally reserved to VBA objects.

Also, your table, to be update, is Department? so try:


DoCmd.RunSQL " UPDATE Department
SET Department.rate = [forms]![employee]![department
subform].Form.controls![rate]
WHERE Department.employeeID=[forms]![employee]![department
subform].Form.controls![employeeID]); "


(in one line, or with the appropriate line continuation)





Hoping it may help,
Vanderghast, Access MVP


"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8761BF28-3C81-4F66-9B87-7E67C9FE9857@xxxxxxxxxxxxxxxx
This is what I now have:

UPDATE Department AS Subform
SET sheet1!rate = [forms]![employee]![department
subform].Form.controls![rate]
WHERE ([sheet1]![employeeID]=[forms]![employee]![department
subform].Form.controls![employeeID]);

I am wondering whether the "SET" line should be the other way round.

I get parameter boxes for

sheet1!rate
sheet1!employeeID
forms!employee!department

"Michel Walsh" wrote:

Hi,

Try the full syntax:

FROMS!formName!SubFormControlName.FORM.CONTROLS!controlNameInTheSubForm


(all cap word are keywords).


Note that you use the name of the control of the subform control,
NOT
the
name of the form embedded into it (they MAY differ, and obviously,
if
so,
it
is important to use the right one).



Hoping it may help,
Vanderghast, Access MVP

"scubadiver" <scubadiver@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:17D7060D-4AC4-4178-85DD-1F0B8691B575@xxxxxxxxxxxxxxxx
I want to update a field called "rate" in the "department" subform
table
from
a field called "rate" in a temporary table called "sheet1".

I am getting parameter value boxes. Here is my SQL.

UPDATE Department
SET [sheet1]![rate] = [forms]![employee]![department
subform].Form![rate]
WHERE ([sheet1]![employeeID]=[forms]![employee]![department
subform].Form![employeeID]);











.