Trouble with simple Update query...

[Guest]

http://www.kennyshardwood.com/query.gif

I've got my update query setup as shown in the picture provided. When I go
to run it it's telling me "you are about to update 0 rows." What am I
missing here?

I'm simply trying to take the data from the Hardwood tables' InventoryID and
Description fields and copy it into the Products tables' ProductID and
productDescription fields.

Any informaiton would be greatly apprecaited. Thanks!

 

[Rick B]

First, you would need to create the link or relationship between the two
tables. Second, you would not want to do this. Putting the description in
both tables defeats the whole purpose of a relational database.

When you need the description, just put both tables in your queries (but
make the link) and pull the description from the product table.

 

[Guest]

I should have explained a little futher..

I'm simply trying to get the data from the Hardwood table into the Products
table so that I can get rid of the Hardwood table. I'm in the very beginning
stages of creating the database for this web application. All of the tables
are completely empty. I was given an excel spreadsheet that contains the
InventoryID and Description. I want to get this data into the products table
for use with my application and then remove the 'temporary' Hardwood table.

 

[Rick B]

Ok.

If the tables are all empty, then I'm not sure how you will get the data
from one table to the other.

Once all the data is in the two tables, you would create your link so it
knows which product description to pull. In other words, how will it know
for Product 123, which description to pull? What field in the products
table links the record to the hardwoos table? You have to tell Access how
to find the appropriate hardwood description for the product.

Once you draw thos links, then your UpdateTo looks like it will work.

I'm still not 100% sure that you would not want to keep both tables and
relate them. Let's say one of your Descriptions is "Hand Cut Brazillian
Cherry 3/8inch by 3inch. Will you have more than one record in the
"products table that will contain those values? If so, you should keep both
tables. If not, then you are most likely doing the right thing.

 

[Guest]

I've also got a productDetails table...

Right now the products table is all empty. The hardwood table holds the
inventory IDs and descriptions that I imported form the excel sheet. I don't
really have any related fields with these 2 tables. I just want to basicaly
copy and paste all of the data from the hardwood table into the products
table in the corresponding fields and I'll be set.

 

[Guest]

I accidentally responded to my own post...not sure if it matters...but just
in case:

I've also got a productDetails table...

Right now the products table is all empty. The hardwood table holds the
inventory IDs and descriptions that I imported form the excel sheet. I don't
really have any related fields with these 2 tables. I just want to basicaly
copy and paste all of the data from the hardwood table into the products
table in the corresponding fields and I'll be set.

 

[Rick B]

That is an APPEND query, not an UPDATE query.

 

[Guest]

I tried to create a join in the update query by connecting ProductID with
InventoryID in the 2 tables. I then select to use ALL data from Hardwood
table and only matching data from Products table. When I try and run this it
does show the correct number of records to update, 359, but it says there's a
key violation and cannot run the query.

I then tried an append query and I get the same exact message.

???

 

[Rick B]

Sounds like you have a field required but yor append query won't fill that
field in. If you will only end up with two fields filled in, then those are
the only two fields that could be required.

Personally, I'd just use the imported table (if your PRODUCTS table is
empty) and make changes to it there. Why move records from one table to
another table when that second table is empty?

 

[Guest]

LOL. that makes perfect sense. That's exactly what I did and all is well. I
don't know I didn't stop to think about that.

Thanks!