I have a management system that runs on a SQL 2000 DB. I’m fairly new to the DB world, and am trying to create a DB in Access 2010 using a linked table to my SQL 2000 DB. Here’s what I trying to accomplish:



In my SQL DB, I have a table that lists a job#, process number, and process hours. In Access, I’m linking to that SQL table. In Access, I want to create a table that will hold the same 3 fields listed above, in addition to a new field called “Multiplier” (a numeric value that will serve as a multiplier to the process hours field). So, in Access, I have my linked table with the 3 fields I need. In addition, I’ve created a new table called “multiplier”, and create 4 fields: job#, process number, process hours, and multiplier. The relationship between the 2 tables will be job# and process number.

My goal is to create a form where I can look up a job# from the linked table, and have a field in the form where I can enter a multiplier. When I click Save, I want the data from the linked table, to populate the fields in the multiplier table (job#, process#, and process hours) in addition to the multiplier. When the data in the linked table is refreshed, I don’t want anything to change in the multiplier table.

It seems like this should be fairly straight forward, but I’m struggling with the method on how to accomplish this. I’ve played around with append queries, update queries, etc. I can’t figure out a good solution.

Can someone point me in the right direction?

Thanks,

Troy