Questions on the possibilities of Access.

[SirTypos]

Hello!

I always wonder on the possibilities of Microsoft Access and how it can make Data Gathering and Validation with ease and precession.

Here's my case:
I work in an office with 50+ Employees that are required to add information of invoices and their amount on a regular basis.

all that information would then be gathered in a major base file or report to show case the work of each employee in a presentable documents.

what I'm curious about, can access do the following actions? :

1-Each employee would have his own access file to input his Data, once he's done with the entry it would automatically be recorded in a base file that collects all the employees inputs .

2-Data validation would occur to spot if an invoice has been written incorrectly and alerts the user

3-Several error messages like: Duplicate - Invoice not found. to appear.

Now my limitation is that we work on a Network Drive and have access 2013.



Apologies for the long text, but if access would make life a little bit easier than excel,
i would pour in my efforts onto learning it.

Thank you.

[ranman256]

Yes access can do this. I built this exact db. Many employees entering data.
the data screen would perform the data validation before it adds it.

as to the solo user viewing only his data, you may need some vb programming. I had a user table, so when the user started the db, the userID is put on the form, so when they ran queries, it pulled only that users data.

[SirTypos]

Thanks for the response ranman256.
That's great to hear.

and yes, an employee would regularly may want to check his recordings for evaluation purposes or revisions.

if i may ask, how difficult it is (Intermediate - Advanced ) and what do i need to learn in access to build such files?

would like to start with it for 2017 new data entries, that why i asked on difficulty to predict the needed time to learn and build.

[aytee111]

Databases are defined to be shared by multiple users, so don't worry about that part of it when you are designing it. You would design one database with one process and the users would share it. For a network it is the usual custom to split the data into its own database, called a back-end, which resides on the server and then each employee would have their own copy of the front-end, the part that contains the form, queries, etc.

The first thing to do is to design the table structure. At a minimum you would have two tables, one containing customers and one containing invoice data. You can start there and build up as you go along.

Once the tables are defined you would design a form for the employees to input the data. The form will be tied back to the tables which are shared, so all data entered will be added to the invoice table as the employees are typing it in.

Then you can start on querying the data....

[Micron]

Ask 10 people this question and you're likely to get 10 varied answers. To that point, I'm saying once tables and relationships are defined/built, the next step is not forms, but queries. If you cannot return the data in a query, your table structure is either inadequate or wrong. Same can be said if your query cannot be updated (unless it is of the type that never can be, such as UNION or Totals queries), so I suggest these tests even if the queries are not to be used for anything else. Then again, I don't know why they wouldn't since a form or report built on a table is less common than one that depends on a query; especially if you consider that normalization means most of the time, related data is compartmentalized into separate tables. This means most forms and reports cannot function without an underlying query that brings these records together in meaningful ways.
Rather than repeat myself, take a look at my post #10 here https://www.accessforums.net/showthread.php?t=62712 for some links that might get you started. Suggest you research and find any of the countless tutorials based on Access database design.

[ssanfu]

Might also see

Database Design Basics
Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html


How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

[SirTypos]

Thank you all for your replies and the links.

surely brought hope up for having next year's work to be easier.

Now, onto intensive Access learning!