Excel to Access Advice

[daithiboy]

Hi All,

I'm new to access and looking for some advice on how to redo an excel staff training database in Access.

In excel, I have a userform that will search a worksheet of staff members (with ID number, department, position etc) and add them to an "Attended" worksheet, which contains training date, training type, attendance date, location etc.

This exercise is about to get much bigger (more training types and many more people) and I was wondering what the basic setup would be for this type system in Access. From my reading and google searches, I think I might need a "Staff" table (with name, ID number, Position), a "Training" table (with Training ID, type, location etc) and an "Attended" table which references the details from the "Staff" and "Training" tables.

I could not be newer to Access so I'm just looking for some guidance before I dive into anything.

Many thanks in advance folks!
Dave

 

[Kyle123]

Your approach sounds practical, the table naming is a little confusing, but that's just preference, I'd have gone with something like staff, courses, trainingLog but the structure would be the same.

I think your structure is fine from your description

 

[Micron]

I could not be newer to Access

There's much to consider, then. Normalization is probably the most important; perhaps followed by naming things properly. Suggest you do your homework as you need to forget pretty much everything you know about Excel when it comes to creating a database. As for the structure - I think it depends on if and how you want to control data input. You might need lookup tables for locations and training types. Your idea of the field lists for each table may be more expansive than what you reveal here. If not, it's incomplete IMHO since I would strongly advise against putting first and last names together as one field - amongst other things (you don't mention date fields either, for example).
Care to do some reading?
Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/2008/12/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/2009/01/entity-relationship-diagramming-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

 

[daithiboy]

Thanks for the responses gents. Thanks for all of the advice Micron, I think this is the kind of info I need. I will ideally be using forms to input the data and from what I've noticed in tutorials, naming seems crucial. Many thanks for all of the references Micron. I look forward to diving into them!!

Many thanks,
Dave

 

[Micron]

You're welcome. Good luck!