Matching field in one table with two fields in another 2

[dscoiho]

Using CR10 and SQL Server db. I have a table that has City State Zipcode and I have another table that has State Zip_Start and Zip_End with an Identification Field. I am trying to match Table 1 using State and Zipcode and return the ID field from Table 2. My problem is that linking to stat is fine but if the zipcode falls in between the start and end I cant get it to come out correctly. The NY is an example of this:

Table 1
State Zip
MI 48009
NC 27612
NC 27150
IN 46240

Table 2
STATE ZIP_START ZIP_END IDField
GA D
NM B
NY 12000 14999 A
NY F
NY 10000 10499 A
OH 45000 45299 G
OK E
TX C

Any insight on how to resolve this would be great. Thanks

 

[lbass]

You could just link on state and then use a record selection formula like this:

(
isnull({table2.zipstart}) or
{table1.zipcode} in {table2.zip_start} to {table2.zip_end}
)

-LB

 

[Madawc]

A crude way to do it would be to link just by state. Group by state for each table 1 record. Find the minimum difference between the two zip codes, using a formula that always subtracts the smaller of the two values from the larger. Sort by that minimum - which should be OK since it is based just on values in that row. Suppress group header and details, just show the group footer.

There may well be a better way using JOIN, but I can't advise on that.

If you can write SQL, that would be a much better way to do it. Use SQL Commands to select just the records you want.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[dscoiho]

Thanks for all the insight on my problem. I will look more into the join factor due this being common request.

The ultimate problem I was having, for some reason couldnt think outside the box, is all fields were text and needed convert to number.

This what I ended up with:

@Match
if tonumber({table1.Zip}) = [tonumber({table2.ZIP_START}) to tonumber({table2.ZIP_END})]
then {table2.ID} else "A" ( for some reason there is a character or something that didnt allow me to use null all fields.)

@AllMatch
if isnull(Maximum ({@Match}, {table1.uniqueID})) or Maximum ({@Match}, {table1.uniqueID})= "A"
then {table2.ID} else Maximum ({@Match}, {table1.uniqueID})

 

[lbass]

I don't think you should be converting to a number, as some zips are like this: 02140, which would convert to 2140 as a number. Why do you think converting is necessary??? You can use "in" with "x" to "y" with strings, as in my post.

-LB