Hello,
The hospital I work for used to have a members' database in MsExcel. After a few meetings, a managed to convince my supervisors to switch to MsAccess (We are using the 2003 version for Windows).
I've studies database management like 25 years ago, without ever using it in my day to day life, so I'm a bit rusty.
I've read just about all the posts in this thread, especially https://www.accessforums.net/showthr...abase+creation and read everthing Micron suggested.
Now, I'd like to get some input on my database design and normalization before we start using it. I'm hoping someone here could help me we that as well as answering a few questions.
You will find attached an image of my TablesRelatioinship (RelantionshipEng.PNG), an empty version of my database (TestEng_2017-0807.mdb), as well as the narrative to go with all that below.
My Questions :
- Can I run into any problems if I do not get a newer version of MsAccess?
- Once the database is running, is it possible to use some kind of mailmerge (like in MsWord) to create like 250 letters based on the information contained within the database, create a PDF (Adobe) version of the letters with a password to protect the signature of the letters from being copied, and send everything through an email provider like Outlook?
I thank you all for taking the time to read and answer this post.
Chantal B.
My Narrative (PrimaryKeys are in green; Secondary index are in orange; comments are in blue) :
"tblMembers"
- Each member has a unique #permit. However, the #permit varies in size, for some members (Doctors) it is ##-### while it is ###-### for others (pharmacists). If I make the first digit of the doctor's permit number a non-required character, will this work?
- Other personal data collected from each member include Title (CAN ONLY BE : Doctor, Sir, or Madam), FirstName, LastName, DateBirth, home address (Street, city, province, postal code), Homephone, cellphone, email1, email2, FirstDay, LastDate
- For each member, we collect work data like PermitType (CAN ONLY BE : regular, restrictive or temporary), MSSS_Specialty, PEMSite, PEM#, Extension#, paget#
- To link to tblInsurancea : InsuranceNumber
- To link to tblSites : MainSite# (Site# in the tblSites)
- To link to tblSecretaries : Secretary#
"tblNominations"
- Each nomination has an autonumber : AutoNomination.
- Status (CAN ONLY BE : active, associate, council or honorary), privileges, Permanence (CAN ONBLY BE : Permanent, Temporary, Retired or Dismissed), StartDate, EndDate (These dates represent de start and end date [or renewing date] of the nomination. A member can change status many times throughout his career at the hospital), NominationDate (which is the date CEO assigned the nomination), Comments (to put any notes regarding leaves like maternity, formation, sabbatical...)
- Every 3 years, members have to renew their nomination (thus the EndDate).
- To link to tblMembers : #Permit
- To link to tblSecretaries : Secretary#
"tblSites"
- Each establishment has a unique identifying number assigned by the Ministry : Site#
- Address (Street, city, province, postal code), MainPhone, WebPage
"tblSecretaries"
- Each secretary has a unique number assigned by HR : Secretary# (6 digit number)
- FirstName, LastName, Local, Extension#, Email
- Also each secretary is responsible for many members, but never for Retired or Dismissed members (I believed the best way to relate this was to put her number in the tblMembers).
"tblInsurances"
- Each Insurance has an autonumber : AutoInsurance (Since there are many InsuranceProvinders, the same InsuranceNumber# could be used by different InsuranceProvider, the member could change InsuranceProvider many time, I put an autonumber in that table to just make a big list of all the yearly records for each member.)
- Each member must provide, on a yearly basis, a proof of their insurance.
- InsuranceProvider, Insurance#, StartDate, EndDate, ProofSubmitted
- To link to tblMembers : #permit.
"tblFees"
- Every fee has an autonumber : AutoFee
- Every year, each Active and each Associate member must pay a membership fee (for which I have to issue receipts).
- YearFee (for the year), Amount (Amount text for the receipt), PaidBy and DatePaid. (All of this information could differ from one year to the next and from one member to the next.)
- The amount of the fee is based on the status of the member (presently : active = 300 $ and associate = 200 $, but there could be late fees added to this amount if they are not paid on time. In such case I would typeover the Amount already stated
- To link to tblMembers : #Permit
"tblDepartments"
- Each department has a pre-assigned 2-digit number from 01 to 12 : Department#
- DepartmentName
"tblServices"
- Each service has a pre-assigned 2-digit number from 01 to 15 : Service#
- ServiceName
- Since each service can only to belong to 1 Department à To link to tblDepartment : Department#
“tblMembersDpt”
- Since any member can be part of many departments at the same time and that each department has many members, I created this extra table through normalization
- A member can be, at the same time, chef of a department and chef of a service
- Each record is assigned an autonumber : AutoMemberDpt
- To link to tblMembers : #Permit
- To link to tblDepartments : Department#
“tblMembersServ”
- Since any member can be part of many services at the same time and that each service has many members, I created this extra table through normalization
- However, some members are part of no services since some department don’t have any service. So, some members can be part of 2 departments and 2 services (1 service per department), others be part of 1 department and 2 services, or even 2 departments and 0 service
- A member can be, at the same time, chef of a department and chef of a service
- Each record is assigned an autonumber : AutoMemberServ
- To link to tblMembers : #Permit
- To link to tblServices : Service#
"tblChiefDpt"
- A chief is assigned to each department for a 4-year period that is renewable. These chiefs have to be members of the organisation, so we don't need additional personal information from them.
- A chief can only be a chief of a department where he belongs
- Each record is assigned an autonumber : AutoChiefDpt
- StartDate and EndDate for the chieftaincy
- To link to the tblMembers : #permit
- To link to the tblDepartments : Department#
"tblChiefServ"
- A chief is assigned to each service for a 4-year period that is renewable. These chiefs have to be members of the service, so we don't need additional personal information from them.
- A chief can only be a chief of a service where he belongs
- Each record is assigned an autonumber : AutoChiefServ
- StartDate and EndDate for the chieftaincy
- To link to the tblMembers : #permit
- To link to the tblServices : Service#