Update Query

[LMRollins]

I know just enough about Access to be dangerous but this one has me stumped. This is what I need to do.

I have a table from Visual called Part. I have imported into Access. I don't want to change this table. I then have created another table called SetPrice which would relate to the Part table by part_id. I want to be able to populate table SetPrice with the Part ids from the Part table without messing up the data in that table. I need my sales person to see the Part_id and be able to put in his pricing in the SetPrice table. Can this be done? Please help?

 

[jhall156]

If I read you correctly you just want to populate the part_id field in a new table (SetPrice) with all the part_id's from the Part table. If this is so, then try this SQL.


INSERT INTO SetPrice (part_id) SELECT part_id FROM Part JHall

 

[LMRollins]

Yes, but I also want it to remain update with any new part numbers that may show up in the Part table. Would this statement cover that?

 

[jhall156]

Are you saying that you want to periodically update the SetPrice table with any part_id's that don't yet exist in it?

If so try

INSERT INTO SetPrice (part_id) SELECT source.part_id FROM Part source WHERE NOT EXISTS(SELECT test.part_id FROM SetPrice test WHERE test.part_id = source.part_id) JHall

 

[LMRollins]

Basically the SetPrice table is empty until the first update but whenever someone goes into this table to see pricing, I need it to automatically update from the part table any parts that are not yet in table setprice. Do I enter this SQL statements under the Query. SQL View?

 

[jhall156]

Yessir, (or ma'am) JHall

 

[LMRollins]

I finally got it to work. Thank you so very much. There is a guy that I work with who uses Access but I never could understand how he was trying to tell me to do it.

 

[LMRollins]

I take that back. I need help with one more thing. Let's see if I can explain this. Ok, now I got the new table updating like I want. Let's say that my sales guy wants to enter new pricing for the following part:

Part. ABC123 Price1: 235.00 Price2: 181.00 Date: 3/1/01

Next month he goes back to that same part and enters 2 new prices. In the SetPrice table it will overwrite the amounts entered on 3/1. Is there a way to create another table that would show a history for this part of the prices entered each time he would change them, like such:

Part Price 1 Price2 Date
ABC123 235.00 181.00 3/1/2001
ABC123 240.00 190.00 4/1/2001