tables within tables

[Sheepstealer]

HELP I am a beginner to using access to this depth. I have been given a project at work. We have a excel spread sheet for each task, however there can be multiple entries per area , ie stock codes and serial numbers / job.
When I enter the data into access only the first field in each category is entered into the relevant table 1 stock code 1 serial number.
So how or is it possible to get access to take several stock codes and or serial numbers into the relevant record fields

Both are 2003 versions

All help very gratefully recieved....

 

[ejstefl]

Excel and Access are meant for different purposes, and as such they have different ways of working with them. One of the big differences is in how they handle data. Go onto this forum or google or whatever and do a search for "Database Normalization" and read all you can on it. That should help you to get a start on your database.

 

[The_Doc_Man]

ejstefl is right but didn't give you quite enough to consider.

When data has the property you describe, you need to consider what are called parent/child relationships in Access. Database normalization is a topic that shows you the rules about splitting up data where some parts are in common but other parts are unique.

Normalization saves space at the cost of a little bit more manipulation to handle what is called a JOIN. The benefit comes because when you normalize a table, you quickly see what is part of the parent and what is part of the child tables. Your conceptualization becomes easier once you get the hang of it.

Now the bad news. "Getting the hang of it" often balks Excel users because they see everything in flat sheets. The "relationships" don't show up so clearly and that is what gets you confused really fast. But eventually you "see" it with a blinding flash of insight and from then on, you will have much less of a problem making relationships work. So don't be surprised if at first you don't get it.