Excel to Access: Conceptual question

[dcfrancis]

Good day,

I have an Excel table capturing employment data for companies (see attached). I am struggling to see how to design an Access table to capture this information. For each company I need a record of employment numbers by gender, skill level and race [in the attached, A, B, C and D are the four race categories](this is mandated by government regulations.) Can anyone help here?

Thanks in advance.

[R_Badger]

You are looking at the problem from the wrong angle.

When it comes to database they store data i.e:

Example from your zip

Tbl_employment

No_people, Race, Skill_level, Gender

4, A, Unskilled, Male
3, B, Skilled, Female

From this you could then have a relationship between the race (a,b,c,d for instance could be a primary key) and another table containing further data, e,g a Race_description field.

The difference is between what you want to see and how best the data can be stored if that makes sense.

You could extract the same information from both, but the database is very easily expandable, say you want to add more races, in your excel its another 2 columns, in access its one record in a related table, Tbl_race.

Same for skill level, etc etc etc.

Do learn a bit more Orange tends to have some good links he post, but for the life of me I cant remember the last time he posted them ( I know he did it recently too).

Take a look at:
http://www.barrywise.com/2008/01/dat...gn-techniques/

If i havent made sense please post back and I'll try to decrypt my ramblings

[dcfrancis]

Thanks Badger. That makes a lot of sense.

Now I need to keep this record for many companies and for multiple years. In essence, I need one of the tables (as in the attachments) for each company for each year.) How do you think I should do this? Under Tbl_employment should I add Company Name and Year?

I guess I am aiming to be able to create a form that resembles the excel table I attached, and have the data then written to the database table in the format you suggested above.

Thanks again.