Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Matching field in one table with two fields in another 2

Status
Not open for further replies.

dscoiho

MIS
Sep 26, 2005
51
US
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
 
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
 
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.

[yinyang] Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 [yinyang]
 
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})

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top