Hope someone can help me here...

[Mugs]

Hello,

I am a novice user of Access 2003 and have myself up to my ears in a problem.

I have a table with a field in it (call it dept_code for arguments sake) that stores numerical values separated with a comma in one table. The values its storing are the depts that the managers in that table are responsible for looking after. I have been able to get the values loaded into the field (as a string) using the fieldcontents + "," + variable1 format method with each addition adding to the existing value in the field, a comma and then another dept code (2 digits).

The table also stores user information for login and is separate from the table that stores the core employee info they will be working with.

The other or (main) table contains records for every employee in the company with various details. I have been trying to figure out how to get access to read the dept codes separated by commas from the managers table and then using it to present only those records that match the dept code from the employees table.

The problem is sort of two fold, one how to I get access to read that "string" and see it as several dept codes and then compare them to the main table and show only the records that have matching dept codes?

I have tried using filters in a number of ways, however using a filter allows users to simply click unfilter to gain access to all records, and prevents them from using their own filters.

From what I have been able to gather I need to use some sort of select/where/in statement.

SELECT Main_data_Table.*
FROM Main_data_Table
WHERE Main_data_table.[Cost Centre Number]
In (SELECT [resp_cc] FROM Security WHERE Security.[emp_num] = [cur_emp_id]);

resp_cc is a global variable defined as a string
cur_emp_id is also global but is an integer


This example is as close as I got but still does not read or work properly whether I insert it into code or use it as an SQL query.
I apologise in advance if my terminology or question is incorrectly worded or formatted however I am a novice user and although I did development in college its been over 10 years since I used any of it, so I am kinda out of date.

Any help with this would be mucho appreciated since I am on a time critical project that needs completion soon and until I figure this out I cant move much beyond it. Thanks in advance to everyone for your help.

Regards,

Mugs

[MMcCarthy]

Take the department code out of the table where it is currently stored. Create a new 'Join' table, containing dept_code and emp_num. Make them a joint primary key. and add one department code per employee number. (e.g. Employee Number 215 and department code 12, Employee Number 216 and department code 14, etc.)

BTW: Don't use + sign to concatenate strings in VBA use &.
& "," &

Hello,

I am a novice user of Access 2003 and have myself up to my ears in a problem.

I have a table with a field in it (call it dept_code for arguments sake) that stores numerical values separated with a comma in one table. The values its storing are the depts that the managers in that table are responsible for looking after. I have been able to get the values loaded into the field (as a string) using the fieldcontents + "," + variable1 format method with each addition adding to the existing value in the field, a comma and then another dept code (2 digits).

The table also stores user information for login and is separate from the table that stores the core employee info they will be working with.

The other or (main) table contains records for every employee in the company with various details. I have been trying to figure out how to get access to read the dept codes separated by commas from the managers table and then using it to present only those records that match the dept code from the employees table.

The problem is sort of two fold, one how to I get access to read that "string" and see it as several dept codes and then compare them to the main table and show only the records that have matching dept codes?

I have tried using filters in a number of ways, however using a filter allows users to simply click unfilter to gain access to all records, and prevents them from using their own filters.

From what I have been able to gather I need to use some sort of select/where/in statement.

SELECT Main_data_Table.*
FROM Main_data_Table
WHERE Main_data_table.[Cost Centre Number]
In (SELECT [resp_cc] FROM Security WHERE Security.[emp_num] = [cur_emp_id]);

resp_cc is a global variable defined as a string
cur_emp_id is also global but is an integer


This example is as close as I got but still does not read or work properly whether I insert it into code or use it as an SQL query.
I apologise in advance if my terminology or question is incorrectly worded or formatted however I am a novice user and although I did development in college its been over 10 years since I used any of it, so I am kinda out of date.

Any help with this would be mucho appreciated since I am on a time critical project that needs completion soon and until I figure this out I cant move much beyond it. Thanks in advance to everyone for your help.

Regards,

Mugs

[Mugs]

Take the department code out of the table where it is currently stored. Create a new 'Join' table, containing dept_code and emp_num. Make them a joint primary key. and add one department code per employee number. (e.g. Employee Number 215 and department code 12, Employee Number 216 and department code 14, etc.)

BTW: Don't use + sign to concatenate strings in VBA use &.
& "," &

Thanks for the help. I managed to find a different solution during the evening and so have not been able to implement this one yet. Having said that, I will try these suggestions as well and see if that works. I appreciate the response and the value of this site and its members, novices like myself would be lost without these sorts of resources.