query to show what folders employees have access to using yes/no as criteria

[michael butler]

I need help in running a query to show what folders employees have access to- currently there are 222 folders, when I tried doing the query it told me that it was too complex to do-I do not want a list of all folders just the ones the employee has access to.


the fields in the folders table are set to yes/no-

Employee ID#	Abuse	Abuse Database	Accounting	Accounting CW	Accounting LBB	Acounting Volunteer	Acute Administration	Acute Physicians and Psychologists	Acute Rehab Transcription	Acute Service	Acute Service Timekeeping	Acute Service Timekeeping Memos	Acute Service Transcriptionist	Acute Services Forms	Acute SW Transcription	Acute Transfer	Acute Transfer Dr Calendar	Administration	Admin Registration and Tracking Log	Admin R/S	Admissions	Admissions Share	Admit Logs	Adolescent	Adolescent SPEC Forms	AMARS	APPS/TRWIN	Avatar Reports	BHIS Reports	CAMH Joint Commission	Casa Amistad Shared	Cash	Cashiers	CFO Files	Chapel	CHCS	Client Data Card Photo	Client Rights	Clinical Dietetics	Clinical Engineering	Clinics	CLRIGHTS	CLUSTER I STENO POOL	Commitments	Communications	communications Web Data	Community Activity Center	Community Relations	Contracting	Conf Comm OD_NA	Consumer Service	Consumer Service (PSAT)	Consumer Service Reports	Consumer Service Rights Database	Contracts	CPM Facility Cost Report	Crockett	CT-D Web Data	CWS Order Entry Print	Crystal Reports	Database Access	DataCard	DataLink	Decals	Dental	Dental Clinic	Diet Cards	Disable Desktop Redirefion	Driver's License Records	Emergency Management	Employee Injuries	Environment Of Care	ERP	Everyone	Executive Committee	Falls Data	FMU	Food Service	Food Service Diet Cards	Food Service Production	Fuel System	Hospital SOPs	Hospital Summary	Houston	Houston Nursing Forms	Houston Psychiatry	Houston Shared	HR	HR Eval	HRD	HRD Admin	HRD Class Registration	HRD Web Calendars	Infection Control	Information Mgmt	Information Mgmt Security	Information Mgmt Web Page	Information Technology	INTERNET	INTRANET	JCAHO	Keys	Laboratory	Lifescan	Logbook	LSAH-SIS	Maintenance	Maintenance Projects	Media Center	Medical Records	Medical Records Census	Medical Records Data	Medical Records\Summaries	Medicare-D-RX	Melinda's Bug Blog	MHA Daily Reports	MS Publisher	MSDS	Music Therapy	National School Lunch Program	Navarro	NEO SSN	Nurse Admin Office	Nurse Manager	Nursing	Nursing Admin	Nursing Admin Emergency Medication	Nursing Admin R/S	Nursing Admin UD Report	Nursing Archived	Nursing Archived Staff Coverage Sheet	Nursing Education	Nursing OD Report	Nursint PI	Nursing Report	Nursing Share	Nursing\\Nursing Reports	Off Grounds Medical Svcs	Operations and Production	Operators	OT Medicare	Pagemaker	Pager	Patient Injury	PFEES (Professional Fees)	PFVoucher	Pharmacologist	Pharmacy	Pharmacy Prepacking	Pharmacy Users	Pharmec	Physical Therapy	Positive Performance	Property	PSAT	Psychiatry-Houston	Psychologist	Psychology	Public File Folder	Public Folder	QM Patient System Admin	QM Patient User	QM Positive Descriptions	Quality	Quality Management	RAJ_User	RED	Registered Nurses	Reimbursement	Reimbursement Logs	Remote Assistance	Report Writer User	REQINFO_READ	Requestion Info	Rights	Risk Management	Safety	SASH-TCID	SASS Reconciliations	Security	Security Folder	Seguin	Seidel Learning Center (SLC)	Social Services	Social Services Admin	SOP Revision Folder	SPACCESS	Staffing Coverage	Statewide Information Services	Student Data Folder	Subscriptions	Supply	SW Memo	Switchboard	Switchboard Admin	TB Admissions	TB Clinic	TB Clinic Immunization	TCID	Tele-Communications	Therapies	Terapies Managers	Threat Response	Travis Nursing	Travis Nursing Admin	Travis Spec Forms	Travis Transcriptionist	Travis-Groups	Trust Fund	Trust Fund Social Worker	Trust_Fund Printer	Users	VIA Hope	Volunteer	Web Page	Workcomp_Users	Workers Comp	Scan To File	SharePoint	SharePoint-Information Management	SharePoint-Medical Records	Outlook Users	Webmail Users
240681	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	True	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	False	True

[ItsMe]

Perhaps a new table that has two columns, one for the Primary Key (Autonumber) and another for FolderName (text). Another table (Junction Table) would have four columns, one for its own PK, a Foreign Key for EmpID, a Foreign Key for FolderID, and another column to indicate if the employee has access or not (Yes/No).

[rpeare]

Yay for normalization! I agree with ItsMe.

[June7]

Another vote for normalization. Another poster's experience with non-normalized yes/no fields structure https://www.accessforums.net/access/...xes-43408.html

[michael butler]

ok, i will try this- thanks for the prompt reply

[michael butler]

If I follow these instructions, I would have to renter all of my data into these new tables? I was hoping to avoid having to do this. the folders information is contained in a subform- with social security as the primary key that links to an all users table.

[June7]

Could build a UNION query to rearrange the data into normalized structure then use that query as source to create table. Limit of 50 SELECT lines in a union and must type or copy/paste in SQL view.

SELECT EmpID, 1 AS CatCode FROM tablename WHERE Abuse = True
UNION SELECT EmpID, 2 FROM tablename WHERE [Abuse Database] = True
...;


BTW, recommend no spaces or special characters/punctuation (underscore is exception) in naming convention.

Also, I have read that SSN is bad idea for PK/FK. For one thing, privacy laws can get you in trouble. For another, apparently they have been known to be duplicated.

[rpeare]

I am in a business where we need the SSN as a *PARTIAL* client identifier. It is subject to all kinds of errors and, as June said, not be used as a primary key. Primary keys should not be subject to data entry of any sort. Another approach to your problem is to run a series of append queries where you just change the field you're appending to your new table, you can accomplish this with code but hopefully it's a one time deal but 222 fields is a lot to slog through without code.

Here's some code to help you get the field names of a table (note I didn't test just giving hint):

dim db as database
dim rst as recordset
dim sFieldName as string

set db = currentdb
set rst = db.openrecordset(<INSERT TABLE NAME WHERE DATA IS CURRENTLY STORED>)

for i = <x> to rst.fields.count - 1
    sFieldName = rst.fields(i).name    
    <Insert your SQL creation statement here>
    db.execute (<Insert SQL String here>)
next i

where <X> would be the first field that has your yes/no response

your SQL statement would have to contain the employee ID, the folder name (which you can get from the current field name above) and the actual values from the table.