1. You could probably do this in Excel by creating a pivot table, and in Access by creating a totals query or crosstab query.
2. Your attachment contains a screenshot. That doesn't tell us anything about how those log files are being created. There is probably VBA code that generates them, so open the Visual Basic Editor (Alt+F11) and inspect the code behind the workbook. Don't forget to view the code (if any) for ThisWorkbook and for each of the worksheets.
Thanks for your reply post, and I'm sorry that you didn't get the attachemnet. I'm trying to attach it now...
The workbook in your attachment gives no indication at all where the department comes into this, so I cannot help with that. I think, however, that it would be much easier to process the data if they were stored in a table format. In Excel, you could design a user form as interface to the table. Access would be an even better choice (but I'm biased).
The workbook contains no VBA code, so I still have no idea where the log files come from. No log file is created when I fill in one of these forms.
I agree that Access would be a better choice and I decided to proceed with it. I would also appreciate your input in regard to the table design.
Following is what I try to accomplish:
We have three departments (COP, CCP, SHH) each department have 10-15 employees and all the departments use the same form. Every now and then, a department head or a supervisor from one department would like to have a view only access to another departments record and every month, every quarter, and at the end of the year, I generate a report (first by users, second by department ). The way I plan this process is to design three table (tblUsersCOP, tblUsersCCP, and tblUsersSHH). The form also will have few drop downs, (e.g. GENDER: male, female RACE, AGE, etc.) but not sure if I should create a table for all my dropdowns
Thank you in advance for your help!
I wouldn't create separate tables for the three departments, but a single table (tblUsers) with an extra field specifying the department. That makes it easy to select records for a single department, but also to select records for all departments combined.
For Gender, it's probably a toss-up whether you want to use a value list or a lookup table, but for any dropdown with a longer list of choices, such as Race, I would use a lookup table. Much easier to maintain.
BTW, if you're starting to design your database in earnest, it is probably better to create a new thread in the Access forum. We will then lock this thread, with a link to the Access one.
Thank you Hans,
I'll start a new thread in Access forum!
The new thread in the Access forum starts at <post#=403323>post 403323</post#>. Please post reactions there. This thread is locked.