Solved

URGENT!  Multiple Conditions but only one possible answer...Need Help!

Posted on 2006-06-11
13
299 Views
Last Modified: 2012-06-27
I have a project that I just can't figure how to handle and once again seek the advice of the "experts".  I have a database with two tables.  The first looks like the following:

street #     street name/type     city     zip     route wheel
10          ABBEY RD          FFLD     06824    
11          ABBEY RD          FFLD     06824    
21          ABBEY RD          FFLD     06824    
31          ABBEY RD          FFLD     06824    
41          ABBEY RD          FFLD     06824    
110          ABBEY RD          FFLD     06824    
50          ABIGAILS WY           FFLD    06824    
9          ADAMS RD          FFLD     06824    
17          ADAMS RD          FFLD     06824    
20          ADAMS RD          FFLD     06824    
31          ADAMS RD          FFLD     06824    
43          ADAMS RD          FFLD     06824    
219          BARLOW RD          FFLD     06824    
                   
Column 5 (route wheel) is where the formula is needed and this is why it is blank in this example.  

Table 2 contains the following information.

route #     Low     High     street name/type     city     zip
54600     1     100     ABBEY RD          FFLD     06824
52900     101     200     ABBEY RD          FFLD     06824
52650     1     150     ADAMS RD          FFLD     06824
54900     1     200     ADELAIDE ST          FFLD     06825
54800     1     600     ADLEY RD          FFLD     06825
53880     1     300     ALBERTA ST          FFLD     06825
52800     1     311     BARLOW RD          FFLD     06824
52810     312     800     BARLOW RD          FFLD     06824

What I need the formula or solution tolook at  address and decide what route should be assigned from the parameters  in table 2.  An example would be the first line in table 1.  It needs to find the correct road, city, zip and then make the determination that 10 Abbey Rd would fall between 1 and 100 all from comparing table 1 and 2 and then assign it to route 54600.  The address that is 110 ABBEY Rd would go on route 52900.  

I just can't figure out what the best and most efficient way to handle this task would be.  We have thousands of addresses and an equal amount of ranges.  Your URGENT help would be greatly appreciated.

Andy
0
Comment
Question by:spudmcc
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 6

Accepted Solution

by:
Ken Turner earned 150 total points
Comment Utility
Looking at the problem from an SQL (Oracle) viewpoint, if you had the following:

TABLE1.ZIP                = TABLE2.ZIP                AND
TABLE1.CITY              = TABLE2.CITY              AND
TABLE1.STREETNAME = TABLE2.STREETNAME AND
TABLE1.STREETNO BETWEEN TABLE2.LOW and TABLE2.HIGH

then your missing column would simply be TABLE2.ROUTENO.

The above could be achieved by linking the two tables on ZIP, CITY and STREETNAME, and then having in your record selection criteria:

{TABLE1.STREETNO} in {TABLE2.LOW} to {TABLE2.HIGH}

However a quick test would seem to indicate that the above line of code doesn't get translated into SQL, so if you're concerned about performance, then you may need to specify your entire SQL SELECT statement using the Add Command functionality (CR9.0 and later only).
0
 
LVL 9

Expert Comment

by:Outin
Comment Utility
Try this:

- Link the tables using street, zip and city.
- Add the folowing part to the selection criteria formula:
   
  and {table_1.street_number} in {table_2.low} to {table_1.high}

--
Outin
0
 

Author Comment

by:spudmcc
Comment Utility
I tried both ideas and had no success with either.  When I tried idea 1 from Ken it blanked out all of the other fields and I ended up with nothing.  When I tried the idea from OUTIN it did the exact same thing!  Maybe I am missing something!

A
0
 
LVL 9

Assisted Solution

by:Outin
Outin earned 150 total points
Comment Utility
Ok, some additional info needed then:

- Could you post the complete selection criteria there is when you implemented the offered suggestion?
- Is any data returned when you just link the tables using street, zip and city?
- Are {table_1.street_number}, {table_2.low} and {table_2.high} all number fields?

PS: Both offered suggestion are exactly the same....

--
Outin
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Does table 2 change very often?

I think from an efficiency standpoint Adding a route column to table 1 and doing a lookup when the data is entered would work better than trying to do all this processing in Crystal or the database each time the report is run.

To populate the initial table you could run an update query then change the data entry routine to do an update.  Perhaps using a trigger so that any time a record is updated and saved the route number is also updated.  Obviously this won't be very good if the route table (table 2) is in constant change.

mlmcc
0
 

Author Comment

by:spudmcc
Comment Utility
Table 2 doesn't change unless a route is added or deleted and that is not very often.  I am looking to have take the information from table 1 (specific address/zip/city), then find the appropriate route from table 2 (based on city, zip, street name and finally comparing the low and high value of the street number to find the route and  assign it to the "specific address" in table 1.    

I want it to do this for ALL the data in table 1 not just based on any search criteria.  

I hope this helps!

Andy
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 200 total points
Comment Utility
I think the best method would be to change table1 to include the route number.  Can you make changes to the database and the update routines for the database.

If you try to do this in Crystal I believe you will be very disappointed in the performance.

mlmcc
0
 

Author Comment

by:spudmcc
Comment Utility
Unfortunately not possible!  I can do this in excel but that requires lots of data manipulation and conversion.  Maybe your right in that Crystal may not be proper program to handle this task.  
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can you build a new table in the database?

Do you have the ability to build a database somewhere else and popukate it using an application?

mlmcc
0
 

Author Comment

by:spudmcc
Comment Utility
mlmcc

I did not get any answers that would help me resolve my issue.  I appreciate all the help but I guess I was
looking for something too complicated or not possible with Crystal.  

I leave the outcome of this question and awarding of points as you see fit.

Spudmcc--Andy
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Do you still need help?

mlmcc
0
 

Author Comment

by:spudmcc
Comment Utility
Yes, I certainly do!  
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now