Left Function

[Aoife]

Hi,

I have 2 tables:

1. Application
2. Users

I need to do a count of applications per user. In the Application table I have a field 'SalesAgentID'. I need to link this to the field 'AgentID' in the Users table in order to display agent name which is held in the Users table. The problem is that every time an agent views or edits a particular application, their ID is written to the SalesAgentID field so you can end up with multiple ID's in this field.

Is there any way of saying 'Look at the first 7 chars in the SalesAgentID field, match this with the AgentID field and display these first 7 chars along with agent name held in the Users table?

Thanks

 

[KenReay]

Hi

Are you saying that the SalesAgentID contains multiple Ids?

If yes, how will taking the leftmost seven characters help you?

Theer is a function Left() which would allow you to do as you say, but from your explanation I do not see how it will help.
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[Aoife]

The SalesAgentID field can contain multiple id's. However, I just need the first one as this is the agent who initially submitted the application. The problem is that I need to display the agent name which is held in the Users table. To do this I need to join the SalesAgent ID field to AgentID in the users table.

 

[KenReay]

Hi

OK

So make a query based on the Application Table, include all the columns you want, plus a 'calculated column X:Left(SalesAgentId,7)

Save this query

Now make another query based on UserTable and the above query, join on X and AgentId

Select the columsn you want to include in the query

Theer you have it. Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[Aoife]

Thanks for your help - much appreciated.

Aoife