Access 2K3 Append/Update ignore non null cells!

[bazdaa]

Access 2K3 Append/Update ignore non null cells!

Hi,

I have been tasked with completing an audit of approximately 10,000 items, to which I have generated a list of 40 questions (fields) for each record. I began collating the information in Excel, but found myself getting lost and even experienced major data loss on at least 2 occasions. I have now decided to ditch Excel and use Access 2003 instead.

I have imported my master spreadsheet, however as I have various contacts sending in their respective information in Excel spreadsheets with same types of fields, and also need to import data that has already been sent in. I’m thinking that it would be better for me to create update and append queries, especially as there is going to be a stage 2, where I will be requesting additional (field) information.

There is a big chance that some of the contacts will send in info for the same item (record), which means that there is a risk of duplication, which I have removed as I have a unique identifier which will be the primary key! Is that right or should it be indexed?

The main problem that I have is that I want Access to ignore the target cell if it has a value in it! Meaning that I would not like Access 2003 to overwrite the cell with valid data in it, with a blank cell! As I need the database to grow!

Can anyone suggest a way that I can do this please.

TIA

Bazdaa

[nico5038]

In a case like this, I import all data in one big table, but add the user and a datetime or better sequencenumber for each lot.
Thus I'm able to remove a lot when a user has second thoughts about his data and sends in an improved set.

Idea?

Nic;o)

[bazdaa]

Thanks,

The problem is however that I have numerous spreadsheets coming from various sources, with information that overlaps. i.e. Duplicate records with different data in the same fields!

Bazdaa

[nico5038]

Somewhere you need to have a unique identifier to detect duplicate rows.
By storing all data separated in "lots" you'll be able to match the duplicates and you can create a form to have this solved.
I don't see a way to solve this "fully automated", there will always be the need for human intervention to made the final record with the best/correct data.

Nic;o)