Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Access Db

Options
  • 03-07-2014 2:14pm
    #1
    Clauric
    Registered Users Posts: 610 ✭✭✭


    Going to post this here, but mods, please move if necessary/appropriate.

    My team leader has asked me to look into creating a way to report the activity of individuals within a company. From what I have figured out, based on requirements, and outcomes, I think an Access DB with the ability to pull/push to Excel is what I am looking for. However, as I have had very limited use in Access (not really used in any of my roles), and never created a DB before, I am unsure if it is possible to do what is required.

    The requirements are that the DB contains a list of names of individuals within the organisation, as well as their email address, and the list of clients. (As an example, a list of nurses who work for the organisation as well as the clients who we send them out to). The DB has to email them on a daily basis, with a request for their ID (each is individual), as well as the clients they have visited/worked with on the day and the hours they spent with the client (e.g. nurse A spent 3 hours with Client 1 and 5 hours with Client 3). The individual would reply to the email (all using Outlook), and enter the details of their visits (client ID and time spent), and send it back to a specific address, which would automatically enter the details into the DB, or generate an error report.


    My questions are:
    1. Is this set up possible?
    2. Is it possible to get Access DB to generate an automatic email on a daily basis?
    3. Will the system allow for updating on a daily basis (e.g. it populates each day according to the reply, as opposed to over-writing the existing data)?
    Is it easy enough to do the above, assuming that it is possible, or is it a very large and complicated problem?

    Thanks in advance,

    Clauric


Comments

  • Options
    #2
    FSL
    Registered Users Posts: 1,456 ✭✭✭FSL


    You could store the data using any database server e.g. MySql, MSSQL or Access. What you would need would be an application to send the emails using an email client and read the emails from the email client, extract the relevant data and update the database.

    If the employees enter the data in a fixed format e.g. on a spreadsheet and attach it to the email then you are more likely to get far fewer errors than if the data is entered in 'free format' in the body of the email.


  • Options
    #3
    Clauric
    Registered Users Posts: 610 ✭✭✭Clauric


    FSL wrote: »
    You could store the data using any database server e.g. MySql, MSSQL or Access. What you would need would be an application to send the emails using an email client and read the emails from the email client, extract the relevant data and update the database.

    If the employees enter the data in a fixed format e.g. on a spreadsheet and attach it to the email then you are more likely to get far fewer errors than if the data is entered in 'free format' in the body of the email.

    Thanks for the reply. Getting a spreadsheet attached to an email is impractical. There are currently 60 emails to be sent every day, so that is about 15,600 emails per year. I had been thinking of a template based email, with an ID entry, as well as input places for (3-5 companies) and they're corresponding hours. I guess the short term answer is a spreadsheet, but I was thinking of a larger DB, ideally with no VBA or coding!


  • Options
    #4
    FSL
    Registered Users Posts: 1,456 ✭✭✭FSL


    If you use a template then that should help ensure the data is in the correct order and format. The data still needs to be extracted from the emails and the database updated.


  • Options
    #5
    gctest50
    Registered Users Posts: 9,605 ✭✭✭gctest50


    Clauric wrote: »
    There are currently 60 emails to be sent every day, so that is about 15,600 emails per year. !

    How secure is this email carry on end to end ?


  • Options
    #6
    brianwalshcork
    Registered Users Posts: 607 ✭✭✭brianwalshcork


    I have no doubt that it would be possible to set up as you described, and in an ideal world it might work fine.

    In reality, you'll be paying a full time admin person to clean up the data. The email you send in the morning will be out of date as soon as it's sent. How are you going to handle changes in appointments? Send another email? Guess which one will be replied to.
    The recipients will mangle the reply in ways that you can't even imagine.

    So much so that it would be more efficient to:

    1) abandon the idea and just hire a full time administrative person
    or
    2) get an experienced developer to build you a web app that the nurses can access live to get their appointments / update directly to the db.

    ...and as gctest50 mentioned you'll at least have the basis for a securing the patient data using https.


  • Advertisement
  • Options
    #7
    Clauric
    Registered Users Posts: 610 ✭✭✭Clauric


    To reply to the questions and comments:

    I used the nurses as an example. They are a close approximation of what we are doing, but in a different environment.

    The emails are all secure, as they are sent only to internal recipients. The emails would be sent out once a day, near COB, to all the users, and it would be up to the recipients to populate the fields as required.

    I'm curious has anybody tried to implement a similar system, either in Excel or Access (or other systems), and did it work?


  • Options
    #8
    28064212
    Registered Users Posts: 10,636 ✭✭✭✭28064212


    Relying on emails for responses is a really bad idea. You'd be far better off setting up a website where they can fill in their details, and the email reminder is just a link to that site.

    Boardsie Enhancement Suite - a browser extension to make using Boards on desktop a better experience (includes full-width display, keyboard shortcuts, dark mode, and more). Now available through your browser's extension store.

    Firefox: https://addons.mozilla.org/addon/boardsie-enhancement-suite/

    Chrome/Edge/Opera: https://chromewebstore.google.com/detail/boardsie-enhancement-suit/bbgnmnfagihoohjkofdnofcfmkpdmmce



  • Options
    #9
    gctest50
    Registered Users Posts: 9,605 ✭✭✭gctest50


    Clauric wrote: »
    ....................
    The emails are all secure, as they are sent only to internal recipients. The emails would be sent out once a day, near COB, to all the users, and it would be up to the recipients to populate the fields as required...........

    So,
    everyone gets an email
    fills in some of the fields
    emails it back
    someone reads all the emails and puts the data into something

    https://www.youtube.com/watch?v=kxRX6LXDpWs


  • Options
    #10
    irish_dave_83
    Registered Users Posts: 144 ✭✭irish_dave_83


    Clauric wrote: »
    To reply to the questions and comments:

    I used the nurses as an example. They are a close approximation of what we are doing, but in a different environment.

    The emails are all secure, as they are sent only to internal recipients. The emails would be sent out once a day, near COB, to all the users, and it would be up to the recipients to populate the fields as required.

    I'm curious has anybody tried to implement a similar system, either in Excel or Access (or other systems), and did it work?

    If you havent built a relationship database before, you will need to understand how it works, so to that end it could be tricky for you.

    Alternatively you could try linking MS Infopath to Access, this will all depend on your experience in ICT in order to make this work. Users would complete the form and it would poulate the database for you to query.


  • Options
    #11
    brianwalshcork
    Registered Users Posts: 607 ✭✭✭brianwalshcork


    Op, are the users in the office once per day and then on the road the rest of the time? Or are they working away from the office usually?

    No one is keen on the email idea, as you can tell, and rightly so, it will create more work tan it will save. The users will hate it because someone is eventually going to blame them for submitting dodgy data, then you'll have acceptance issues, some users just won't use it and you'll have fragmented returns.

    You really need some client side validation, so that data entry errors can be picked up on entry, and a structured to be returned to the database.

    Info path sounds like it would be the job, but I don't know anything about it. With 60 users it could work out expensive to license if you don't already have it. 2013 is also the last release, although that still gives you 10 years of support.

    If you're happy enough with the email as a transmission method, (for whatever reason), then you could still use that.... but really if you could give more information about your environment then it would help.

    What are the client devices? Laptops? Tablets? Smart phones?

    Do your users have VPN connectivity back to the office?

    Is your mail server hosted on site or externally?

    How do users get their email externally - Owa? Or through outlook with RpC over https, or IMAP or pop?

    What are the limiting criteria on the client side?
    Do you have ownership of their devices? Do the users have admin rights? Are the devices locked down?

    If you're going to solve this without help of others, what skills / technologies do you have? Or what ones would you like to learn!

    How much time do you have for this task?

    There's probably lots of solutions, some good, some bad. Of all the good ones, only some of them will suit your specific circumstances.


  • Advertisement
Advertisement