Trying to build a DB for reporting

[bkrimminger]

Let me start by saying that I know very little about Access and have only put my toes into the ocean of possibilities that creating an Access DB would/could provide . I am thinking that Access would be the best product to do what we are already doing in Excel.

Every three months I have to build 20+ excel files for the 20+ different work areas I support. In the files are up to 4 spreadsheets (2 sheets for "permanent" employees and 2 sheets for "temporary" employees). On the sheets are the employees names and information related to their computer access. The work areas then take this Excel file and review it to ensure that the employees in their area have the appropriate access. The "stumbling" block in moving this to Access is the amount of information that could be available for any one employee.

My first thought is to put ALL of the information into 1 or 2 tables (or should it stay in 4) and then have reports that the work areas could pull. I've tried to see if I could find a sample database that I could use for this, but have no idea of what I am looking for. Any (and all) help would be greatly appreciated.

My data looks like this:

Permanent Employees
Spreadsheet #1
Name, Work area, title, last log-on, Primary Menu, Secondary Menu
- the last column is a multiple and can be 0-100+

Spreadsheet #2
Name, Work area, title, security Keys
- the last column is a multiple and can be 0-100+

Temporary Employees
Spreadsheet #1
Name, Work area, title, last log-on, Termination Date, Primary Menu, Secondary Menu
- the last column is a multiple and can be 0-100+

Spreadsheet #2
Name, Work area, title, Termination Date, security Keys
- the last column is a multiple and can be 0-100+

The problem I am running into right now is that when I try to create a report and preview the information in the multiple fields on an employee, I only get one menu/key at a time. If there are 20 or 30 menus/keys, then it is difficult to review.

My end result (and what I really need help on) is that I would like to provide the Work Area supervisor with a Access front end for them to select their Work area, Permanent/Temp employees and then Menus/keys and then they will be presented with a report on that person with the option to go on to the next employee. Once they review all employee information, they can then print a report stating they have reviewed the information.

If there is a sample db that works similar to what I have described, I would appreciate it if you can point me in the right direction.

 

[DJkarl]

Welcome to the forums first off, and also welcome to Access.

The first thing you need to think of is that tables are not to be treated like spreadsheets. Try not to think of it as a one to one relationship, one spreadsheet might best be split into many tables so that your database can conform to normalization rules.

Now if what I've said in the previous statement made little or no sense to you then before you dive into designing a database, you should check out some free tutorials online, or grab a beginner Access book and get some of the fundamentals down.

Access sounds like it would be a great solution for what you are doing, but if it's implemented incorrectly it will become a nightmare to manage.

Just my two cents (and speaking from personal experience)

 

[bkrimminger]

Thanks for the reply, and yes your comments make sense. I just don't know any thing about "normalization" rules. Since the employees change from month to month, all I want to have to do is re-populate the tables and the information is available in matter of minutes, rather than half a day to create all of these Excel spreadsheets. Plus I want it easy enought for anyone coming behind me to do.

I am about half way through Getting Started with Access 2003 CBT, and thought I would start looking for some samples/examples.

Thanks again, and please keep suggestions coming. . . . .

 

[dinosaur]

Hi,
I'm creating a database wich does something like you want.
I've a small security company. I have a lot of employees, some of them always work at the same place, bu others don't.
If you want to take a look check the topic called "any ideas..." in the general area...

 

[georgedwilkinson]

Since this is going to be a reporting database and not an OLTP database, normal normalization rules do not apply. If you decide later that you want the users to enter data directly into the database, you will need to delve into the topic of normalization for OLTP databases.

Please do a google on "Star Schema" and "Snowflake Schema" to get started. Ignore the commercial stuff. Read and understand all you can. Post back if you need help after doing your research.

One other thing...depending on how big the data gets, Access might not be the right tool to build a data warehouse in. You may want to consider a more commercial DBMS.