Update Query

  • Thread starter Thread starter Dale Brown
  • Start date Start date
D

Dale Brown

I have never done an update query in Access yet. Here is what I have tried
but it will not work. Can anyone help with the syntax?

update classinfo
INNER JOIN activeshow ON ActiveShow.ShowID=ClassInfo.ShowID
WHERE classinfo.classnumber Between forms!ClassFill!begnbr And
forms!ClassFill!endnbr
set scoreid = (select scoreid from classinfoscore)
 
You'll need to tell us what you're trying to do, including what your table
structures are and such.

It's not clear from the SQL statement that you've posted. Is the
(select scoreid from classinfoscore)
clause going to return just one record? or multiple records?
 
Ken,

The select scoreid from classinfoscore will return just one record. I am
trying to update the scoreid field in a number of records in my classinfo
table. The classinfoscore has 1 record with one field scoreid in it. This is
a temporary table set based on what a user selects in a listbox on a form.
The classinfo table has classes from many different shows. The joining on
the activeshow table allows me to tell which show the user is working on.
The user will also enter a starting and ending classnumber on the form. This
is where the where clause comes into play. Let me know if you need more
information or if this is enough.

Dale
 
How does the joining to activeshow table tell you which show the user is
working on? The query, as you've written it, would have no reason to join to
activeshow table *unless* you only want to update records in classinfo table
that have one or more child records in activeshow table.

I'm guessing that there is a control on the form that has the "activeshow"
value that you need to be using?

Also, explain what you mean by ""will not work". What happens when you try
to run this query?
--

Ken Snell
<MS ACCESS MVP>
 
When I try to run the query I get an Syntax error in update statement
message. The activeshow table is a table that has one record with a showid.
The user sets the active show with a form like you thought. Then everything
that is done with classes is for only that show. The inner join will keep me
from getting any other classinfo records from other shows.
 
Are you trying to run this from the query window? or from code? Normally,
when you get an syntax error in the SQL view, often the offending word in
the erroneous clause will be highlighted (or the beginning of that clause
will be highlighted).

If you're trying to run this from code, where you're building the string via
VBA steps, then post the code that you're using. Often, the problem in this
case is that you are not adding spaces into the string as you build it (not
uncommon error).

Assuming that this is the SQL statement from the SQL view of the query, try
this:

update classinfo
INNER JOIN activeshow ON ClassInfo.ShowID=ActiveShow.ShowID
WHERE classinfo.classnumber Between forms!ClassFill!begnbr And
forms!ClassFill!endnbr
set classinfo.scoreid = DLookup("scoreid", "classinfoscore");
 
This is from a sql view. The offending word is the where. This happens with
yours and mine.
 
Oh sorry.. it's late here and I overlooked that the WHERE clause goes after
the SET clause.


update classinfo
INNER JOIN activeshow ON ClassInfo.ShowID=ActiveShow.ShowID
set classinfo.scoreid = DLookup("scoreid", "classinfoscore")
WHERE classinfo.classnumber Between forms!ClassFill!begnbr And
forms!ClassFill!endnbr;
 
That did it. Thanks
Ken Snell said:
Oh sorry.. it's late here and I overlooked that the WHERE clause goes
after the SET clause.


update classinfo
INNER JOIN activeshow ON ClassInfo.ShowID=ActiveShow.ShowID
set classinfo.scoreid = DLookup("scoreid", "classinfoscore")
WHERE classinfo.classnumber Between forms!ClassFill!begnbr And
forms!ClassFill!endnbr;
Click to expand...
 
Back
Top