Append or Update Query and How?

[pathetic]

I have been fortunate to have received some great advice here and hope someone can help me with this last piece. I know I need to do a Query, but not sure if I need an Append or an Update, and how to make it happen.

I have two tables:

tblProduct contains two columns of data:
ProdID (1, 2, 3)
Description (red, yellow, blue)
1=red
2=yellow
3=blue

tblResults contains these columns:
Location (north, south, midwest)
Product (product names)
ProdID (1, 2, 3)
Description (not yet populated... this is where I want query results to come in)

How do I get Description from tblProduct into Description in tblResults. Where ProdID=1 in both tables then the Description in tblResults should populate "Red"

Seems simple enough but I sure don't know how to make this happen with SQL or the Access (2002)Query interface.

Thank you.

 

[PHV]

First, why storing a derived value ?
Take a look here:

http://www.abcdataworks.com/fundamentals.htm

Anyway:
UPDATE tblResults INNER JOIN tblProduct ON tblResults.ProdID = tblProduct.ProdID
SET tblResults.Description = tblProduct.Description;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pathetic]

PHV,

Thank you so much! I also read the informative article at the link you provided. I'm trying to get my arms around all of this at once and I appreciate the direction and suggestions.

Cheers!

 

[pathetic]

PHV,

Is there any way to automate this query so every time a new record is entered the tblResults table the query runs? Or must it be a manual effort to run the query?

Thank you.

 

[PHV]

My opinion is to NOT store tblProduct.Description in the tblResults table as you can easily retrieve it with a query:
SELECT R.Location, R.Product, R.ProdID, P.Description
FROM tblResults R INNER JOIN tblProduct P ON R.ProdID = P.ProdID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pathetic]

Hi PHV,

I need it in the tblResults as that's where I will generate a CFGrid from. tblResults contains much much more data than just the ID and Description. I know from this description and my inquiry you may not be able to realize the full application and it's intention. I sure appreciate you heading me in the right direction.

Regarding the Query though, is there a method I can implement that would make the query update each time a new record is added to the table?

Thank you again.

 

[PHV]

Can't you generate a CFGrid from a query instead of a table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pathetic]

hi PHV,

yes, CFGrid is generated from a query, Select ALL from tblResults and that's what I need. I could modify the query to obtain info from both tables but what would be the efficiency? The purpose of tblProduct was to identify ID and Description for a dynamic select statement on my web form. I knew in the design process that this table would not be my main table to pull all results into, and that's were tblResults comes in.

Anyway, back to my question about automating the query. Is there a method I can put in place to automate the query whenever a new record is added to the table? Or must I plan to manually run the query?

I do thank you very much.