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

Hi

Welcome to UA (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

I don't know how experienced you are with Access, so forgive me if this is too basic an answer for you......

Access is a totally different beast to Excel. You store records in Access in related tables and not in a single table which looks like an Excel spreadsheet. To get info like you describe into a useable format for Access, you'd need to run probably a series of queries to organise (structure) your data. You can do it by first importing eveything into a single temporary table and then run your queries on that table until your records are in their relevant related tables.

There's a tutorial somewhere in the archives which I can't find at the moment, but will post a link to it when I get a minute.

Also, it's generally good practice to have your primary key in all tables an Autonumber datatype. You can still have an indexed unique identifier as you suggested........

I'll see if I can find the link for you which will give you a bit more info............

Many thanks for the swift reply Alan.

The problem that I have, is that I have at least 50 spreadsheets initially, with tons more on the way! Also as the data is mismatched, its hard to collate in one table, as there will be duplicates (I'm thinking) or maybe even data loss!

Seeing as time is against me at the moment! I think I have come up with a workaround, untill I get more skilled up with Access. The workaround is;

*Clean up and import the initial master spreadsheet into Access 2003 as a table.
*Clean up and to link to another spreadsheet as a table.
*Create a select query using the 2 tables.
*Create a macro, and enter the criteria "Is Null" in the first field.Convert this into an update query. Then once completed, do the same for the 2nd field, with the macro still running.
*Then once completed for all 40 fields, stop the macro from recording, and simply use this to import the spreadsheets, until I come up or find something better.

What do you think?

P.S. I am a newbie!

Bazdaa

It doesn't matter on your skill level, the good news is that you're in the right place........you'll always find a helping hand from someone at UA to guide you at your own pace (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)

Now, the first thing you need to do to develop a reliable, manageable solid database is to set your table structure up. That generally (well, in my case anyway) means turning off the computer and getting out a pencil and several pieces of paper to design the db. It's THE most important thing in creating a successful db to have your structure correct before worrying about queries, forms etc etc. There's some links below for you which should give you an insight into normalising and getting a sound structure. Have a read through them, then either post your proposed structure or some more info (or an example of the spreadsheet you're importing - doesn't have to have real data in it. Dummy data will do.) on what you're dealing with data wise and we can take it from there...........

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS Basics for Programming
• Database Answers (free models)
• MSKB 283878: Description of the database normalization basics
• The Evils of Lookup Fields in Tables

Sounds like a plan, and believe it or not I was going to do the paper thing anyway. However as I need to move fast on this! I am thinking of importing as much data as possible into a temp DB, then in the background develop (wow, me developing!) a true database, in order to move forward and who knows perhaps have users input thier data in via a form (who knows).

The main is that I need to be able to export the data to a Excel spreadsheet as a minimum to the SQL team, for them to inport into thier database, when I have collated the info at the end.

P.S. Hopefully the macro I'm creating wont grind my machine to a halt!

Bazdaa

Go for it and good luck with your project.........you know where to come for a helping hand if you need it (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)