UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Creating An In-out Database, Access 2013    
 
   
MrWrap2
post Dec 2 2019, 04:13 PM
Post#1



Posts: 121
Joined: 10-January 17



Good afternoon UtterAccess'ers!

Does anyone have good tutorial to build an in/out database?

I work in a controlled facility. I have to control access. I need to know who is in my building at any given time (*1), who has signed out controlled keys (*2), and if they have received a safety brief within the last 12 months (*3).

*1- Using an in/out badging system, I need to know who is in my facility (one of my 14 employees or a visitor), the date/time they entered, the date/time they departed, a status (in or out), and potentially the duration of their visit.

*2- I have 14 employees and 25 keys managed by a key manager. The key manager is required to maintain a log that records who has been issued the key(s), the key serial number(s), and the date and time the key(s) were issued and subsequently turned in. (In the event there is an inventory discrepancy, I will have a log of who had access during a specific time frame). Seems like an easy solution for an excel sheet, but I want to manage some permissions rules as well: Some employees are authorized to receive keys from the manager while others are restricted from receiving keys from the manager. Additionally, I want to make this DB as fool-proof as possible to the user.... limiting a key being signed out while it is already issued out, as an example.

*3- Employees and visitors are required to receive a Safety Brief prior to being granted permission to the facility. Ultimately as an employee or visitor is badged in, I'd like for a display message to pop up if the individual has not received the brief in the last 12 months. If I click a button on the pop-up, it will record the individuals name and the today's date on a table (tblBrief), thus creating a register of who has received the brief within the last 12 months.

I'm essentially starting from scratch with little to no VBA experience.

I've created three tables; tblKeys which contain the building number and the key serial number; and tblPersonnel which contains the individuals title, Name, section in which they work, and a yes/no for authorization to receive keys; and tblBrief which contains Date and Name

I'm assuming this DB conversation to fragment as I attempt to build it, but am open to any suggestions or ideas. Although templates may be the easiest initial solution, my network prevents me from accessing or downloading templates... crazy.gif

Any insight is greatly appreciated. notworthy.gif

-Mike
Go to the top of the page
 
WildBird
post Dec 2 2019, 04:59 PM
Post#2


UtterAccess VIP
Posts: 3,676
Joined: 19-August 03
From: Auckland, Little Australia


If you are working in a controlled environment, and responsible for safety (I have worked in such environments, and they need to know where everyone is, who is still left etc, when an incident occurs), and you have little experience, starting from scratch, and no VBA or programming experience, then I would suggest to get a OTS (Off The Shelf) system. There are systems that would already be built, and have support, and everything. This will generally work out cheaper than trying to build one yourself. While it is fine to want to learn, if this is for peoples safety and compliance, then I would simply purchase one.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
theDBguy
post Dec 2 2019, 05:14 PM
Post#3


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike,

QUOTE (MrWrap2)
Although templates may be the easiest initial solution, my network prevents me from accessing or downloading templates... crazy.gif

Does that mean you can't download from this site as well? Like if we gave you a sample file to get started, will you be able to use it?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MrWrap2
post Dec 2 2019, 05:21 PM
Post#4



Posts: 121
Joined: 10-January 17



WildBird,

Thanks for the reply. Although an OTS system might work, this is something we are doing using logbooks and manual entries at the moment. I've got an interim system in place now using excel which kind of works, but cant keep my guys from inadvertently modifying formulas (even after locking cells, etc.) Excel unfortunately isn't the correct application. Anything is leaps and bounds beyond current practices in place.

-Mike
Go to the top of the page
 
MrWrap2
post Dec 2 2019, 05:25 PM
Post#5



Posts: 121
Joined: 10-January 17



Thanks for the reply @theDBguy,

QUOTE
Does that mean you can't download from this site as well? Like if we gave you a sample file to get started, will you be able to use it?


Immediately after posting this, I went back and discovered that I can, in fact, download templates, however the feature from MS Access when creating a new DB is greyed out. I was able to download a few from office.com such as Asset Tracker and Time Card, but neither seem to fit the bill.
Go to the top of the page
 
WildBird
post Dec 2 2019, 05:43 PM
Post#6


UtterAccess VIP
Posts: 3,676
Joined: 19-August 03
From: Auckland, Little Australia


Hi Mike,

Think of an analogy like this. Lets assume you know nothing about boat building (you might know loads, I dont know!). You have to build a boat, from scratch. There are loads of materials you can choose, and lets face it, people have been navigating waters around the world in boats made from carved out logs basically. How hard can it be? But now you need to take passengers on the boat, and have a bit of responsibility. What safety systems do you need? What about navigational aids? What communications do you need? What about legislation for different locations? Instead of trying to work out from scratch how to build a boat, and see if it floats, and then slowly learn bits and pieces and add them to it, you can simply buy a boat, has all the safety gear built in, along with GPS, radar, charts, plotters, radios etc. All you need to do is be shown how to use it. Lot cheaper, lots faster.

Again, might go against what this site offers, which is advice and often great code examples etc, but my opinion is that it will save you, and your company, time and money, if you simply look at a commercial system. False economy thinking you can build it yourself in a quick timeframe. Like an accounting system - similar principle. Money goes in, money goes out, and your system needs to know how much is left etc. You could use a log book, and then an Excel file, but reality is, an off the shelf system will be so much easier and cheaper to implement. Knowledge of what you want the system to be able to do is a great asset when looking for a system.






--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
theDBguy
post Dec 2 2019, 05:45 PM
Post#7


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi. Templates and demos are tend to be general in nature rather than specific. I doubt you will find one that exactly fits what you're looking for. You may have to put pieces from different templates together into one, to get every functionality you're looking for. Even the demo files we have in the Code Archive, even if you find one that tackles your subject, it might not have everything you're looking for. But hopefully, you will at least find something you can start with and expand upon. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Dec 2 2019, 05:55 PM
Post#8



Posts: 6,182
Joined: 11-November 10
From: SoCal, USA


i agree with WildBird, commercial will probably be a quicker and much cheaper solution, in the long run. this seems to me like one of those business processes that sounds relatively simple on the face of it - but when you start digging into it, you may find you've opened a can of worms.

in your first post, #s 2 and 3 sound do-able, at least for employees; i can envision problems when it comes to visitors, re #3. and i see problems implementing #1 with 100% accuracy. unless your facility is prison-style - entry and egress overseen at all times by a person who can enforce the badging in/out rule - you're going to have to deal with people who enter/exit the building without flashing their badge, just by walking in or out when another employee's badge unlocks an exterior door. from a database standpoint, this creates an issue when it comes to matching up in/out records, or completing table records appropriately if the design puts a single in/out "session" in one record.

the company i work for implemented a clock in/out using our security badges, for the purposes of paying the hourly-paid employees. it took our IT dept months to get a reasonably reliable system in place, and it doesn't deal with secure access to the building, just strictly timekeeping.

if you want to move forward, go for it - but just be forewarned that this is not going to be a trivial project, even for an experienced developer.

hth
tina
This post has been edited by tina t: Dec 2 2019, 05:58 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Dec 2 2019, 05:59 PM
Post#9


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike. Just as an example, take a look at the demo posted in this thread. Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
WildBird
post Dec 2 2019, 06:13 PM
Post#10


UtterAccess VIP
Posts: 3,676
Joined: 19-August 03
From: Auckland, Little Australia


I have worked in companies with both security clocking in and out, and also timekeeping. Not simple systems.

At one place, we would need to swipe in and out. Randomly, the doors wouldn't open, it would make a sound, and you had 30 minutes to report to the security building. Here you would undergo drug and alcohol testing. I mentioned to a few in my team that I could get into the database and take your name out - I said this very jokingly by the way, but next day I had a number of emails from people I didnt know offering me $1,000s of dollars to get them out of the system. The very next day, I myself got called in for testing. Completely ruined my business plan!

Anyway, just saying that security systems and timekeeping systems are quite complex and business models and processes and even building layouts etc need to be considered. I used to work in a place where the secretary was a real pain and hated me and another guy for some reason. She used to write down what times I walked out of the building and keep track of it. I simply would walk out the door in front of her, so she would see, and would walk back in the door out the back, where she couldn't see me, and would have no idea how long I had been there. So unless you have consistency across where people enter and leave, then there are other issues you need to look at.

As for templates you were talking about, I think you are talking about Office Templates? I never use these, but if you can download from this site, then you are good. People can help with things and can upload something you can then use if you can download. Office Templates is another thing altogether.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
tina t
post Dec 2 2019, 10:01 PM
Post#11



Posts: 6,182
Joined: 11-November 10
From: SoCal, USA


QUOTE
I had a number of emails from people I didnt know offering me $1,000s of dollars to get them out of the system

wow, i'm obviously working at the wrong company, lol! ;) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
WildBird
post Dec 2 2019, 10:13 PM
Post#12


UtterAccess VIP
Posts: 3,676
Joined: 19-August 03
From: Auckland, Little Australia


QUOTE
wow, i'm obviously working at the wrong company, lol! wink.gif tina


This was a mining company and most people were on way more than they would normally make. Double or more what they normally would expect to make in whatever they were doing before, quite often. So they were very keen to stay on the gravy train, and if they were picked up randomly and failed a drug or alcohol test, could be dismissed on the spot. A few $grand was nothing in the scheme of things :-) The first time I got called in, the day after I got offered all this money, I went over slightly. 0.02. Breath tested, and then made to pee into a cup, with open wall toilet - wall came up to head high or so, so you were semi private. They would take temperature of the cup. I was given an hour or half to resit it (couldn't leave the room). Had free unlimited water, so I drank a heap of that, redid it, and was 0.00. The very next day, the rules changed to 0.00 first reading, meaning I would have been fired immediately. To put it in perspective, I could drive at 0.04 no problems, legally anyway. I worked as a programmer, no machinery, but still had to be 0.00.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
gemmathehusky
post Dec 3 2019, 07:56 AM
Post#13


UtterAccess VIP
Posts: 4,750
Joined: 5-June 07
From: UK


Logging Systems are relatviely simple.


The main thing is you don't want the data to look like a spreadsheet

BAD
CODE
EmployeeNumber  TimeIn  TimeOut  TimeIn  TimeOut   TimeIn  TimeOut  etc


You just want a series of time stamps.
GOOD
CODE
EmployeeNumber  Action(In/Out)   Time


You can't allow 2 actions of the same type to occur sequentially.

It's easy now to find the current employees on site. These are employees whose latest action is LogIn.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
MrWrap2
post Dec 3 2019, 08:59 AM
Post#14



Posts: 121
Joined: 10-January 17



Thanks everyone thus far,

thDBguy,
QUOTE
Hi Mike. Just as an example, take a look at the demo posted in this thread. Hope it helps...

This helps a lot! It is a great start, but opens a few more questions to make it fit my needs. What is the best way to work through questions pertaining to modifying the linked DB?

WildBird,
I honestly appreciate the info and analogy; I get it, but this is a DB not necessarily intended for safety, but rather accountability. At the end of the day before going home, we must account for all keys and badges and have a record of who (based upon a restrictive list of authorized personnel) was assigned what specific key(s) during a specific time period.

Tina,
QUOTE
unless your facility is prison-style - entry and egress overseen at all times by a person who can enforce the badging in/out rule

This is precisely how the facility works; we have one entry/egress point with an individual who scans an ID card, then presses a button to remotely open the door and allow them to enter, reverse process for exit. Although I don't intend to use this to track payroll, I would like to be able to capture

gemmathehusky,
Great examples of both good and bad ways to collect the data! I suppose I hadn't considered using an Action (in/out) field before.

Again, I'm very appreciative of the support I've found through UA! Great community to learn from! I want to continue learning!

-Mike
Go to the top of the page
 
theDBguy
post Dec 3 2019, 10:49 AM
Post#15


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike,

QUOTE (MrWrap2)
What is the best way to work through questions pertaining to modifying the linked DB?

My suggestion would be to start a new thread for each question and make sure to attach a copy of the latest updated version of the db as you get each problem fixed. Good luck!

PS. Just to clarify, I don't mean to start a bunch of new threads for all your questions. What I was suggesting is to tackle each problem one step at a time. Only move on to the next problem after the previous one is fixed. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MrWrap2
post Dec 3 2019, 11:22 AM
Post#16



Posts: 121
Joined: 10-January 17



theDBguy,

Thanks! I reached out to a few colleagues and one just so happened to have a DB already built for his organization that meets the requirements for maintaining a log of who entered, exited, etc. There are a few bugs to work out (like not being able to select the contents of a combobox), but overall really quite good.

Is it worth uploading the DB and asking for help in building the feature to sign in/out keys in another thread?

-Mike
Go to the top of the page
 
theDBguy
post Dec 3 2019, 11:28 AM
Post#17


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (MrWrap2)
Is it worth uploading the DB and asking for help in building the feature to sign in/out keys in another thread?

I would think so. At the very least, it couldn't hurt. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 10:05 AM