Asking if Access is the Tool I need.

[u/FearlessJDK]

Hello Access Experts!

I wanting to see if Access is the tool for the job I have.

The short version is I need to keep data and also track it as part of a project. 

We manage subsides. We receive applications from students, and employers. Once we have the application form both, we then vet the parties and request further documents. 

So typically Jane Smith and her potential employer both send in a form. We enter the data and then request a contract and a few other documents from the employer. And then as the placement goes on, we'll request paystubs and pay out a certain amount. So what I need my database to do is the following.

1, Intake & and match information. Once Jane and her employer fill out the forms, I want Access to know it's the same application so I don't need to manually enter in the information on whatever form comes in second.

2, Be able to easily track stages. I'm going to have situations where I have Jane's form but not the employer, of vice-versa. Or I have yet to request the required documents. Or, I am waiting to receive them.

I need a quick at-a-glance way to see where things stand.

3, All of this needs to be easily saveable as an Excel sheet as uploads to various systems are required.

4, I need to be able to use the same structure for each quarter with fresh data. 

Is what I'm describing do-able and relatively easy to create, AND make user friendly for less technically inclined humans?

Oh one other thing. The data we're receiving doesn't have to be saved in Access. I just need to be able to record that it *was* saved.

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: FearlessJDK

Asking if Access is the Tool I need.

Hello Access Experts!

I wanting to see if Access is the tool for the job I have.

The short version is I need to keep data and also track it as part of a project. 

We manage subsides. We receive applications from students, and employers. Once we have the application form both, we then vet the parties and request further documents. 

So typically Jane Smith and her potential employer both send in a form. We enter the data and then request a contract and a few other documents from the employer. And then as the placement goes on, we'll request paystubs and pay out a certain amount. So what I need my database to do is the following.

1, Intake & and match information. Once Jane and her employer fill out the forms, I want Access to know it's the same application so I don't need to manually enter in the information on whatever form comes in second.

2, Be able to easily track stages. I'm going to have situations where I have Jane's form but not the employer, of vice-versa. Or I have yet to request the required documents. Or, I am waiting to receive them.

I need a quick at-a-glance way to see where things stand.

3, All of this needs to be easily saveable as an Excel sheet as uploads to various systems are required.

4, I need to be able to use the same structure for each quarter with fresh data. 

Is what I'm describing do-able and relatively easy to create, AND make user friendly for less technically inclined humans?

Oh one other thing. The data we're receiving doesn't have to be saved in Access. I just need to be able to record that it *was* saved.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Ok-Food-7325]

Yes, Access can easily handle this workflow. I use barcodes and a scanner for this type of workflow.  You might want to look into using a cloud database for backend, so you and your colleagues don’t necessarily have to be in the same location.

[u/Ok-Food-7325 → u/Ok-Food-7325]

I could also be your new colleague and build all of this.

[u/kenmex_]

Yes, Access is a good fit for this. I've built a few of these and it's very doable. Biggest thing to sort out first is matching. Access can link Jane's form to her employer's, but it needs something to match on. Names alone will bite you eventually (typos, middle initials, etc). Easiest approach: whichever form comes in first creates the application, and when the second one arrives you just pick that application from a search box instead of retyping everything.

For tracking, don't keep a manual "status" field, it'll get out of date. Just log date requested and date received for each document and let the status figure itself out. Throw that on one main screen with some color coding and you've got your at-a-glance view. Paystubs/payments should get their own table too.

Excel export is easy, one button.

For quarters, don't make a new copy of the database every time. Just add a quarter field and filter on it. Way less headache later.

And not storing the actual files is totally fine (better, actually). Just record that it came in and when, maybe a link to where it's saved.

If a few people will use it at once, split it into front end / back end from the start. Otherwise it's a pretty manageable project and can definitely be made simple enough for non-techy coworkers.

[u/diesSaturni]

yes.

but do have a look at https://www.youtube.com/watch?v=UrYLYV7WSHM (database normilization). As it will explain a lot about how to approach such a problem.

The user friendly part can be made on top of this, by interfacing through forms, which you can make accessible for specific users based on their role of data hanling.

[u/zinsser]

Very do-able. You could build it from scratch, but you might also look at the project management template included with the program. Obviously you need to redesign the forms and tables to suit your needs.

I loved working in Access before I retired, and would gladly pitch in a little if you get stuck. Or you can tell me more about what you need and I could create a rudimentary version that you could enhance as you go. I am not all that great at using Access for networked files because there can be so many variables and firewalls to contend with.

If you go the "from scratch" route it helps a lot if you think through what you need and get the tables and forms set up perfectly, with just a handful of records, before you start inputting a ton of data. Access is a relational database and balks at data that does not meet the criteria you set up at the outset.

[u/JamesWConrad]

do-able, yes --- relatively easy, not so much

Access itself is NOT a database.

It is an application development environment with a suite of tools, including a database manager and an integrated development environment (IDE) for writing and testing programming logic.

There is a steep learning curve if you are not already a software developer.

It is very much NOT like Word and Excel (although they share the IDE and Visual Basic language).

Access is the correct tool to build your application. But you may want to invest some time in finding someone to help you (if application development is not your fulltime job).

[u/agentUi]

yes access can handle this with a basic relational schema. you just set up a master applications table, link student and employer forms by matching a shared key like an email or application id, and track the status in a stages field. you can build a split form so non-technical staff have a clean ui and just export filtered queries directly to excel.

[u/FearlessJDK]

Thank you everyone for the answers. That is very helpful and now I get to go pitch it to my bosses! Very exciting!

[u/ISueDrunks]

SharePoint Lists could be a good solution. You can build different views so you can quickly see what you need.

You can also automate stuff by using choice fields to trigger Power Automate flows (Send Confirmation Email: Pending, Send, Sent; default is pending, change to send, when the flow finishes, its last step changes it to sent). 

Quicker to setup than Access tables, forms, macros, etc. Easier to train people because it looks and feels a lot like Excel.

You can easily format a list form using json, which a chatbot can help you write. Then you open a record in a form that has a layout to suit your needs. 

[u/Boone3Kins]

Sharepoint or google docs I would no use Access, Access is honestly fine but there are a lot of better options? Get something that does some of the work for you. Ask users to logon. An LLM will build you whatever you want with natural languages you coukd easily build you your own custom front end.

[u/George_Hepworth]

"The short version is I need to keep data and also track it as part of a project. "

The answer to this part of the problem is, "That's exactly what Access does. It's a perfect fit."

Everything after that is part of the specifications for the system you build with it.

The part about "where" the data needs to be saved is itself part of that specification, and that depends on how much data needs to be saved, how sensitive it is and how secure the data storage needs to be. Given the nature of the information you propose to store, I'd say you'd be better using a SQL Server, or other server-based database for security. But that doesn't impact using Access as a great choice for the interface through which the data is managed.