As I understand some input that I have received regarding table design... Normalization seems to be very very important. However I have concerns with my Test Results Table. If I do what I have been suggested to do then the Results table with be veeeery long! Long is my word because the table would only have 6 fields wide but to add each data result would add a record to the Test Results table. What is alarming to me is that each individual sample could have a varying number of test results (which is why I don't go left to right with multiple results per sample) and I could be at 10,000 records in a month's time for say maybe 1,000 samples! Extrapolate that number and I'm looking at 120,000 in only one year... imagine 5-10 years! I am worried that with the all of results creating so many "records" - that either the performance could bog down quickly with multiple users - or I would reach some ACCESS 2010 record limit and crash it. This project that I have undertaken has gotten the attention of executives in my corporation and they are expecting this project to work and be great... so failure is NOT an option.
Please. Any guidance?

Assuming the 6 fields are numbers then you are not going to have a problem with database size.

A million records of 6 numbers will not be more tha 60mb.

If you aren't used to data normalization then doing this may seem counter intuitive at first. Persevere! Soon you will find it more difficult of data in a non-normalized way. Don't fret about record numbers, you are unlikely to reach the limits and as for speeds. If you don't normalize your data you may have few records, but more columns (and more empty columns) and Access working through all the badly sorted and redundant data will make it much much slower than a large number of records could.

Good luck with your project and impressing the bosses

I'll chime in: you're fine. 120,000 records is a very reasonable size for a table, given proper indexing and design. 1.2 million records is still very much within Access' powers, and will be even more so with ten years of development of new hardware and software upgrades. 1.2 BILLION records is managable for SQL/Server or other client/server databases; my sister works with an Oracle database containing every airline reservation made for a dozen airlines. You're not scratching the surface of "big" yet!

There is no explicit limit to the number of records in an Access table; the only size limit is that your entire database - .mdb or .accdb file, including all tables, forms, reports, etc. - cannot exceed 2GBytes. Given that you could probably fit the text of the Encyclpedia Britannica in one gigabyte, you're not going to have any trouble!