Can Someone Give me a Good Starting Point?

[m2244]

Hello,

I'm new to Access, although i have a fair amount of experience with SQL and other languages.



With that said, I'm trying to put together a simple tool for internal office use. Right now the front office uses several spreadsheets to track personnel and other office data. I was hoping to replace Excel with Access so that the front office could use the tables in Access similar to the way they use Excel, almost zero learning curve. This is an information sharing "dashboard" of sorts. The problem is, I don't want to display all of the personnel information that the front office tracks, such as SSN, etc. What I was hoping to do was give the front office 1 table to work from, and somehow pull only the information the rest of the office needs into a second table.

I understand relational databases by the way. But Access has so many other features like reports (tried it, didn't quite fit the bill), parameter queries, etc. What should I be using? Can someone give me a few ideas?

[Cottonshirt]

one way to do this is to divide personnel data into two types, visible data that anyone can see, and discrete data that you would prefer to keep secret. then have discrete data in a separate table.

you can also split the database into two parts, that are traditionally referred to as front end and back end.

the back end can be seen by developers and programmers, while the front end can be seen by users. you can define which database objects (forms, tables, reports, etc) users can see, so you can literally decide on a user by user basis who can see what.


good luck with your project,



Cottonshirt

[m2244]

"one way to do this is to divide personnel data into two types, visible data that anyone can see, and discrete data that you would prefer to keep secret. then have discrete data in a separate table."

How would I pull that data into a second, visible table? A custom query/insert?

Also, thank you for the help.

[Micron]

If that plan duplicates data then I would not do that. In any Access db that contains proprietary/private information, you design to prevent access to that information through tinkering in tables. Only forms and reports serve up information, and they are based on queries, not tables. Those queries cannot be viewable by way of poking around in the nav pane and you limit access to data/forms/reports based on user profiles/levels.

[June7]

Agree with Micron. Separating data into different tables that should really be in one table is just complicating structure. Control what data users interact with through interface design.

[orange]

I understand relational databases by the way. But Access has so many other features like reports (tried it, didn't quite fit the bill), parameter queries, etc. What should I be using? Can someone give me a few ideas?

If you are intending to use Access as the basis of a database application, then I would suggest that Normalization would be a priority. Access and Excel (database vs spreadsheet) are built on different object models and not readily interchangeable. From a well designed user interface you could hide many of the details of the underlying structure from users. But as Micron and June have suggested, this would not be a 1 or 2 table thing. Users would only interact by means of a series of forms.
As with any automation to support business processes, it would seem that a clear statement of requirements; some analysis leading to a general design; feasibility review and approval would be a common approach.
There are several articles in different formats in the Database Planning and Design link in my signature that you may find helpful.
The separation of general and proprietary/private info could be handled with a separate table(s) in a separate, limited access database; or some sort of user accounts (role-based-access control). The specifics would be determined by analysis of the requirement details.See this info from Pat Hartman re tables/privacy.
Good luck with your project.