Results 1 to 12 of 12

Creating a reporting and investigating database for tracking retail store events

  1. #1
    templeowls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    45

    Creating a reporting and investigating database for tracking retail store events

    I want to check to see if this is feasible before diving in.

    I have retail stores that have incidents occur on a daily basis which they need to be reported by employees (customer complaints, employee errors, injuries, etc.) Right now, the employees report it on an online form, and then staff take these entries and add them to an Access database. It's all very antiquated and subject to error, as the import process is messy. Below is what I am picturing in my head that I'd like to create, but I want to make sure it's even possible before embarking.

    These would all be separate files:
    - Backend database that just stores all the raw data (on the network drive, but in the limited access Managers folder)
    - One simple form that employees use to input in the event (on the network drive so that every employee can access it)


    - Another file that is used to by myself and other key managers to investigate these events, add follow up, and run reports (preferably on each person's local drive, or the limited access network Managers folder)

    The key is all three would need to be linked. I'd like the second file to seperate from the third, as I don't even want employees to accidentally to stumble upon the info in the third file. Stuff there could be directly related to employee behavior or terminations.

    Is this feasible? Is there a better method for doing this?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,323
    Yes, it is, though it may be clunky.

    Without knowing more I think I would try something like:

    1. the 'Incident' database, where staff members enter the basic incident information
    This could be very basic and just have a single local table. What I would do to avoid any long term ability for employees to change old incident information is let this be just a table with no primary key or just a 'local' primary key.
    2. a back end database containing all of your permanent information, periodically (daily?) you could import all the 'incident' database data into your back end and delete it from your 'incident' database giving them a fresh template every day
    3. A front end for each of your 'groups' of users. You just have to make sure you break down the tables such that you are only linking in tables from the back end relevant to the group of users you are building your user interface for. Your 'super users' would be linked to all the tables, your 'managers' version would just link in the basic incident table along with whatever tables are necessary for them to do their portion of the investigations before handing it off to your super users.

  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
    13,828
    I agree generally with rpeare --it definitely is doable.
    Your comment
    I want to check to see if this is feasible before diving in
    is a great approach.
    The link in my signature re: DataBase Planning and Design has many articles which you may find helpful.
    Here's a link to Stump the Model that may help focus your feasibility checking.
    Good luck with your project.

  4. #4
    templeowls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    45
    That sounds pretty ideal. What would be clinky about it though?

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,323
    You just have to be very careful and very thoughtful about how you build your table structure and how you prevent people from doing things you don't want them to do. Access is not a super secure application and If you know your way around you can avoid most security built by a programmer within access. I'd suggest you look at how to:

    1. Hide navigation pane from non super users
    2. Hide the ribbon bar from non super users
    3. Hide any database objects (forms, tables, queries, etc) from your users, just in case they open the navigation pane they won't be able to see the objects directly and screw them up
    4. If your users log in to their work stations, look into harvesting their environ.username to run some of your security, then you won't have to have them log in.

    probably a bunch of other things I'm just not thinking about.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,756
    Wondering what your definitions are for "linked" and "file"...
    If manager folder is where you intend to keep the data, this implies you will split db in to front and back ends. If your intent is to not provide folder permissions to regular "users" then they will not be able to write to those tables. An application lock file (.laccdb) must be created where the be db is located and users must have create, delete and modify/edit permissions in order to do so. That leaves you with having a "replication" type of system, which was deprecated about 10 years ago, so don't even go there.

    A reasonable level of security can be realized in an Access design (enough to thwart novices), short of outright copying of a be file. I imagine Windows could report such activity but I'm not an IT specialist. At that point the data would be already out the door, so a be password ought to be one of the many things you'll want to employ.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  7. #7
    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
    13,828

  8. #8
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,294
    Taking a step back, you say that the end users fill out a online form ?
    Where is the data they enter for this stored? Could you not get to it for the next stage?

    Or is it simply a template that they then email / print off?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    templeowls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    45
    Apologies, I've been away for the past week so I'm just seeing these. So for our online form, it's pretty simple. Its on our internal website and every employee has access to it. They fill out 6-7 different sections (name of submitter, date, category, description, etc) and then submit. Managers then export this information from the website and hand type it into a Form in our Access database. Like I said, sometimes managers type stuff wrong or condense the info unnecessarily, so its problematic.

    If anyone has a better idea for setting it up, by all means, but I'd like to have a Form in Access that all employees have access to and mirrors our online form. Once completed, they submit and it gets dropped into a table that only managers have access to and have their own form that then allows them to view each individual event and add follow up.

    I was thinking that there's a backend database in our network drive that just purely stores all the data and then each manager has a front end database to add follow=up, because currently we have one database on a network drive that stores and does everything and multiple managers use it, and it regularly gets corrupted for reasons I cannot explain except that its not setup in a frontend / backend style.

  10. #10
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,294
    Quote Originally Posted by templeowls View Post
    I cannot explain except that its not setup in a frontend / backend style.
    This is why it gets corrupt.
    Managers then export this information from the website
    This is what I was getting at - if it's in a database somewhere (and this makes it sound like it is) then you should be able to get that data into your database by some means.
    Even if the export is in a fixed format XLS , Fixed Width, Tab delimited etc , it can be imported without the hand typing, and that import can be automated.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,323
    I agree with minty. If you are exporting data from an internal web form collection vehicle, you should be importing that data directly into your database, not having people retype it. Then your 'initial' complaints don't have to be accounted for at all, you already have a method to collect them. You'd only have to worry about your two follow up stages.

  12. #12
    templeowls is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    45
    Yea I assumed that's why it corrupted so easily. Regardless of this whole project, switching to a front / back end setup is a necessity.

    In terms of the form collection online, the results can be exported one at a time via PDF. So basically you click on the submission you want after its submitted, and can export that single submission via PDF. I do not thing there is an excel option to export one / multiple submissions. Maybe if you log into the form master account, you can. But I don't want every manager to be able to do that.

    Even if there was an excel option though, I think it's overly complex. We'd be using an online form that is exported to excel, which is then imported via Access. I've looked into linking up Excel exports and Access up in the past, and it seems like unstable and easily prone to errors, tbh.

    Just seems like having 2-3 Access files is a bit simpler (if it's possible). Plus, we pay for the online form. It's a small monthly fee, but still would be nice to axe.

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

Similar Threads

  1. Creating a database to track sports events and children
    By swellybro in forum Database Design
    Replies: 1
    Last Post: 09-29-2017, 04:04 PM
  2. Strategy/Creating Referrals Tracking Database
    By Jeannine in forum Database Design
    Replies: 1
    Last Post: 02-12-2016, 05:32 PM
  3. Replies: 2
    Last Post: 08-06-2015, 08:13 AM
  4. Help Creating Tracking Database
    By 2uniq in forum Access
    Replies: 7
    Last Post: 01-14-2015, 09:36 PM
  5. Creating a database for student tracking
    By ajh19742000 in forum Database Design
    Replies: 1
    Last Post: 01-15-2014, 12:52 PM

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