Append to Table Where...then I'm lost.

[Mississippi Girl]

I am not an SQL or database person, but somehow am always dragged in to these situations. So, here's the story...
We have a task order system. The task order system generates a task order number. The task order number format is # R00, so task order 10 would be 10 R00; task order 2 is 2 R00. Task orders can be revised and when they are, the revision number changes, so task order 10 R00 can become 10 R01 and task order 2 would become 2 R01. When a task order is revised, the only thing that changes in our tracking database (this is where the question is coming in at) it the task order number and the dollar value.

Here's my quandry....I want to append records from the linked spreadsheet "tbl_Daily" to the task order table "tbl_TO" that are not the already in the "tbl_TO". Basically, I want to ignore the R## part of the task order number. I've gotten this far with my SQL:

INSERT INTO tbl_TO ( [Task Order Number], [Contract Number], [Task Order Title], [Current Task Step], [Date Requested], [Completion Date], [Last Modified], [SAP WBS], [Task Value] )
SELECT tbl_Daily.[Task Order Number], tbl_Daily.[Contract Number], tbl_Daily.[Task Order Title], tbl_Daily.[Current Task Step], tbl_Daily.[Date Requested], tbl_Daily.[Completion Date], tbl_Daily.[Last Modified], tbl_Daily.[SAP WBS], tbl_Daily.[Task Value]
FROM tbl_Daily LEFT JOIN tbl_TO ON tbl_Daily.[Task Order Number] = tbl_TO.[Task Order Number];

Of course, this adds all the previous revisions of the task orders. I manually go in and delete the old ones right now, but this database is only going to grow and I don't want my entire job to become that of a record deleter.


Any suggestions? Can I append records based on only a portion of a field? I thought about using the number of characters, but that will only work for the first 99 task orders.

Also, the task order number is not unique - it's the task order number + the contract number that makes a record unique. Do I need to add a join on the contract number field as well?

Thanks for your help.

 

[Micron]

Any suggestions? *Can I append records based on only a portion of a field? I thought about using the number of characters, but that will only work for the first 99 task orders.
Also, the task order number is not unique - **it's the task order number + the contract number that makes a record unique. ***Do I need to add a join on the contract number field as well?

*Yes, if you can come up with an expression that removes unwanted characters first. However, as you pointed out, this is subject to variations - some of which you can't predict now, such as if the format changes some day.
**You need a field or combination of fields in the target table that are a composite primary key or composite index. Some advocate the latter over the former, but I've been using composite primaries for years without any noticeable issue, so I'm not sure why. Anyway, it seems your composite would be a combination of task order number and contract number. In code or macro, you turn off warnings before appending the records and any that violate the composite key will not be appended. Remember to turn warnings on after the code or macro is finished appending.
***Another join will not help to exclude records that are already in the target table.