filtering integers in a field

[Guest]

I have a problem in creating a relationship for 2 tables.
Table1 - represents a call list with destination phone numbers(
international).
Table2 - is a destination codes table in which the destination code field is
made up of different integer lenghts due to different destinations. The
intgers differ in lenght as in different countries the country codes and
regional codes vary. thus I can not separate by Left function a fixed # of
integers from my destination number(table1) and link it to the destination
code (table 2) .
I have a problem with the Dlookup function. I am not able to link
destination number(Table1) to destination code in(table2). How can I specify
in a query that a variable string of integers(starting from the left) from
the destination numbers (table1)should match the longest possible integer in
the destination codes (table 2)
Thanks!

 

[Guest]

Ivan,

Can you give us a couple of examples of the records in the two tables for
clarification? It would also help if we knew the table structure for these
tables.

Dale

 

[Guest]

Hi Dale

Thanks for the quick response
Here are some details for eg. purposes
Table 1
Dest number minutes
23456897888997 6
5647823875332 34
612349874443333 14

The table contains other fields but this is the one I have to link to the
destination table.

Table2

Destination code Destination Price per minute in$
6123 England 2
564 Peru 4
23456 Germany 2.5

I would like to link the two but the problems that I have are:
- the destination code integer lenghts are different as different countries
have different codes
I would like to create a query where I will be able to see what is the
destination and price per minute based on the destination number in table 1.
How can I use a Dlookup/Vlookup or any formula to determine the matching
destination code in table 2 which is build in the beggining of my destination
number in Table1 so that I can price the durrations.

Awaiting for your replay. Thank you so much for the response again

Best regards
Ivan

 

[Guest]

Table 2
cns country_code
917189 91 7189
917196 91 7196
917197 91 7197
917198 91 7198
917199 91 7199
2473 247 3
24740 247 40
24741 247 41
24742 247 42
24743 247 43
24744 247 44
24745 247 45



Table 1
EndTime DestNumber
Duration
2/1/2007 11:28:37 AM 33497234847 57
2/1/2007 12:43:18 PM 442078153100 46
2/1/2007 12:45:27 PM 442078153000 14

 

[John Nurick]

Hi Ivan,

Use a query along these lines:
SELECT <field list>
FROM [Table 1] INNER JOIN [Table 2]
ON [Table 1].[Dest number] LIKE [Table 2].[Destination code] & '*'

 

[Guest]

Thank you so much John
I am going to try it now. Please tell me what in what format is best to
extract these fields in from the server. Ie mumeric...

I really appreciate your help

Hi Ivan,

Use a query along these lines:
SELECT <field list>
FROM [Table 1] INNER JOIN [Table 2]
ON [Table 1].[Dest number] LIKE [Table 2].[Destination code] & '*'

Hi Dale

Thanks for the quick response
Here are some details for eg. purposes
Table 1
Dest number minutes
23456897888997 6
5647823875332 34
612349874443333 14

The table contains other fields but this is the one I have to link to the
destination table.

Table2

Destination code Destination Price per minute in$
6123 England 2
564 Peru 4
23456 Germany 2.5

I would like to link the two but the problems that I have are:
- the destination code integer lenghts are different as different countries
have different codes
I would like to create a query where I will be able to see what is the
destination and price per minute based on the destination number in table 1.
How can I use a Dlookup/Vlookup or any formula to determine the matching
destination code in table 2 which is build in the beggining of my destination
number in Table1 so that I can price the durrations.

Awaiting for your replay. Thank you so much for the response again

Best regards
Ivan

 

[John Nurick]

String (text). In general, only use numeric fields for values that you
can do arithmetic with.

Thank you so much John
I am going to try it now. Please tell me what in what format is best to
extract these fields in from the server. Ie mumeric...

I really appreciate your help

Hi Ivan,

Use a query along these lines:
SELECT <field list>
FROM [Table 1] INNER JOIN [Table 2]
ON [Table 1].[Dest number] LIKE [Table 2].[Destination code] & '*'

Hi Dale

Thanks for the quick response
Here are some details for eg. purposes
Table 1
Dest number minutes
23456897888997 6
5647823875332 34
612349874443333 14

The table contains other fields but this is the one I have to link to the
destination table.

Table2

Destination code Destination Price per minute in$
6123 England 2
564 Peru 4
23456 Germany 2.5

I would like to link the two but the problems that I have are:
- the destination code integer lenghts are different as different countries
have different codes
I would like to create a query where I will be able to see what is the
destination and price per minute based on the destination number in table 1.
How can I use a Dlookup/Vlookup or any formula to determine the matching
destination code in table 2 which is build in the beggining of my destination
number in Table1 so that I can price the durrations.

Awaiting for your replay. Thank you so much for the response again

Best regards
Ivan

:

Ivan,

Can you give us a couple of examples of the records in the two tables for
clarification? It would also help if we knew the table structure for these
tables.

Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

I have a problem in creating a relationship for 2 tables.
Table1 - represents a call list with destination phone numbers(
international).
Table2 - is a destination codes table in which the destination code field is
made up of different integer lenghts due to different destinations. The
intgers differ in lenght as in different countries the country codes and
regional codes vary. thus I can not separate by Left function a fixed # of
integers from my destination number(table1) and link it to the destination
code (table 2) .
I have a problem with the Dlookup function. I am not able to link
destination number(Table1) to destination code in(table2). How can I specify
in a query that a variable string of integers(starting from the left) from
the destination numbers (table1)should match the longest possible integer in
the destination codes (table 2)
Thanks!