Solved

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

Posted on 2006-06-11
Medium Priority
339 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
Question by:spudmcc
• 5
• 4
• 2
• +1
12 Comments

LVL 6

Accepted Solution

Ken Turner earned 450 total points
ID: 16883472
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

ID: 16883483
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

ID: 16886133
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

Outin earned 450 total points
ID: 16892020
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 101

Expert Comment

ID: 16917521
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

ID: 16919418
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

LVL 101

Assisted Solution

mlmcc earned 600 total points
ID: 16925083
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

ID: 16926297
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 101

Expert Comment

ID: 16926769
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

ID: 17115807
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 101

Expert Comment

ID: 17115813
Do you still need help?

mlmcc
0

Author Comment

ID: 17115828
Yes, I certainly do!
0

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

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

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