Results 1 to 8 of 8
  1. #1
    wsammy is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    5

    Is Access the right tool?

    Hi

    I have a specific project in mind I would like to go after. I thought it might be the best to first ask you if Access is the right tool to realize the following. And also, being a newbie, what are the specific things I need to go after / learn?

    Thanks a lot for your help!

    Daily Statement "Sheet"
    What: Different users shall be able to use a very simple tool to fill out their daily statement.
    Important: It needs to be "dummy-secure"

    Today it looks like the following:
    1. Employee writes a daily statement by hand (2 types of revenues (writes downs: bills, coins, foreign currency, credit cards), expenses
    2. accountant takes the statements and posts it individually

    Vision:
    - Use a tool to put in step by step what it asks you to put it (important: it should be a step-by-step form
    1. Name of Event? (type in value, press: next)
    2. Date of the Event? (type in value, press: next)
    3. Hours worked for the Event (Emplyee A, Employee B, ...) (...)
    4. Kilometers total (or beginning and end)

    5. How many $ bills for categoryA did you count?
    6. How many $ coins for categeroyA did you count?
    7. How many $ bills for catB did you count?
    8. How many $ coins for catB did you count?
    9. Show total revenue in cash



    10. Revenue by credit cards?
    11. Revenue by foreign currency?

    12. Show total Revenue

    13. Confirmed by: (type in name)
    14. Show a unique number so that the physical receipts can be manually tagged with it.

    DONE

    All these data shall be gathered in a database and the accounting could "collectively post" it.

    Thanks a lot!
    Sammy

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Access is a good tool for this, but really all you need is a database system. You need to research different database systems and decide which one is the best for you. Access has a steep learning curve, although any database system you use will require that you have an understanding of database table design.

    See here post # 7 for some links to start you off.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,824
    "dummy-secure" is an impossible goal - there is always a better dummy - although might get close, will most certainly someday encounter an unforeseen situation. Keep in mind the more 'user friendly', the more code.

    Your 'step-by-step' form could involve code that sets visibility of controls. Regardless of approach, I expect your 'vision' will be complicated.

    First step is to identify data entities and define relationships, then build tables, then develop user interface.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,852
    Agreed that Access is more than up to the job. A lot of normalization links there, but I suggest you first read up on how to begin understanding the requirements regardless of whether you build this or contract it out. You can know a lot about what your app has to have without knowing a thing about how to do it.

    Maybe read this and research similar posts and write a treatise on what the primary concepts are before you begin. I know there are were books on how to approach such a project, but can't recall their titles from long ago. One of their major concepts was that conferring with the db sponsor/process manager or a team tasked with the development to produce an outline of the project. The main aspects are decided first, then sub aspects. Drive down into the details while recording (and parking) absolute and indefinite bells and whistles that are suggested as you go. The reason being, if you have a time line and begin without a clear deliverable, more nice-to-haves will arise, the project suffers from scope creep and pushes your deadline out. You run the risk of appearing less than competent at fulfilling the task. As has been said in this forum many times, pencil and paper will be a big help. In fact, that's where you should flesh out the requirements of each process. Large sheets of flip chart paper (one for each function) enables you to think about what information you want to go in, what you want out, and how to control both. As the link suggests, you examine the inputs and outputs of the current system, whatever that is, and identify the shortcomings.

    After or during this exercise is when I'd begin studying db concepts. As June rightly pointed out, there is a fairly steep learning curve that will only be exacerbated by increasing the level of complexity (dummy "proofing"), and the nature of Access is that it will never be as secure as the worst designed executable program. It wasn't really designed to be a software development tool. Since you seem to be involved in financial work, there may be an increased need to learn about data integrity, data types (for decimal precision) and security (forget anything you research about security that involves .mdw as it has been deprecated). You must learn about how to properly split a db and secure it to a level that satisfies your requirements. Your development time will be in the order of months (3 - 5?) depending on how much time you get to devote to this in the normal course of your work, how fast you learn and how complex you make it. It can be more work raising the level of complexity later than doing it right the first time.

    You should also learn about naming conventions, calculated fields, lookup fields, multi-value fields, reserved names, etc. (most/many of us here would NOT use mvf's). Here's some links for that stuff when you have your design needs figured out.
    One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
    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
    a LOT of info there if you examine the left nav bar at this site.

    About calculated table fields - http://allenbrowne.com/casu-14.html

    In the db creation process, it's my belief that you design queries before forms or reports and you base them on queries instead of tables as much as practical. I see little point in creating a form if the query you want to base it on either does not provide the correct data or needs to be updatable but is not due to design constraints. You'd probably find the Access wizards handy for forms and whatnot, but beware of how controls are named. I will use a wizard, but will always rename all the controls according to my convention lest I introduce ambiguity. Which reminds me. NameAutoCorrect is a db option that most of us would turn off. Then there's always requiring variable declaration if you're writing code, which BTW is much better than macros but maybe a bit longer to learn. Oh yeah, in a financial situation, it's probably more important than usual that you learn about why calculations should not be stored - about 99% of the time. Be thankful that there's tons of info on the Net!!
    Last edited by Micron; 11-13-2017 at 06:03 PM. Reason: added info
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  5. #5
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,979
    Access is two tools, a back end to store data (the actual database) and a front end for forms and reports to view/analyse/report/manipulate data. You do not need to use the access back end as the repository for the data, you can use others instead such as MySQL and SQL Server. Access may well serve your purpose, but you must also take into account the environment of the users.

    For example - Do you have a network? are all users on that network? Do they all have their own pc? Do they all use Windows? If the answer is yes to all of those, then Access may well be the tool you need. But if users are using apple/android machines then probably not, because access will not run in those environments (tho apple machines can run what is called a virtual machine to run windows which in turn will run access). If your users are working in disconnected offices/from home/in the field then the access front end is fine, but for the back end you will need to use something like sql azure or you will need to install something like terminal server or citrix on your network.

    More importantly in these wireless times, the access database (back end) does not work well in a wireless environment. It should only be used with cabled networks. Using wireless is likely to corrupt the data. To get round this you use sql server or the free sql server express which are better suited to working in an environment where small disconnects can occur

  6. #6
    wsammy is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    5
    Hey

    Thank you all so much! As you can see, I was pretty busy the past weeks but I've already tried to figure it all out with Access and I am almost there I still cannot say I "grasp" Access' logic, but it seems to be pretty easy and sometimes it seems to just overcomplicate things. (It's probably me, that overcomplicates things )

    Anyway, I was wondering if you could help me with a little problem, at least I thought and think it must be simple, but it has cost me hours so far because I thought I'll be able to figure it out somehow...

    I made the database pretty simple:
    - One Table with all the data needed
    - One Form - where people will put in data (additionally a button: print and "done" (doubleclick) so that a new empty form reloads.)
    - One Report (opens onClick "print")

    My little problem is: I only want the actual data to be printed and that's my code:

    Private Sub cmdPrint_Click()
    On Error GoTo Err_cmdPrint_Click
    DoCmd.OpenReport "MarketReport", , , "MarketID =" & Me!MarketID
    Exit_cmdPrint_Click:
    Exit Sub
    Err_cmdPrint_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint_Click
    End Sub

    It seems to work:
    - It does print 1 page with all the fields, but they are empty!
    - the weirdest thing ever... it did work 1-2 times with the correct data shown, but even though I havent changed ANYTHING, it doesn't work anymore, the fields are empty.

    - I do have Autonumber (Fieldname = MarketID)

    Thanks a lot and I hope it's ok for just that one question to be asked in this same thread. If not, please let me know.
    Sammy

  7. #7
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,979
    since this is a separate topic, it should be in its own thread.

    I'm very suspicious of a database with only one table - may be correct, but somehow I don't think so. Would need to know what your db is intended to do to comment further.

    With regards your question, if id did work and now it doesn't then even though you say you haven't changed anything, something, somewhere has changed. Perhaps you have deleted data, perhaps the marketID you are looking for doesn't exist.

  8. #8
    wsammy is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    5
    Thank you guys... I think I will implement my little Access "database"? very soon If somebody ready my last question about the empty fields, here is the answer:

    "Are you trying to print newly created records? If so, commit the data first:

    If Me.Dirty Then Me.Dirty = False"

    ->

    Private Sub cmdPrint_Click()
    On Error GoTo Err_cmdPrint_Click
    If Me.Dirty Then Me.Dirty = False
    DoCmd.OpenReport "MarketReport", , , "MarketID =" & Me!MarketID
    Exit_cmdPrint_Click:
    Exit Sub
    Err_cmdPrint_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrint_Click
    End Sub

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

Similar Threads

  1. Replies: 3
    Last Post: 05-09-2017, 08:58 AM
  2. Is Access the right tool for this?
    By tmacg7 in forum Access
    Replies: 3
    Last Post: 03-08-2016, 06:20 PM
  3. Access Tool
    By cbende2 in forum Access
    Replies: 10
    Last Post: 06-15-2015, 02:55 PM
  4. Is Access the right tool for me?
    By buienxg in forum Access
    Replies: 1
    Last Post: 11-26-2011, 02:32 AM
  5. the search tool in access
    By Grooz13 in forum Access
    Replies: 1
    Last Post: 08-31-2010, 09:29 AM

Posting Permissions

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