Ah, ok. I'd probably create a temporary table called something like
User_Rank_Tmp with just the User_ID and Rank_ID columns in it.
Then run
INSERT INTO User_Rank_Tmp (User_ID, Rank_ID)
SELECT Users.User_ID, Rank.Rank_id
FROM Users INNER JOIN Rank on Users.User_Rank = Rank.Rank_Name
That will pop
Yes, that is what I am trying to do. Once that is done I will change that
varchar to a number field. I imported the users from an excel spreadsheet
and am being lazy and trying to let access do the work, but it is only 34
people so I will probably just do it by hand.
On Thu, Jul 29, 2010 at 10:27
I have the two tables rank and users. Right now users has the actual ranks,
pvt, pfc, ssg, and so on. I want to change that to the rank ID from the rank
table. Rank table has rank_name and rank_id. I have a relationship between
rank.rank_ID and users.rank.
On Thu, Jul 29, 2010 at 10:24 AM, Judah
This is still kind of weird looking to me. The query as G put it would
seem like it is trying to convert the current rank that a User has
from a text value to an id of the equivalent row in the Rank table.
That would, amongst other things, mean that you're going to be now
storing all numeric value
The subquery SELECT Rank.Rank_Name FROM Rank is probably returning
more than one record because you aren't doing any filtering on it. You
can't use a subquery that returns multiple values in most areas of a
query. This query also looks like it is circular. You seem to be
trying to set the Users Ra
On Thu, Jul 29, 2010 at 11:09 AM, Bruce Sorge wrote:
>
> UPDATE Users
> SET Users.User_Rank = (Select Rank_ID FROM Rank
> Where Users.User_Rank =
> (Select Rank.Rank_Name
> FROM Rank));
>
Will an Update...From work? Something like:
UPDATE Users
SET Users.User_Rank = Rank.Rank_ID
FROM Users
JOI
Can you join Users and Rank?
On Thu, Jul 29, 2010 at 12:09 PM, Bruce Sorge wrote:
>
> OK, so I have been out of the game for a bit and my platoon sergeant wants
> me to build an MS Access form for our platoon info. So I am trying to do a
> simple update query and I get the message Operation must
OK, so I have been out of the game for a bit and my platoon sergeant wants
me to build an MS Access form for our platoon info. So I am trying to do a
simple update query and I get the message Operation must use an updateable
query. Here is the query:
UPDATE Users
SET Users.User_Rank = (Select Ran
