I am trying to create an app that records the entry and exit of visitors in a building. At the entrance there is a tablet that scans some id of the visitor and registers the entrance, at the exit the user scans his id again and the app registers the exit.
I have three tables: buildings, visitors, entrances.
The diagram should look like this:
at the entrance:
- from the access form scan the visitor’s id, if the id is already present write the entry time in the access table, if the id is not present ask to fill in the user data.
at the exit:
- from the visitor id scan access form (if the visitor has entered it means that he is already present in the visitors table and that in the access table there is already a record with an entrance exit).
SEARCH in the access table and update the relative record with the exit time (so I can have a virtual column that calculates the time of presence).
Now, with part 1) I have no problems, but it is part 2) that puts me in crisis because I don’t know how to implement the “look in the access table and update the record to write the exit time”.
Can anyone give me some idea or indication?