Selecting multiple items based on multiple inputs 1

[puforee]

I hope I can explain this righ...here goes. I am responsible to assign accesses to many server/shares. I have a Access DB to track this. On my employee form I have 10 check boxes of which the user can select any number based on his/her job.

I have a table of server names. Each name has 10 check boxes. These check boxes match the names of the check boxes on my Form but do not feed the form. The checkboxes in the table are setup so each server has one or more check box possibilities. In other words the server will be assigned to a user depending on the checkboxes they check on the employee form. So I need a query against the server names table, that will use the input from the employee form to show what servers the employee gets access to.

So...how do I write the query so the checkboxes on the form bring up all the corred table check boxes?

Example....if check box 1 and 4 are checked...the query only show the servers with check box 1 OR 4 true. This works with check box 1 and check box 4 criteria OR'd in the query...but

Issue...the query against the server table references (Criteria)from the employee form for each of the 10 fields....when I select check box 1 on the form the query returns all severs because it is looking for check box 1= true and all the rest false.

I have tried OR'd statements and that does not work. I have tried criteria such as Checkbox 1 or IsNull(checkbox 1) ect in all the check box fields.

It is like having something on the tip of your tounge...it is there but I just can't get to it.

 

[PHV]

What is the SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

In addition to PH's question, can you provide some actual table and field names as well as any relationships?

Is this query the record source for a form or report?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

Remembering it does not work...here is the sql

SELECT [Server Share List].ServerID, [Server Share List].[Server Share], [Server Share List].[737CL], [Server Share List].[737NG], [Server Share List].[747], [Server Share List].[747-8], [Server Share List].[757], [Server Share List].[767], [Server Share List].[777], [Server Share List].[787], [Server Share List].Structures
FROM [Server Share List]
WHERE ((([Server Share List].[737NG])=[Forms]![Employee Frm]![737 NG])) OR ((([Server Share List].[747])=[Forms]![Employee Frm]![747]));

dhookom...table and field names are in the code.

 

[dhookom]

Have you considered normalizin your data so the solution might be simpler?

Is this query the record source for a form or report?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

This query will be change to an append query to anothe table...when it is finished.

 

[dhookom]

IMO, your table structure is not normalized and I wouldn't put much effort into it until you didn't have servers as names of fields. Server names are data values and should be stored in a field, not as a field name.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

dhookom,

I am using this tool to stored server access information. So, the name of the servers is data stored in the server field. This tool does not grant access or connect to the servers. It stores request information so I can do the granting of accesses.

Not normalized
The check boxes (like 737NG) are employee job catagories. Each server name can be associated to all of the job catagores...or none...or just some of them. The employee table has the job catagories listed as check boxes of witch one or many can ge selected. When the query is run it should return all the servers for each and all selcted job catagories. This data in turn will eventually be placed in another table that holds the employee pk and all the servers he/she have access to.

Does this make more sense?

 

[dhookom]

Ok, so replace my questions regarding server names with "job categories". These should be values in a field, not field names. I would have a tables like following with records rather than multiple check boxes:

[tt]
tblJobCatServers
============================================
jcsJCSID autonumber primary key
jcsShareID links to table of server shares
jcsJoCID links to table of job categories
jcsPrmID links to table of permissions/rights
[/tt]

[tt]
tblJobCatEmployees
============================================
jcdJCEID autonumber primary key
jceEmpID links to table of employees
jceJoCID links to table of job categories
[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK..I think I see what you mean. I will work on this.

A little more information.

On my employee form I have a sub-form for servers. On this sub-form I select (drop down) each server for the person.

What I am trying to do is automatically complete the list on the Server subfore by relating Enployee...Job Catagory...Servers and by pushind a "load" button it will query and select servers based on job catagory and then populate the Subform (employee server table) with th results.

This would save a lot of manual effort selecting servers one at a time.

Thanks...I will get back to you.

 

[dhookom]

Why would you relate servers directly to employees? Aren't employees related to job categories which are related to servers/shares?

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

No. The servers are related directly the employees. Employees get access to servers. Jobs don't.

But. Jobs can help determine which servers the employees should have access to. This would be basic server set up. More refind...like special access would still be done manually.

So Employees Table is linked to Servers. But I want to fill in servers for an employee based on jobs. I have over 300 people and the jobs only affect about 1/2 of them.

 

[dhookom]

I would still use the tables I recommended. You could then create an append query from the tables to add records to an Employee Server junction table.

Again, I would not mess around with un-normalized data.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

OK..here is what I have done. I have written queries for each job against the Employee table. These queries populate a table. I then query the table and retrieve all the servers based on all the jobs. Some jobs include the same servers as other....so my final query Selects Distinct. I have tried it with 3 jobs selected and it works good. This means a lot of queries but......

I have never tried to nest queries in code so I am will stick with all the queries that append to the table.

Best I could do now.

Thanks for all of your advice.......so far.

If you have anything else to add...please do.

 

[dhookom]

You didn't state whether you kept your un-normalized tables or decided to normalize.

Duane
Hook'D on Access
MS Access MVP

 

[puforee]

I kept them....They are individual table and only store the data (unique data) once...so I don't see a problem.

What I now do is create a single table from all my queries that holds the server name and userID. From that I run a query that table and Distinct the data. I now have a list of server names and a userID. This can be loaded to the Employees Servers subtable and will show a list of servers to be assigned back on the employee form/server sub-form.

I know this may sound flaky but it works and runs very fast. With my limited knowledge it works. If I could redesign the complete system I would probably do it better....but it is a tool all my supervisors use and has been in use for 5 years. I am just adding a bit of automation for them based on the job/s they will perform.

Again -- thanks for all your support and help.