URGENT! Multiple Conditions but only one possible answer...Need Help!
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
A
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
Do you have the ability to build a database somewhere else and popukate it using an application?
mlmcc
ASKER
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
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
Do you still need help?
mlmcc
mlmcc
ASKER
Yes, I certainly do!
- 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