Ms Excel versus Ms Access

[ChantalB]

Hello,

I work for a the doctors' HR department in a hospital. This fall, we will have to review our systems and make a lot of changes. I was wondering if we should consider changing our database made in an Ecxel spreadsheet to Ms Access.

Ultimately, we would like to be able to keep data on retired members as well as current ones. I would like to have a field that fills with the current date when personnal information changes for a doctor. We need to be able to create letters with somekind of mailpost (like Ms Word) to selected members. These letters then need to be converted into PDF with password to protect the image of the signature on the lettre.

The database needs to be shared by a few people but can only be updated by one or 2 people. The fields listed in blue should only be viewed by me.

I would appreciate any input you may want to share me before I have to present this file to the decision makers.

Thank you so much for your help!

Chantal


Field Name Field size, description, limit list

[TABLE="width: 766"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Permis[/TD]
[TD]2 digits-3 digits or 3 digits-3 digits[/TD]
[/TR]
[TR]
[TD]Titre[/TD]
[TD]Dr or Dre or M. or Mme[/TD]
[/TR]
[TR]
[TD]Nom[/TD]
[TD]Max 25 letters[/TD]
[/TR]
[TR]
[TD]Prénom[/TD]
[TD]Max 25 letters[/TD]
[/TR]
[TR]
[TD]O/S[/TD]
[TD]O or S[/TD]
[/TR]
[TR]
[TD]Type de permis[/TD]
[TD]Régulier or Restrictif or Temporaire[/TD]
[/TR]
[TR]
[TD]Statut[/TD]
[TD]Actif or Associé or Conseil or Honoraire or Actif à venir or Associé à venir or Actif temporaire or Associé temporaire[/TD]
[/TR]
[TR]
[TD]# Assurance[/TD]
[TD]Max 9 digits[/TD]
[/TR]
[TR]
[TD]Date assurance[/TD]
[TD]YYYY-MM-DD[/TD]
[/TR]
[TR]
[TD]Département1[/TD]
[TD]Max 72 letters - People can be part of 2 or 3 different departements, hense why we have a département 2 and a département 3[/TD]
[/TR]
[TR]
[TD]Service1[/TD]
[TD]Max 45 letters - There are many services within each of the department. People can be part of more than one service from the same departement, hense why we have a service 2 and a service 3[/TD]
[/TR]
[TR]
[TD]Département2[/TD]
[TD]Max 72 letters - People can be part of 2 or 3 different departements, hense why we have a département 2 and a département 3[/TD]
[/TR]
[TR]
[TD]Service2[/TD]
[TD]Max 45 letters - There are many services within each of the department. People can be part of more than one service from the same departement, hense why we have a service 2 and a service 3[/TD]
[/TR]
[TR]
[TD]Département2[/TD]
[TD]Max 72 letters - People can be part of 2 or 3 different departements, hense why we have a département 2 and a département 3[/TD]
[/TR]
[TR]
[TD]Service2[/TD]
[TD]Max 45 letters - There are many services within each of the department. People can be part of more than one service from the same departement, hense why we have a service 2 and a service 3[/TD]
[/TR]
[TR]
[TD]Autres[/TD]
[TD]Here I created codes to represent de multiple departments in ordre to be able to select desired departments with more ease. The departments where membres could belong to another department at the same time were listed separately (AN, SI, ♥, PN)[/TD]
[/TR]
[TR]
[TD]AN[/TD]
[TD]Here I created codes to represent de multiple departments in ordre to be able to select desired departments with more ease. The departments where membres could belong to another department at the same time were listed separately (AN, SI, ♥, PN)[/TD]
[/TR]
[TR]
[TD]SI[/TD]
[TD]Here I created codes to represent de multiple departments in ordre to be able to select desired departments with more ease. The departments where membres could belong to another department at the same time were listed separately (AN, SI, ♥, PN)[/TD]
[/TR]
[TR]
[TD]♥[/TD]
[TD]Here I created codes to represent de multiple departments in ordre to be able to select desired departments with more ease. The departments where membres could belong to another department at the same time were listed separately (AN, SI, ♥, PN)[/TD]
[/TR]
[TR]
[TD]PN[/TD]
[TD]Here I created codes to represent de multiple departments in ordre to be able to select desired departments with more ease. The departments where membres could belong to another department at the same time were listed separately (AN, SI, ♥, PN)[/TD]
[/TR]
[TR]
[TD]Privilèges en …[/TD]
[TD]Max 802 letters (memo field)[/TD]
[/TR]
[TR]
[TD]Engagement académique[/TD]
[TD]Yes/No[/TD]
[/TR]
[TR]
[TD]Engagement règlements[/TD]
[TD]Yes/No[/TD]
[/TR]
[TR]
[TD]Formation continue[/TD]
[TD]Yes/No[/TD]
[/TR]
[TR]
[TD]Remarques formation continue, assurance, poste réseau[/TD]
[TD]Memo field[/TD]
[/TR]
[TR]
[TD]Établissement Primaire[/TD]
[TD]Max of 60 lettres - This could be coded and then the info would be looked up in antoher table[/TD]
[/TR]
[TR]
[TD]Adresse1-Primaire[/TD]
[TD]Max of 60 lettres - This could belooked up in antoher table[/TD]
[/TR]
[TR]
[TD]Adresse2-Primaire[/TD]
[TD]Max of 60 lettres - This could belooked up in antoher table[/TD]
[/TR]
[TR]
[TD]Adresse3-Primaire[/TD]
[TD]Max of 60 lettres - This could belooked up in antoher table[/TD]
[/TR]
[TR]
[TD]Téléphone-Primaire[/TD]
[TD]3 digits "space" 3 digits "-" 4 digits - This could belooked up in antoher table[/TD]
[/TR]
[TR]
[TD]Poste-Primaire[/TD]
[TD]4 or 5 digits[/TD]
[/TR]
[TR]
[TD]Secrétaire-Primaire[/TD]
[TD]Max of 60 lettres - This could belooked up in antoher table[/TD]
[/TR]
[TR]
[TD]Poste secrétaire-Primaire[/TD]
[TD]Max of 60 lettres - This could belooked up in antoher table[/TD]
[/TR]
[TR]
[TD]Adresse1-Résidence[/TD]
[TD]Max 60 lettres[/TD]
[/TR]
[TR]
[TD]Adresse2-Résidence[/TD]
[TD]Max 60 lettres[/TD]
[/TR]
[TR]
[TD]Téléphone-Résidence[/TD]
[TD]3 digits "space" 3 digits "-" 4 digits[/TD]
[/TR]
[TR]
[TD]Cellulaire[/TD]
[TD]3 digits "space" 3 digits "-" 4 digits[/TD]
[/TR]
[TR]
[TD]Téléavertisseur[/TD]
[TD]3 digits "space" 3 digits "-" 4 digits[/TD]
[/TR]
[TR]
[TD]Courriel1[/TD]
[TD]Memo field[/TD]
[/TR]
[TR]
[TD]Courriel2[/TD]
[TD]Memo field[/TD]
[/TR]
[TR]
[TD]Spécialité MSSS[/TD]
[TD]This could be from a list[/TD]
[/TR]
[TR]
[TD]Date de naissance[/TD]
[TD]YYYY-MM-AA[/TD]
[/TR]
[TR]
[TD]Date d'entrée[/TD]
[TD]YYYY-MM-AA[/TD]
[/TR]
[TR]
[TD]Date de nomination[/TD]
[TD]YYYY-MM-AA[/TD]
[/TR]
[TR]
[TD]Date de renouvellement[/TD]
[TD]YYYY-MM-AA[/TD]
[/TR]
[TR]
[TD]Date de démission /
Non-renouvellement[/TD]
[TD]YYYY-MM-AA[/TD]
[/TR]
[TR]
[TD]Endroit du PEM[/TD]
[TD]Max of 60 lettres - This could be coded and then the info would be looked up in antoher table[/TD]
[/TR]
[TR]
[TD]Commentaires arrivée, départ, congé[/TD]
[TD]Memo field[/TD]
[/TR]
[TR]
[TD]Cotisations payées par[/TD]
[TD]Memo field - We need a new of this every year as dues are paid[/TD]
[/TR]
[TR]
[TD]MONTANT[/TD]
[TD]$ - We need a new of this every year as dues are paid[/TD]
[/TR]
[TR]
[TD]Payé[/TD]
[TD]Yes/No - We need a new of this every year as dues are paid[/TD]
[/TR]
[TR]
[TD]Titre chef Dpt[/TD]
[TD]Dr or Dre or M. or Mme - If the member is part of more than 1 department, there sould be more than 1 of these - could be looked up in another table[/TD]
[/TR]
[TR]
[TD]Nom chef Dpt[/TD]
[TD]Max 25 letters - If the member is part of more than 1 department, there sould be more than 1 of these - could be looked up in another table[/TD]
[/TR]
[TR]
[TD]Prénom chef Dpt[/TD]
[TD]Max 25 letters - If the member is part of more than 1 department, there sould be more than 1 of these - could be looked up in another table[/TD]
[/TR]
[TR]
[TD]Titre chef service[/TD]
[TD]Dr or Dre or M. or Mme - If the member is part of more than 1 service there sould be more than 1 of these - could be looked up in another table[/TD]
[/TR]
[TR]
[TD]Nom chef service[/TD]
[TD]Max 25 letters - If the member is part of more than 1 service there sould be more than 1 of these - could be looked up in another table[/TD]
[/TR]
[TR]
[TD]Prénom chef service[/TD]
[TD]Max 25 letters - If the member is part of more than 1 service there sould be more than 1 of these - could be looked up in another table[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

What you are talking about is a database, and by nature, Microsoft Access would probably be a batter tool, as Access is a database tool, and Excel is not.
I have seen people create databases in Excel, but they tend to be a bit clunky, as that is not what Excel is designed for.

They key to having a good working database that is easy to use is good design. So you need to make sure that whoever creates it is an experienced Access programmer, who understands database concepts like the "Rules of Normalization".

 

[ChantalB]

What you are talking about is a database, and by nature, Microsoft Access would probably be a batter tool, as Access is a database tool, and Excel is not.
I have seen people create databases in Excel, but they tend to be a bit clunky, as that is not what Excel is designed for.

They key to having a good working database that is easy to use is good design. So you need to make sure that whoever creates it is an experienced Access programmer, who understands database concepts like the "Rules of Normalization".

Thank you Joe, that's king of what I had figured out. The only problem is that I would be the one creating the database and MS Access is not supported but our tech guys. I learned a lot on MS Access way back when I was in College, but haven't had the chance to work much with it since then. Just to give you an idea, it goes back to MS Office 1997.

Where there be a web page, if not here, where I could get help on how to set it up? I know how to create tables, queries, reports, but I might have problems with relationships and that sort of stuff.

I actually, worked with databases a few years ago, but I started from something that was already there. I created user menus and user forms with codes behind. It extracts data from the other database in order to keep the smaller one uptodate. I remember I did ask a lot of questions on forums similar to one in order to get things done properly.

One thing for sure, When it is properly done, it is easy to work with it.

Thanks for your input.

ChantalB

 

[Joe4]

Equally important to knowing Access is understanding Relational Database Theory and the Rules of Normalization. In my first database project, I knew some Access (even a little VBA back then), but did not understand those other concepts. After 6 months of spinning my wheels and pulling my hair out, someone introduced me to those concepts. I realized that I need to scrap just about everything I had done, and start over. It was humbling, but once I did that, I was able to design a database which works so well that it is still being used 15 years later.

There are lots of good articles and books out there on those subjects. Here is one that will help you get a good start:
Access Database Design and Normalization | The Relational Design Theory | InformIT

The key is, if you design your tables correctly and according to those rules, you make it very easy to work with the data, and you can do just about anything. However, if you design them poorly, you will find it very hard to do many things (some which seem like they should be simple tasks).

Just be prepared, it will be a big undertaking and take a while to do it right.

 

[Micron]

Good advice.
FWIW, here's some of my favourite links on design principles. Suggest you read all of them, since you may find that MS will tell you how to use multi value fields, yet most (many, at least) Access developers will not use them. I don't seem to have a good link for that.
Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
Roger's Access Blog: What Is Normalization, Part I
and/or
Database Normalization Full Tutorial

Entity-Relationship Diagramming: Part I, II, III and IV
Roger's Access Blog: Entity-Relationship Diagramming: Part I

How do I Create an Application in Microsoft Access?
Roger's Access Blog: How do I Create an Application in Microsoft Access?

Important for success:
One source about how to name things - General: Commonly used naming conventions
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