Humanipro application

RDBMS can also handle unstructured data. You just define the column as text, either long or short and put in whatever you want to. So, Excel has no advantage there. Excel does have manipulation advantages when you are trying to duplicate data or clean it up. But once you commit to an RDBMS, you are essentially committing to mostly structured data and the need for clean up in aisle one no longer exists unless you have a constant stream of bad input data and have no way to control the source of the data to fix the problem before it gets to you. You add validation code to your forms and RI to the schema and you keep out bad data going forward. Bulk updates when necessary are done with queries, not by copying and pasting.
True, but to even begin to handle it, "...you just define the column as text, either long or short..." I would argue that the data is immediately structured, at least at a minimum level, by virtue of being part of a table.
 
I'm not arguing with that per se but even Excel has a "data type" although it is fluid. That is my objection to it. Why bother with a data type if you can put any ol' crap in the cell?
 
@MarlonVisser0408 So far, it seems that you are here to complain about how inferior Access is to Excel, to the point where you actually think it is better to use Excel spreadsheets to hold your data than actual tables because spreadsheets support unique functions and data types for every single cell.
Nope, not even close.

Do you honestly think that having 5 million potentially different functions rather than one is some kind of advantage? Yes, when you are manually manipulating data, some things are easier with Excel than with a relational database (notice I don't say "Access") but that is because Excel doesn't care what is in the cell whereas the RDBMS does care and does enforce rules because in an RDBMS, consistency counts for something. I admit to sometimes resorting to Excel to help me to clean up bad data before I convert an old app to Access. But once the data is in Access, then my forms and tables enforce RI and contain validation rules to prevent bad data from ever being saved again.
I was simply asking if there was a benefit to be had there, simply because I do not have the knowledge of how things in the end turn out. I rather save myself hundreds of hours of learning and testing stuff than not to ask some questions. The only reason that I'm even naming excel is because of my minimalistic background, any overlap I have from excel is about all my experience in access - I hope that this time around, you grasp that I don't have the experience to go too far beyond the small references that I can make; it has nothing to do with either Access or Excel actually; it has to do with my experience.

I'm pretty sure that no expert here agrees with either using Excel as your data store or your preference for 5 million functions rather than one. You are on such a bad track that you needed a wakeup call, so I rocked the boat. Sorry, you're right. I was appalled by this approach being proposed by someone who I would have thought would know better given the level of experience you claim. If you actually want to learn how to use Access, we are here to help. Nothing a novice developer does appalls me. They don't know any better.
I'm here for access; don't worry. If I thought that Excel was the solution here, I wouldn't have came down here to waste my time. The very reason that I'm having a hard time getting to start in Access is actually the same reason that Excel doesn't fit for the data that I'm trying to connect and store. It's data from more then a hundred tables and it's driving me insane.

Since you are unfamiliar with Access, it is probably better to state your problem and ask for a solution to the problem rather than asking how to implement what you think should be the solution based on your strong preference for how Excel works.
I think my biggest issue is with the time frame that my state of mind is in and the pressure of me wanting to do something with the product. It's a personal thing that unfortunately, I cannot turn off. This results in me thinking about too much in too little time. Don't get me wrong about wanting to do something with the product, I very much look forward to learning and making it.


Once you actually make the move in your mind from Excel to a RDBMS, you give up the loosey- goosey no rules way Excel handles data for something structured and firm. Right now you seem to be pining for this flexibility. If I define a field as numeric, a RDBMS will simply not allow me to enter "N/A" as a value. If you don't like that, you should probably stick with Excel rather than asking how to make a RDBMS allow "N/A" in a numeric field (just an example).
RDBMS is the only thing really that fits what I'm trying to do. I person can only learn so much at a time - it's not easy to learn something without having reference to things you've done before. For me it feels like learning Chinese by ear sometimes. If anything, I'm proud of the things that I've already managed to do with my own research; not that it's anywhere close to where it's going to be once I'm done :)

I posted before as an ordinary member, having had some negative experiences with using Excel as a direct tabular data source. However, I now have to put on my moderator hat.

Pat Hartman has apologized for her harsh response. None of us intend to be harsh with other members, new OR old, in any of the technical forums. (Non-tech forums? No promises.)
I think we both have more information now to make sure that this doesn't happen in the future.

You do, however, have to realize that we are all volunteers here and (last time I checked) also all humans. We can get frustrated when we see someone going down a costly or questionably-effective path even after telling them of the potential massive pitfalls. It frustrates us because it seems like we have wasted our time or our many years of experience are being ignored. Have you ever been watching a movie and want to yell to the protagonists "Don't open the door..." after which they open it anyway and the bug-eyed monster immediately jumps out?
I was just gathering information and opinions. It was about having the information for me to let go off the subject. Not only did the post before showed me having closed the idea of having to do anything with this; the topic was already put to solved.

I get your point about the movie, but do you get how it feels to me as someone who's dipping his toes to something new - that was not a good experience. Don't let the tool be a bigger problem then the problem; it's like you said yourself - we're all human. That'd include the recipient of the message.


I hope this helps you to understand better why you got the response you got. We also hope that you can find help here, short-term OR long-term, to better facilitate your projects. But finally, please DO understand that it is our way to warn you of those pitfalls in your path even if that really isn't what you wanted to hear. Would you want your doctor to hide a dire diagnosis from you on your next wellness check? Or would you prefer blunt honesty?
I understand where the tone comes from, but I'll never respect it. Kicking and bashing are not considered to be tools in a healthy learning environment. If I wanted to be gaslighted about something stupid, I'd not join a forum to learn access; I would've joined Facebook or X. Blunt honesty looks differently from what's over there Docman, you know this as well as I do; so let's not compare appels and bananas here. I'm not here to cause a problem, but I'm highly allergic to being shit on and false assumptions. Either ask or zip it - seems pretty simple.

As you said, we've got a better understanding now. I'm sure that future conversations will be better in tone.
 
I'm not arguing with that per se but even Excel has a "data type" although it is fluid. That is my objection to it. Why bother with a data type if you can put any ol' crap in the cell?
I was focusing more on the fact that the data has to be part of a column, which is part of a table, plus the fact that it has a defined data type that doesn't change depending on the user's mood.
 
I'm not arguing with that per se but even Excel has a "data type" although it is fluid. That is my objection to it. Why bother with a data type if you can put any ol' crap in the cell?
One does not need knowledge to put something in a excel cell. It's a good way to solve skill issues.
 
One does not need knowledge to put something in a excel cell. It's a good way to solve skill issues.
Skill has nothing to do with anything. Why do you not care if the data entered is valid? Doesn't seem much point in keeping random keystrokes. If a field is labeled DOB, it should contain a date or be null. If you want to allow partial dates, then use three fields. AND fields like dates should be checked for sanity, you wouldn't just accept xyz and let it go at that. Or even 3/14/205. Unless you are the SSA, 205 probably isn't a valid birth year unless we are talking about historical figures.
 
Last edited:
One does not need knowledge to put something in a excel cell. It's a good way to solve skill issues.
You actually solve skill issues by creating a coherent interface that doesn't accept bad data. The ball is 100% in your court. No user needs to know he is using Access rather than Excel if you don't let him get behind your interface.
 
It's data from more then a hundred tables and it's driving me insane.
Start by cleaning up the hundreds of separate sets of data. This will familiarize yourself with the underlying data. This may or may not be easier with Excel and it is invariably a tedious job which hopefully, you can automate to some degree rather than having to do it all manually. Depends on what the inconsistencies are. A database application is all about the data. If you are consolidating data from 100 different users, the first step is consistency. Define the Entities (Customers, Orders, Products, Classes, Teachers Suppliers, etc) Define the columns associated with each Entity. Define the valid value ranges as best you can. If you are combining data sources, the objective is ultimately that you want to compare apples to apples and the only way to do that is to ensure the integrity of the data in any given column.

Why not start by describing the current application? How is the data stored now? Who maintains it? What is the goal of the new application? What kinds of outputs to you ultimately want from the consolidated data? Is everyone working on the same LAN? Because if they are not, Access may be problematic depending on the infrastructure your employer can support. You may actually need a WEB app and can't use Access at all if the users of the system will be anonymous. This website supports anonymous users and so Access, the RAD tool would be totally inappropriate as a development platform although ACE the database that underlies Access could be used to hold the data. I would never recommend that but ACE is a RDBMS and so technically can support a WEB app but there are far better RDBMS' for that task.

When you create an application, YOU are the master of the universe as far as structure goes. The users describe their goals and what they want out of the application and you need to guide them into simplifying the interface so even dummies can successfully enter data correctly. And you need to write code to enforce the business rules the domain experts explain to you. i.e. Customers may be individuals or they may be companies. When they are companies, CompanyName is required but when they are individuals, LastName is required. An easy way to implement this rule is to ask the users to tell you their intention. Are they entering a new company or a new individual? That question allows you to actually show/hide fields to eliminate confusion regarding what data is required. If you just give them all the fields, then you need to create different logic to avoid conflicts. Should you allow both CompanyName AND LastName? Does a company have human contacts? If so, you may want to use the FirstName/LastName to allow a contact name to be entered. The users will provide the business rules that tell you how the data needs to be validated. It takes thousands of similar decisions to create even a simple application.
 
I think I just might open up a seperate topic. There's much for me to learn and when asking questions related to it, it might as well be there - to keep on topic with the post.
 
I think I just might open up a seperate topic. There's much for me to learn and when asking questions related to it, it might as well be there - to keep on topic with the post.

It might help keep the discussions about different issues separate so that they will be less likely to get all tangled with each other. And we don't charge by the thread count.
 
I think I just might open up a seperate topic. There's much for me to learn and when asking questions related to it, it might as well be there - to keep on topic with the post.
3 things what would help us help you is to:
  1. Describe the business or operations you are in and what kinds of things you want to keep track of (for example, we are wholesalers and want to track vendor purchase orders, customer orders and inventory levels or we are a sports club and wish to track memberships and payments etc.)
  2. Take a screenshot of just the Headings and only the first row of your spreadsheet. You can delete any names, addresses or other confidential information (we don't need that). That will give us some sense of what kinds of data needs to be included in your ACCESS application. This will be useful for converting your current data to ACCESS tables.
  3. Tell us how many users are going to use the application. ACCESS can accommodate multiple users, but only under certain conditions.
Before we consider each individual tree, we need to see the whole forest first. Unlike with EXCEL, you will be building a house with the table and relationship structure as a foundation. That comes first. Then you build the walls and roof.

As you must know by now, in ACCESS, you keep each piece of data in only one place and then connect different tables together. It's a whole different way of thinking so, fasten your seatbelt. But if you start by designing your data tables properly from the beginning, you can be sure the rest will be much easier.
 
ACCESS can accommodate multiple users, but only under certain conditions.
When the BE is Jet/ACE, the maximum number of active users is 255. A more practical limit is around 50 but really depends on your LAN. HOWEVER, when the BE is SQL Server, the number of concurrent users is limited to the number of seat licenses you own and not by ANYTHING in Access itself. So, you could have thousands of concurrent users provided your schema is properly normalized and the app is efficient in its use of SQL.
 
[BEFORE YOU READ]: As mentioned before: I don't really have too much experience in coding or Access (yet). I'll be editing a few of the points here as we go through, so that it becomes a better explanation of what I'm trying to do. When I got a decent page done, I'll transfer this to it's own post so that maybe I can apply your generous help a little better.

What I just wrote down below is only a part of the information that I'm solving. However, I think if I handle these points below; I'll be a lot further in understanding in how to explain some of the things to you guys or that I'm already able to do parts myself from the earlier advice.


- Application use: The application is for humanitarian work. We're mostly active as management and coordination on refugee shelters. This is why there's a lot of different sorts of data, but almost no data will become very tall. This is the reason that a good interface or application is needed to both connect and properly edit the data on the fly. I'll give an example of some of the datasets and documentation that we're using to do our daily jobs.


Startscreen & Permissions:
- Even though the real security isn't through the application itself but rather by your access to the application, I like the idea of having a start screen with a standard login function. This will serve on application level as a filter for what you're able to see and do in the application. After the first modal page, if you have more then 1 function to your name; you will have another modal page that will ask you to which part of the application you want to sign in.

This is mainly because someone doing sales for the company shouldn't have any access to logs made at a shelter. At the same time, a coordinator that is working on a shelter, should not have the permission to look at financial or employee information when looking up his colleague in the Contact Relation Manager (CRM). At the same time, I want someone with serious access to be able to see everything that's regarding a entity in the application.

A quick snapshot from a User Record Page to look at how I can use the real-estate and to get a clearer picture of what I need to learn and do.
1742125400116.png

For reference:
- Record options will hold options like; 'resend password' or 'resend username' to the user their Humanipro e-mail.
- Record information will show information that's not really used by anyone other then someone working informatics or me/us. Think about UserID, PersonID.

I don't know what I'm doing sometimes, we have to start somewhere.. I guess somewhere is where you leave blanks for when you got more knowledge and experience to use this real-estate. The header and menu are simply there for referencing the real-estate made available for things like a navigational menu, header and the like. Patience, I can only fail at one thing at a time.


Contact Relation Manager:
- PersonID: This is just your average place of a contactbook. There's a few things that we need to add though; allergies (we sometimes provide food), 'emergency contacts' that I've added for now as a really (bad &) basic genealogy that adds phonenumbers. The visual below will show you more about what I mean.
1742126997289.png


- CompanyID: It's basically the same thing as the PersonID - just for companies. When looking up a company, I want to be able to see who works where and which departments. It'll show the information from the department.

As a quick visual to what I mean. All the information goes to a record where I can edit it all on the fly.
1742126790504.png

- AdressID: The AddressID will hold all the information that come together at an address. There can be multiple stakeholders, organizations and volunteers working at different locations and in different functions. To make sure that we don't have to deal with all that mess, the AddressID will act somewhat like a filter [I hope this makes sense] so that we have a page that will show only the things regarding an address.

- ShelterID: The ShelterID is the only one that I would point out is not a universal ID. That's my inexperienced way of saying that the other ones I can imagine are being used in many datasets, where this one I consider to be a 'custom ID'; somewhere I need to watch out for how I name things. The basic of it is that it's almost the same as AddressID. The difference here is that these are addresses that we work on. They'll hold a lot more information that is dependent on your permissions.
- ResidentID: For looking up residents that have been within our care.
- EmployeeID: The employee record will hold the information that we need to automatically generate the contract and documentation that is needed to work for the company. It'll also hold all the data the finances and HR need to do their work. This will also be the part where a employee can edit some of the details for themselves, like address, certificate numbers and the like.

[*Still need to solve groups.. We often use a group mail or a location phone tied to a function. So I'd like a way to add people to add group and for it to show up on the records with the appropriate list of people having access to that e-mail group. I like to see on forehand who that I'm talking to]

Logbook & activity tracker
There needs to be a general logbook page that you can use to make rapports and filter logs based on things like ResidentID, PersonID, CompanyID and the like. This way, we can make a full rapport about at what point of the process they are. These can be safety rapports about misbehavior to the Government branch that goes about this or it'll hold data for appointments that we made for them like the dentist or doctor. All information should be go down there so that we're able to make it convertible to PDF or able to being send by email.

This will mean that all the different little logbooks are linked with this badboy. This is so that we can use the UserPermissions in combination with who's being logged on to what function to give them an impression on how things look. You can check up on the activities of a complete shelter in 2 clicks to then be able to go to a resident or employee in an efficient manner.

[I haven't looked into the ability to hook up third party applications with this - but having outlook, whatsapp and/or (company)phone records being added automatically would absolutely make my year.]
 

Agenda
The agenda page is filtered in a few core points that we can filter on, because we'll use the agenda in different ways. Points being linked to ID's will also come back at the logbook. If you lookup a company, you can not only see the logs of what you've been doing; but also the things that still need to be done.
- Informational: On the core page, we'll show the informational part of the agenda. It'll show what the upcoming vacations are for the school going children, what the birthdays are and what religious holidays are coming up.
- Company: These are appointments made by the company. These can include company meetings or evaluations for example
- Shelter: All the information that is bound to you by your activities on a shelter. Because of European privacy laws, it's important that I try my absolute best and nothing less to secure this kind of data from the view of others - and it's the polite thing to do. It can include things like planning, tasks and appointments.
- Personal: This will include your own planning, appointments and free days.


Inventory
- Current Inventory: The inventory needs to go on a few different levels. We should be able to see it from a company standpoint, where a car is part of the inventory and as a shelter viewpoint; where I can check what the current inventory is; where did that 20 bread go?
- Inventory tracker: The inventory should be trackable. Where is an item at the moment (in case of company level) or to see where a product went. When we give out items to our residents, we're obliged to write down to who we gave what and when (you're only allowed X amount in the week kind of deal). This should be logged and tracked.
- Tracking amounts: There should be a form that lists the inventory that needs to be rebought. If we go over the minimum threshold of X, then buy up to the amount of X.
- Tracking expiring dates: Dates for both medical items and catering should be noted according to law. We should so in document in how we take care of the laws concerning expiring dates
- Catering inventory: To make things easy, we often help with taking care of produce of the catering or do a self-catering with the residents. This means that we hold stock of all the ingredients, gear and produce of the complete catering (for 50-300 people that come from 20+ nationalities per location). This means that we also need to keep data that shows if items are Haram for example or are using ingredients that someone could be allergic though. We use stickers for those items.

As expected; like almost everything, this needs to go back into the logging system. This way, we can see the things that residents already got or borrowed from us.


School, activities, sports & events
Despite promises and laws, the process of asylum often taken multiple years. For the first half year, one of is not allowed to work; there's no numbers yet that verify that you are you. Often this is even longer, which is only a step of the process that one needs to take. One of the core problems in the asylum is that people are bored out of their mind and have a lot of time to think about reasons they fled, things that are happening in the places they rather be or insecurities of having to go through years of process which by no stretch of the imagination is a guarantee.

There's many different opinions about how to make for a humane shelter location to spend years in and I'm a firm believer that someone needs to be able to participate in activities. sport and play. This is why I need to find a system that will allow me to make it easier to inventories what people want to do and to quickly get the right information and documents that match the activity.

It sounds easy, to do activities. I can tell you that it takes weeks for us to be able to play a normal game of basketball that looks like it's basketball. With people from 12 to 50 from 20 nationalities playing a game of which no-one really knows the rules about is tough. We often get the rules on documents, translate it and distribute it manually before we go and do something. The amount of time that goes into this makes my soul cry, I'm pretty sure that if we got everything done; creating a interface that works with this should be doable.

I don't really have too many concrete examples here, because there's only so much that my brain can hold before it gets fuzzy from trying to understand it all. Let's not forget my experience and that visualizing something that I don't have experience in is hard at the very least.



Official documentation:
- Process documentation & Governmental and Municipality policy:
This are documents for helping and giving information to the residents about their process and who the stakeholders are to get a better understanding of the information. It's policy that we always try to find a professional to give them the right and up-to-date information about what they need to know and what they need to do.

The stakeholders on their turn, often give us back documents or information as a handout to the residents so that we can directly hand people this information. The author of this information needs to be the correct stakeholder and needs to be properly referenced. I'd really like a place to put these handouts and being able to send them to group (ShelterID) or a person (EmployeeID or ResidentID).

- Company policy
I don't think I have to elaborate too much about company policy, but it'll hold all the information regarding company policy. Everything from being sick, to the information about the items you borrow from the company.


Here's a virtual pineapple for making the end of this: 🍍
 
Above here is more informational; there's not really a question yet in there.

I'm now looking at the logging in screen and the user part of all of this. If anything, maybe someone can enlighten me on how I should look at the permissions being done.

I was thinking about having group permissions that automatically come with your function in the company and having a layer over it with user permissions - have more control on what I want people to be able to see and do. This is purely for the permissions within access and has less to do with security in general.

I included this file as a zip

1742131169537.png
 

Attachments

Last edited:
I was thinking about having group permissions that automatically come with your function in the company and having a layer over it with user permissions - have more control on what I want people to be able to see and do. This is purely for the permissions within access and has less to do with security in general.

You gave us a bunch to "chew" on and you can probably expect comments to take a while in coming. I saw the above and want to comment on the specific implications. "Group" is unfortunately one of those words that runs into ambiguity in the Windows environment. The commonplace use of the word "group" - meaning some number of people who will use the application in a similar way - can be implemented by determining a User ID in some way and having a user table that lists the groups (or perhaps you mean roles) that each user would play.

The complication is that Windows has groups, too, and they actually have the same meaning - but very different granularity - as the "role" concept that we often see in Access apps. The difficult part is that there is no built-in Access function to test Windows Group Identifiers. You must use one of the Win32 API calls and the one I looked up is ugly. I have used a single Windows Group Identifier as a way to gain/grant permission to open an Access app, but the user role within Access is best determined totally within Access. You would use the user's Domain ID to identify the user and then look up the user's role in a table. There are several relatively easy ways to more reliably determine a user ID if you are in a domain-based environment, and I might suggest some articles by our member isladogs to be an excellent - though not the only - source on that subject. Our SEARCH facilities allow you to put in a topic AND a username for your search, so that should help narrow down some articles.

I'm responding to a narrow subsection of your post simply because it will take a while to digest the whole thing and this is something I could offer quickly. If you really mean from that quote that you want to discuss a user's role in your company as a guide to how they would use the app, the keyword to look up in this forum is "role-based" (with or without the hyphen).
 
Here's a sample with very simple security. It uses the Switchboard Items table to define security levels for each form accessible via the switchboard. If you need to have different levels of security for subforms, then you need a separate table so you can define security levels for each form/subform separately.

 
@MarlonVisser0408

The trouble with spreadsheets is that they don't offer you the strong data-typing at the column level, that you get with a database field.
Their really is no comparison. It's pretty bad that spreadsheets get used for all sorts of things where a database would make much more sense, especially in commercial and even government applications. The problem is that a database requires a much deeper understanding than a spreadsheet, and a casual user just can't achieve credible results.

For instance though, a database is always multi-user, unlike a spreadsheet.
 
There's two levels of security, if you will.

The bit that's easy is managing access to a form.

Design a set of user groups, and and users to those user groups. Then limit a form to a membership of a particular user group.
That's easy - as all you have is a function in the open event of a form to check whether the user has access to a form.

Much more complicated is limiting different users ability to do certain task within a form, (eg delete records). as every form will need manual coding to test the function and the user.
 

Users who are viewing this thread

Back
Top Bottom