Filter Vs Queries or both

[sladetroityer]

I am creating an employee database and I am having trouble filtering employees by department number. I have 11 different groups that utilize over 270 different department numbers and I want to filter these departments so that only the Electrical group shows up in a form. I also want to use one form for this instead of a form for each of the 11 groups.
Currently I am using separate queries for each group and this works but then I'd have to make a from for every query which I'd rather not do.

Is there any way around creating a form for each group based off queries?

PLEASE HELP

 

[spikepl]

Show your table structure. Attach a picture of your tables and relations, from the Relations window.

If you have to use different queries to fish-out the employees then that is a tell-tale sign of rot in the data structure.

 

[sladetroityer]

I tried to attach pics but they are too large. My tables have been normalized, and as far as structure and relationships are concerned as is well. The different queries are for different groups. There will be multiple people using this database and I want each individual to only see the departments that belong to them. That's why I have different queries to fish out the employees. I want to use one form based off the employee database with filters for the groups?

Or how do you suggest I do this?

 

[spikepl]

I suggest that you attach the pics nevertheless. Shrink them into jpegs, zip them. Different queries for different groups suck, and imply rotten structure. All ppl should be in same table, tagged with the specifics that make them different from all the other groups. As to who can see what is just a question of coding the right access, not of making entirely different queries or forms.

 

[sladetroityer]

All people are in the same table. Zipped files are too big, jpegs are too big.

I have no problem sending this database or emailing you a picture.

Also, Microsoft removed most security options in 2010, they would rather have IT control who can and can's access what groups.

 

[spikepl]

If all ppl are in same table then what is the problem? To get them listed just requires some parameter in the query matching the group, and only ppl belonging to the group are allowed to use that parameter - all in the same form, if you keep track of who is logged in, and what they are allowed to see (or which group they themselves belong to).

(If you have a jpeg exceeding the upload limit here then I am truly impressed But never mind, if all are in the same table then it doesn't matter.)

 

[sladetroityer]

The problem is that when I go to my MAIN form all employees are being displayed and the Chassis group doesn't want to see the Electrical group's data and so on and so forth. thus without creating different forms for each group how do I accomplish this?

 

[spikepl]

Then fix the underlying query - you select what goes into the form. So select what is appropriate for the person looking at the form. If your form is not based on a query but a table then change that.

tblPpl
-------
PersonID
personal data
DepartmentID

so if someone that has a given DepartmentID runs the thing, then the query just picks all who have the same DepartmentID.

 

[sladetroityer]

Yes, all my forms are based off queries. I have an qselEmployeeInformation query that has ALL the employees in it. So your telling me to create a table that gives certain people rights to see certain data? Uggh, I was hoping for a filter that would run off a cmdButton. Currently I have a FORM with all the Groups listed on Buttons and I was hoping that I could get a filter or code that would only show certain data when a particular cmdButton is clicked. From there they would be working with their data only. Approximately 13,000 people here.

 

[sladetroityer]

I'd really like to send this to you. Can you net meet? I will be back tomorrow. It's time to go home over here in Detroit.

Thanks

 

[spikepl]

So your telling me to create a table that gives certain people rights to see certain data?

No. I am telling you that if someone with a DepartmentID=X is only allowed to see data pertaining to DepartmentID=X, then only allow that person to see that data, by sorting it in the query.

If peoples' names in the table do not correspond to their logins, then you have to add their logins, to be able to id who is looking. IF the names do correspond, then you can get the login name from the Environ function, or make your own login

 

[sladetroityer]

What I'd like to do is restrict the data to certain groups with a command button. Is this possible? Click on a cmd button and have a ONE form that the restricted data is sent to.

There will be no login and each group (Electrical, Body, ect.) has many department ID's.

 

[BBQ Kittens]

Looks like Spike answered your question despite not knowing any of the structure or relations...If you want the individual to select their department data by a button, then yes, this is certainly possible to display on one form. This can be achieved by filtering or by queries. You can also use combo boxes on the form to select the department and additional criteria. What you want can be achieved in many ways in fact. Though, without knowing any of your structure or relations, I can not venture to guess what is best for you. Concerning the login...if employees have user names on your network then the Environ function can retrieve that. All you would need to do is associate that user to a department and your form could display their department data automatically on open.

 

[sladetroityer]

Ok, so I created queries for each group/department but then I would need a form for each group too and I don't really want to do that. I tried filters but I can never get them to save.

I am also having trouble wiht the Browse To command. The Path to Subform Control is giving me an error

frmNavagationMain.frmReportCenter>sfrReportPage

This is how I have it and it's not working

 

[BBQ Kittens]

What is your access/vba background? Would you say your savvy/competent in access design as well as vba programming? I ask this because in order to help you understand and answer your question it must be explained at your level of understanding. I think the confusion began here when a certain level of access knowledge was assumed.

 

[sladetroityer]

I've been using access for a while now but only as a user not a programmer or designer. I have ZERO vba knowledge. I understand it but don't know the language. I have had no problems copying others VBA code and using it as my own, but I cannot write my own code.

 

[sladetroityer]

Savy in Access, no VBA Backgroud