+ Reply to Thread
Results 1 to 18 of 18

Moving to Access.

  1. #1
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Moving to Access.

    Hello,

    I have a bit of an odd question and was unsure where to post it, i have decided to do it here. So some quick background i currently work for a small construction company and mostly deal with document designs and graphics. Recently i have been approached by our health and safety manager to provided a better way for him to log reports on his site inspections.

    Currently he fills out an excel document i have modified slightly to include some macros to speed up the process but we believe it might be better to move this whole process over to Access, and i was wondering if anyone could suggest if this is the right move for this document also any ideas on table structure for said document i have attached a blank version of the excel document to this post please let me know if you have any questions.

    Many Thanks
    Tom
    Attached Files Attached Files

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Moving to Access.

    Hi Tom and welcome to the forum.

    My first question would be, what do you hope to gain by moving this to Access?

    Second would be, what is wrong with it being in Excel?

    BSB

  3. #3
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    Hi BSB,

    Sorry for the delayed response and thank you greatly for getting in touch. To answer your questions I would prefer to stay in excel but am currently struggling to make this document work in it. See i need a document where someone can do monthly health and safety reports and save each monthly report as a new sheet, making each project a new work book i then need to feed all the data from all the workbooks in to a central workbook which can present it and highlight any trends. Hopefully this explains a little better what I am after let me know if you need any further information.

    Many Thanks
    Tom

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Moving to Access.

    Is there a particular reason you need all this in separate workbooks and worksheets? Sounds like you're making things complicated for yourself.

    How about a UserForm for recording all the data on each audit (this will make things look neat and professional rather than just entering data into a spreadsheet) and one sheet for recording all of the information in one place.

    You can then plot charts for trending off of this data sheet with no need to have to pull it all together from many workbooks/worksheets.

    You could have a sheet (or indeed use the UserForm) to recall all the data from a selected audit so it's visible in an easily read / printed / emailed format.

    One document is always going to be easier to manage than many.

    Let me know if that sounds like something of interest and we can take it from there.

    BSB

  5. #5
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    Hi BSB,

    That is exactly what i am looking for correct you have hit the nail on the head, all i am after is a simple form which some one can
    input data with and then they can review and compare that data later that's all I am after. as for over complicating it would seem to
    be my thing hahaha.

    Thank you very much for your help so far great to see such active community members helping out us noobs haha, so if you have any
    advice on how i can achieve this or the next step to take or even some tutorials to watch it would be a huge help and i would be
    greatly appreciative.

    Many Thanks
    Tom

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Moving to Access.

    I'll put a simple example together for you and you can develop it further from there. In order to do that I need some idea of the data formats. Things like references can be dealt wit in several ways and the best way will depend on the format. i.e. numbers only / text only / mixed numbers and text. If you could fill the form out with a spoof case and attach it that would be a good start. Doesn't need to be real data but does need to be representative of what you would normally record.

    Also have a think about any fields that could be populated using drop down lists. "Auditor / Advisor" for example could be a drop down of all the possible names for auditors. This sort of validation will help keep the data "clean" and when it comes to producing stats from your data this is very important.

    BSB

  7. #7
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    Thank you for getting back to me this would be brilliant as a starting point I am trying to upload a post but not having any luck, to be honest the
    only real interaction is the sheet labelled "HSE Site Inspection" all the other sheets are just mainly used to display
    the information or to store it in a sort of database.

    The "HSE Site Inspection" Sheet has a bunch of tests layed out on it and are answered with a number of 1-5 which is added at the bottom
    now not all the tests will be used for all the projects so I'm unsure how that will effect it. If you have more questions please just
    let me know.I am very grateful for your help also thank you very much for your time and your skill.

    *I really cant upload this new version the attachment thing is blank and doesn't open if you want to work of the original i can try to explain
    more of what i mean currently the only real sheet that needs to be involved is the "HSE Site Inspection" thank you again.
    Attached Files Attached Files
    Last edited by king_tom_1993; 04-06-2017 at 08:35 AM. Reason: cant upload doc

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Moving to Access.

    Is the Audit Ref # unique?
    What format does it take? Could you provide an example of one?
    How do you currently generate them?

    BSB

  9. #9
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    I don't think the reference number is important to be fair it seems to just be a left over from the previous document they are now
    mainly maintained by project names and dates of the audit. If you would like to keep it in there i guess random would be fine but
    i really cant see anyone needing it hahah.

  10. #10
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Moving to Access.

    The reason I ask about the reference number is if you're going to need to be able to recall previously recorded audit details then you'll need some kind of unique identifier to do this. If you don't need that ability at all then it's not a problem.

    BSB

  11. #11
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    Ahhhh i see what you mean well its probably best that its there then so as you say previous audits can be recalled to compare data.
    what would be the best way to accomplish this? random generation or counting up?

  12. #12
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Moving to Access.

    If they were just incrementally numbered (0001, 0002, 0003 etc.) would you know which one you need to recall by just looking at those numbers?

    BSB

  13. #13
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    Good point erm I'am not to sure what the best answer is to this as up till now its been a case of just saving new docs with the updated
    name i guess a system where the first number is the project number followed by the number of audits. So project 4 and its 6 audit could
    be. 04-06 or something like that im really not to sure tbh.

  14. #14
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Moving to Access.

    Aha, now we're getting somewhere!
    So do each of the projects have a unique number/name to identify them? If so, what format do they take?

    BSB

  15. #15
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    Well we do have Project Numbers but the guy who will be filling this out is very unlikely to have these numbers in his head or on his
    system our project numbers look like this 530393 and im unsure where they come from i think they originate in accounts.
    As only our Health and Safety guy will be using this document i wouldn't say it needs to match these but i guess its a start.

    Each project is normally named by what it is lets say we did some work on the university of Bolton the project would usually be called
    University of Bolton Frank Building or something of that ilk.

    Again i apologise for my noobishness i promise I am actually fairly competent although it doesn't seem it haha

  16. #16
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Moving to Access.

    Hi Tom, I've not forgotten about this project. Just a little slow going as a bit busy right now. I have been working on it, however I'm a little stuck at the "Photos" element of it. Will this contain a hyperlink to a single image file / several image files / a folder containing one or more image files?

    Also could you explain a little behind the hidden columns (L:O) on the "Site Inspection" sheet? What is the purpose of the calculations in those columns? Weighting of results?

    BSB

  17. #17
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    Hi BSB,

    Don't Worry about any delays I am just happy you are helping me out, as for the photo element the overall idea i think is so if there is a reason why the Site has failed a test a photo can be attached to provide evidence now i said link because this would keep the sheet looking tidy but im not 100% on that being the best way to do it. There could be multiple photos yes for one test which may cause issues so what would you suggest manually entering new folders with specific names to then populate for each incident?

    As for (L:O).............I had no clue these where even there but it does possibly solve a riddle i have had about this document so what should happen with scoring is the overall site needs 80% to pass and be "safe" which should be achieved by all 3's but if a site scores a 5 and a 1 some where they shouldn't equal out the site should still fail if you get me, 4's and 5's are extras but all tests should be valued the same. If you dont understand my poor explanation let me know and i can try again if need be tho i can work on fixing the scoring side because right now it seems to me there is a lot of useless information on there which i'm hoping the guy can cut when he is in this week.

  18. #18
    Registered User
    Join Date
    02-09-2016
    Location
    widnes
    MS-Off Ver
    2013
    Posts
    10

    Re: Moving to Access.

    Hi BSB,

    Have not heard back in a while just thought id check all is ok? i tried to private message but they wouldn't send i must be missing something hahah give us a shout if you get this.

    Thanks
    Tom

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Moving my database in excel to access
    By DavidRoger in forum Access Tables & Databases
    Replies: 2
    Last Post: 01-09-2014, 09:30 AM
  2. Moving an application from Access
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2012, 05:14 AM
  3. [SOLVED] [SOLVED] REPOST - Moving Data From Excel into Access
    By Steven M. Britton in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-23-2006, 09:35 PM
  4. Moving Data From Excel into Access
    By Steven M. Britton in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2006, 08:30 PM
  5. [SOLVED] moving an Access form to Excel
    By JT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2005, 12:50 PM
  6. [SOLVED] RE: moving an Access form to Excel
    By TomHinkle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2005, 12:20 PM
  7. [SOLVED] Problem moving data from Access to Excel
    By Jesse Aviles in forum Excel General
    Replies: 5
    Last Post: 08-06-2005, 09:05 PM
  8. [SOLVED] Moving Access Mini App to Excel
    By Sprinks in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-04-2005, 12:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1