Update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Can't get this query to work. Any ideas ?

Have 2 tables

RANK has 3 records TEST is 1 record

Store R 1 2 3
------ ----- ----- ----- -----
100 1 100 120 140
120 2
140 3

I empty "Test", then try to do an update query

UPDATE RANK INNER JOIN TEST ON RANK.R = TEST.[1]
SET TEST.[1] = [RANK].[STR]
WHERE (([RANK].[R]=1));

Query comes back saying update 0 rows. Any ideas ?

Thanks,

Mike
 
Dear Mike:

If the table TEST does not have any rows in it, an UPDATE query cannot
do anything. Do you intend for TEST to have just one row? It seems
to have that appearance, but your INNER JOIN makes it look like there
would be separate rows for each rank value, which are columns in your
example. You cannot choose columns with an INNER JOIN, only rows.

All the above is guesswork on my part about what your thinking may
have been.

Now, to a solution. Create a simple crosstab query on RANK. I
recommend you not have a table TEST, just a crosstab that generates
its appearance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top