Filtering User Names from one cell

[ollywalker1]

Hello,

I would be grateful if someone could help me with the following issue: -


Table 1
Name (One unique name per a cell)
Role

The above is a table that I manually enter the names and roles in

Table 2
Name (Multiple unique names per a cell - Example James Brown, Paul hill,
Course

The above table is linked to a separate database, so this would already be populated.

I am using both of these tables in a query alongside a number of other tables.

I'm trying to link the 'Name' column from both tables and have Table 2 only show the names that are listed in Table 1. Because there are several names in one cell in Table 2 I can't work out how to do this.

Can anyone help?

Thank you............

Olly

 

[khawar]

In the query designer link the name and it will show you only that names which are in both tables

 

[georgedwilkinson]

There are so many things wrong here but one thing you must NOT do.

Do not store multiple names in one row of a table (as you seem to be doing in Table 2). Now, you may not be doing that as this is Access and you are using Excel terminology, but I did want to warn you, just in case.

Once you've resolved this (if it is indeed a problem), Khawar's advice should get you closer.

 

[ollywalker1]

Thank you for your replies.

As you pointed out the only way I can match the Name column in both tables is if I have a unique name in each field. Ordinarily I would do this because I know it causes many issues.

I'm not able to separate the names from the source data that is being linked to access (Table 2), as this is not my system.

Can I somehow solve this issue in access?

Any help would be much appreciated.

Olly

 

[DCrake]

As Geaorge stated you are in serious trouble with the legacy data. The only time you will get a match is if there is only one name in the multi value name field and it matches exactly with that in your proprietory table.

Does the linked table not have any other fields that you could possible match to?

David

 

[ollywalker1]

No, there isn't any other column I can match

Let me give you a bit more detail on the database and what I'm trying to achieve, which might help.

Table 1 represents the employee names, job titles and expected training requirements in the organisation. I would manually enter this information in and will not include everyone in the organisation.

Table 2 lists all of the training courses that have been delivered in the organisation. This information is captured in SharePoint and is fixed in terms of the way the trainer captures the course information and the attendees (being the name column). The table that lists these courses has been linked in Access (As Table 2) and as the SharePoint is a fixed system I can't separate the names.

I need table 2 to only display the training courses for the individuals that have manually been enter into table 1.

I hope this makes sense.

I would have no problem performing this in Excel, as I would do a Text to Column to separate the fields. With my limited knowledge of Access and my research I just can't find a way.

Any other ideas.

Thank you )