Results 1 to 10 of 10
  1. #1
    ChantalB is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Location
    Québec, Québec, Canada
    Posts
    4

    Starting a new database - Help needed

    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#
    Attached Thumbnails Attached Thumbnails RelationshipEng.PNG  
    Attached Files Attached Files

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No one can say you didn't do your homework before posting!! A lot of info here; will take some time to process it all. In the meantime, some answers to questions.

    the #permit varies in size,
    Usually, data validation is best done in forms. Assuming the logged in user can have their title or role identifed (probably part of the user profile) it should be easy to enforce the correct format.

    Can I run into any problems if I do not get a newer version of MsAccess?
    IMHO, no. I can't think of anything in a newer version that is worth having and is necessary. You should probably code & design for 64 bit; definitely if that's the environment the db will be used in, otherwise, just in case. The problems would likely be related to IT policy where you work. AFAIK, Access 2003 should run fine in newer versions of Windows. Interestingly enough, it is the last version that allowed custom menubars and toolbars, so you might find that useful. I never learned how to create custom ribbons, and in fact, resisted the change (but resistance was futile).
    is it possible to use some kind of mailmerge
    Sending emails from Access is a common task. Not sure what you mean re: the pdf password. If the password is part of the pdf document, I suspect a full version of Adobe will be needed. If the password is on the Access side, that's different. Access objects (reports, queries, etc.) can be output as pdf's but if you want Access field data to go into a pdf form, that's more complicated. The subject has been solved in this forum before IIRC.

    Note: I thought you were demonstrating a firm grasp on naming conventions untill I saw #'s in your post. Hopefully these don't represent actual object or field names...
    Last edited by Micron; 08-07-2017 at 04:43 PM. Reason: add quote tags
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Normalization and how well your database matches/supports your "business" really depends on your business facts.

    So details of what happens (processes - inputs and outputs) -- like a typical day in your "business" would be helpful in determining your relationships.
    The "#" symbols in your names may cause problems --may force you to use [ ] to enclose these names. I would suggest you use ServiceNo, permitNo etc.

    Here is a link to help you see if the database structure meets your needs. Stump the model post.

    Here is another link that may be helpful re database planning and design.

    You have done a lot of research (congrats). I would use your current relationships as a starting model. Gather some sample data for each of your proposed tables, and create some sample scenarios. Then play stump the model. This can be done with pencil and paper.
    As I often say in the forum, don't be too quick to jump into physical Access. Test and retest your model with sample data to ensure you can get the data you need from processes. And anytime you find an issue/anomaly, research it and determine the cause and adjust accordingly. Then retest again. When it works as per your requirements, you now have a blueprint for your tables and relationships.

    Much like the blueprint to build an airplane, or a shopping center.....

    If your design is sound, I don't foresee issue with new versions of Access.

    Good luck.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Orange touched on the hash (#) being a reserved symbol and shouldn't be used in object names.
    Also shouldn't use special characters or punctuation ("O/S" is an example).

    For the Primary key fields, I use an Autonumber type; the Foreign key field is a Long Integer. The PK/FK fields are never displayed on the form/report.
    In my naming convention of fields, for example in "tblDepartments", I would name the PK field "DeptID_PK" and the FK field in "tblMembersDpt", I would use "DeptID_FK". Much easier to tell the PK/FK fields.

    See
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/Autonumbers


    AutoNumber
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

  5. #5
    ChantalB is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Location
    Québec, Québec, Canada
    Posts
    4
    Hi Micron,

    I'd like to start by thanking you for your reply.

    Then I have to add that my database is in French so all these names are not my actual ones but close to it. I will make sure that my naming doesn't include reserved symbols.

    For the size of the permit number, What I thought would be a problem is that they don't have the same number of characters in them. Most of the members are doctors and they have a 5-digit permit # like 00-123 (where the first two digits represent the year of their graduation). The other members are pharmacists and they have a 6-digit permit # like 157-458 (which represents nothing at all). I supposed I could make the first digit not required so it would leave it blank. Would that work? When I do a search on a part of the permit #, will the doctors and pharmacists both came out?

    Not sure what IMHO and AFAIK mean. I think the 64-bit is for my Windows version, since, with a bit more search, I found out that Ms Access doesn't come in 64-bit version until the 2007 version. So my version is a 32-bit. The IT here don't support Ms Access at all. If we develop using Ms Access, it's at our own risks. Since this database is nor a dependant nor a parent of other databases, I thought the 2003 version should be fine. My supervisor thought it would be better to ask the forum, in case I was wrong. However, I'm not sure what the difference is between a 32-bit and a 64-bit version. How do I determine if we should get more of the same licence (cause my department only has the one I'm using right now) or the same amount of a newer version?

    As for the mailmerge, presently, I write letters in Ms Word, use the Ms Excell database to import the fields' data into the letters. Than I transfert them into a PDF document which I then need to separate each letter into a separate document and add a password protection to it before I create an email for each member and import the a PDF into each email.

    Thanks again for you advice,

    ChantalB.

  6. #6
    ChantalB is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Location
    Québec, Québec, Canada
    Posts
    4
    Thanks for your input Orange.

    A good part of my work is to create lots of letters for different situations. The Input part occurs when I new member starts working at the hospital, when there is a modification to the status or privileges of a member, when new information becomes available for a member, when a member quits, retires or dies. Typical outputs are the different lettres for the different situations that I can come across. Other outputs would be reports based on queries to give us an idea of how many members in a service or department, grouping the members of a specified department by pre-determined age groups, how many members haven't paid their dues...

    For the naming policy, what you are suggesting is actually closer to what I have cause my real database is in French. I tried to shorten the names for here, but mine are long and not abbreviated. I'll just make sure to revise the naming policies and the removed any symbols I may have.

    I'll try to import some real data in it (cause I figure that when its not real its harder to see if it doesn't work). I will sure try the stump the model thing.

    We were hoping to get the Access database going for september while we keep the Excel database up-to-date until december. That would give us, we figured, enough time to test the new database and iron out any problems we come accross.

    I'll keep you posted on the Stump the model thing.

    Thanks again,

    ChantalB.

  7. #7
    ChantalB is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Jul 2017
    Location
    Québec, Québec, Canada
    Posts
    4
    Hi SSanfu, thanks for your reply. I tried to remember all of the naming rules I learned way back then, but I'm sure I hadn't come across the PK and FK part. But I tend to agree with you, it makes things easier to understand.

    As I remember, any of our datafields could be used as a PK as long as we were sure it could never contain a duplicate. That's why I used the permit# as the PK in my tblMembers. Also, we had link tables (not sure if that was the real term for them) which were used to put between to tables with a many to many relationship to make them a many to one relationship. The link table, if I remember right, had a primary key made of 2 fields. Is that still good practice? From what I gather now, I should have an autonumber field in everytable to use as a PK.

    I will take a look at the links you kindly suggested.

    Thanks for your input,

    ChantalB.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IMHO - in my honest opinion
    AFAIK - as far as I know
    IIRC- if I recall correctly

    So IMHO, I think you will be fine with the 32 bit version. If you end up writing code that uses API calls, then you have to consider coding for the possibility that one day your office might convert to 64. If they do, that code part will fail the way things are today. If they never do, no problem. I would code API calls so that IF they do, I'd look more like I knew what I was doing. In that case, research PTR safe and you'll find out how to code for both.
    Re the permit number - looks like my comments were not understood. I assume you know that users are NOT to work directly in tables, thus they will interact with forms. The form should be deciding if the permit# entered is the right length based on the user profile. If the profile that gets loaded into the form (can be hidden) requires 5 digits, then the process validates it is correct.

    Still haven't had an opportunity to read all your notes. The relationship image looks wrong with respect to depts, but without reading the explanations, it's not possible to tell yet.
    I have to get back to what I was doing.
    plus tard!

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    As I remember, any of our datafields could be used as a PK as long as we were sure it could never contain a duplicate. That's why I used the permit# as the PK in my tblMembers.
    Yes, almost any datafield could be used as a PK.... (except memo fields, check boxes, attachment,..., etc.)
    The PK should not have any real world meaning. Yes, I know about the discussion/arguments between natural keys vs surrogate keys, but autonumber/identity field is the better option.

    Is the PermitNum (Permit#) the best choice for a PK field? I would have to answer no. The first thing is that the "PermitNum" has two different formats: one format for Doctors and a different format for pharmacists. Then, it is a text type field.



    Also, we had link tables (not sure if that was the real term for them) which were used to put between to tables with a many to many relationship to make them a many to one relationship. The link table, if I remember right, had a primary key made of 2 fields. Is that still good practice?
    Link tables (or junction tables), IMO, should have an autonumber for the PK field. Set a compound index with two or more fields if you want to prevent duplicate records.


    I should have an autonumber field in everytable to use as a PK.
    I do..... unless it is a table that has limited records, like the days of the week, months of the year, names of the US states.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    lookup tables for permitType; Title; Status; Permanence to restrict users to the values provided.
    This could be done other ways, such as using a combo box with a items list for user selection. Having tables instead means that to modify the selection options only requires you to edit or append the table. The list method would require you to open the form in design and add to the list. For the table method, the combo row source is a query that simply returns all the table options, but its LimitToList property must be set to True. Note that when you bind combo boxes to a table and allow users to select a different value, that new value will become the table value, so it is important to understand how these work lest you end up with data changes you don't want.

    "To link to tblInsurancea : InsuranceNumber"
    I don't understand the relationship between a member and Insurance number. If these are attributes of a member AND there can be only one number, then OK. If a member can have more than one insurance number, then not OK. Same goes for site or secretary associated with that member.

    tblNominations paragraph: if a member status can change many times during a career, is the current status sufficient or do you need a history (status vs the date of that status achievement).
    Will access to any part of the db be restricted based on status? If so, I'd also have a numerical sort order if should you wish to restrict everyone from a report/form/procedure where their status is (for example) permanent or temporary. It is easier to restrict access to something if their status value is less than 3 (arbitrary) than it is to go through a list of status'.

    "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)." Not if a member can have more than one secretary. While this may not be possible now, are you sure this will never happen?

    "the same InsuranceNumber# could be used by different InsuranceProvider, " I would make the number and insurer fields a composite index so that either could appear more than once, but one number cannot appear more than once for a given provider.

    That's all for now. I will resume with tblFees if you still want more scrutiny.
    Last edited by Micron; 08-11-2017 at 06:53 PM. Reason: grammar

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Starting new inventory database
    By Michael.Reynolds1775 in forum Access
    Replies: 1
    Last Post: 04-30-2015, 02:45 AM
  2. Questions about starting first database
    By JM9x in forum Access
    Replies: 15
    Last Post: 09-10-2011, 04:26 AM
  3. Starting a new database
    By JFo in forum Access
    Replies: 9
    Last Post: 08-25-2011, 11:00 PM
  4. Help starting up a daily entry database
    By sparx in forum Database Design
    Replies: 1
    Last Post: 01-12-2011, 10:56 AM
  5. Replies: 7
    Last Post: 05-21-2010, 10:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums