R – adding a checkbox against each item of a list in the view ( sharepoint 2007 )

1. have the attachments upload as part of the Access form.
2. load attachments into a Document Library
3. Check off users like they are currently being done
4. Add hyperlinks to the attachments uploaded in step 2 to the Description (rich text) field. (maybe done automatically in steps 1-2)
5. Leave TaskQueue table alone.

This way, 0..n documents can be included. The task list just stores structured data, and the documents are stored in a document library once, and you don't have runaway growth when attaching 1 document to 5 different tasks (resulting in 5 copies of the document).

Huge list performance

You may want to read "Scaling to Extremely Large Lists and Performant Access Methods" and "Best Practices for LARGE SharePoint Lists and Documents Libraries".

Another thing this article does not mention that adding list items with SPList.Items.Add, because on large list it's a huge performance penality. What you do is build efficient query that returns no items and then add item to that collection (somwhere i was reading that webservices perform good on adding item, however i can't find that article no more).

You can also see some tests (or other tests) on how huge lists perform.

As for duplicates

You may want to create Scheduled job (SPJobDefinition) that runs somwhere at night and checks for duplicates.

Better idea than looping all SPListItem's and then Query list for each item to check for duplicates would probably be to get a DataTable (SPListItemCollection.GetDataTable()) for all items and use some technique to determine duplicates.

As for views

Filter items, order to see relevant ones and define your RowLimit. That's the key for views - you just need most relevant items, don't you?