Can this be done?

[Rick]

I have two tables,
Table1 = Dept#, DeptName, Depthead,PhoneNumber,JobDesc,
PosQual,Enteredby,phone#2.
Table2 = (This table will contain 116 line items)
Example: Col1 col2 col3 col4
1. Lab Blood gas Laboratory
2. Meds Medications Medications
3. Pat Patient demo Patient info
116.
What would be the best way to handle this? What this database will do is, Our security person will put in the dept name, I would then like to see only the department head appear that goes with that department and only job descriptions that apply to that department. The biggest obstical i see is the table two info. How can I allow our security person be able to select one or more of the 116 options for each person in table one? Table 2 will consisist of items that a user from table 1 should have access to, so Bob Smith may have 1 item or 30 items from table two. Please let me know if this is not clear, I have a layout of how the "Big Kahuna" wants to see the input form. The way he wants to see it is, have the department info at the top of the page and then the 116 items listed below where the user can put a check mark next to the item. I tried it that way but i cannot get all 116 items to show up due to the relationships. Please help and please use laymens terms, I am still learning. Thanks in advance

 

[shay]

Hi

Can you explain a bit more about table 2. What's col1, col2, col3, col4?

shay

 

[Rick]

Sorry

As far as I can tell, I will need a second table to show the following, Again these are predetermined.

Table 2 = Col1(Named whatever)
Col2(Named whatever)
Col3(Named whatever)
Col4(Named whatever)

Col1 Col2 Col3 Col4
BBK Test BloodBank Test BloodBank
DI Imaging Diagnostic Imaging Diagnostic Imaging
LAB Body Fluid Other body source Laboratory
LAB Blood Gas Blood Gas Laboratory

Etc...ETc There are a total of 116 of these predefined records.

Again, The input form has the following: Sample Below

Department#: 9030 Dept Name: Information Systems
Job Desc: Programmer

Position Qualifier ( Not sure about this one yet)
Entered by Rick Phone Number 703-369-8373

Code PHI Description Function
1. DI Imaging Diagnostic Imaging Enters PHI
2. LAB CBC Hematology Edits PHI
3. MEDS Medication Medications Reads Only

The above code can be an auto number 1 to 116
The PHI Description will consist of col1 col2 col3 col4
The function will be a drop down box with only the three selections you see above.
Again, This DB is to allow our security person to assign and track who has access to what Meditech module. Please let me know if you need more info. Thanks

 

[Rick]

Shay

I see that when I save my post, It throws off the columns so Here is what it should look like:

Col1 = LAB
Col2 = CBC
Col3 = Hematology
Col4 = Laboratory

Hope this one shows better. Thanks

 

[Pat Hartman]

For starters, table2 should have only 2 columns - AuthID (autonumber primary key) and AuthCode. So you would have something like:

1 LAB
2 CBC
3 Hematology
4 Laboratory
5 DI Imaging
6 Diagnostic Imaging
7 Enters PHI
8 ...
....
....
348 ......

Then you need a third table to relate the auth codes to people. That table also needs two columns - PersonID and AuthID. There would be as many rows in this table per person as that person has auth codes.

To implement this many-to-many relationship you need a form with a subform. I strongly recommend that you don't try to implement this as checkboxes on an unbound form. It will require a great deal of code and you don't seem to know how to code plus, it will require maintenance any time the auth codes need to change in any way.

I also recommend that you rethink the whole process. This could easily become a maintenance nightmare. See if you can find a way of grouping people or grouping authorizations. That way assigning a person to a group would give him the auth's of the group.

 

[Rick]

Thanks Pat

I appreciate your feedback, Can I switch gears a moment? I found a way to do what I want but for all of the access pros out there, I am sure I will get an earfull : )
What I am doing is using a "Label" as a description with a "Yes/No" Field attached. This will do what I want only because the Descriptions will not change over time (Yeah, famous last words) Please don't get to "POed with me for a followup question but, here goes: What is the best/easiest way to have a user enter a deprtment number and have the following fields auto populate?
After entering the Department # The below fields should populate.
Department Head (From the above Department #)
Job Codes(for that particular department only)

Again, Sorry for the second parter. Happy holidays everyone!!

 

[sambo]

Use a query as your recordsource. This query should resemble the following...

(Don't copy this exactly, IT WILL NOT WORK!!)

"SELECT yourTable.DeptHead, yourTable.JobCodes, etc... FROM yourTable WHERE dept# = yourForm!dept#"

Use the visual query building tool to build this. Use the Help files to guide you through this process.

 

[Rick]

Thanks Sambo

I will give it a shot. I appreciate all the help from everyone on this site. Happy Holidays