DLookup between 2 columns

[KenshiroUK]

Hi all I have 2 tables, one called Addresses and Extended Areas.

Within my imported orders I have addresses with postcodes and countries. My Extended Areas table has Country, zip_low, zip_high and extended area with a Y.

I wish to check certain postcodes from a particular country between zip_low and zip_high, which gives Y if it is indeed a Extended area.

 

[KenshiroUK]

So when query is ran if Argentina postcode 3008 is equal to or more than zip low, but equal to but is less than zip_high it gives me a Y

 

[plog]

You should create a function in a module that takes a zip and returns what you want. But you haven't given me enough information. Let's use this as your table's data:

ExtendedArea
Country, zip_low, zip_high, ExtendedArea
Argentina, 3007, 3100, Y
Columbia, 3098, 3900, Y
Peru, 5012, 5111, Y

1. Would you use Country, zip_high and zip_low to determine what the result would be? Or would you just use zip_high and zip_low?

2. In my example data, Argentina and Columbia overlap (3098, 3099, 3100) is that possible in your data?

3. Suppose it doesn't fall between zip_high and zip_low, what should be returned in that case?

 

[KenshiroUK]

Hi, thanks for getting back to me

You should create a function in a module that takes a zip and returns what you want. But you haven't given me enough information. Let's use this as your table's data:

ExtendedArea
Country, zip_low, zip_high, ExtendedArea
Argentina, 3007, 3100, Y
Columbia, 3098, 3900, Y
Peru, 5012, 5111, Y

1. Would you use Country, zip_high and zip_low to determine what the result would be? Or would you just use zip_high and zip_low?

I would use zip_high and zip_low to determine what the result would be, as long as the post code and country within my orders table is matched to my Extended Area table. So the code has to be equal to both, or greater than, less than.



2. In my example data, Argentina and Columbia overlap (3098, 3099, 3100) is that possible in your data?

No this shouldn't over lap because I can link the country within my orders to country within Extended.

3. Suppose it doesn't fall between zip_high and zip_low, what should be returned in that case?

And this part doesn't matter tbh, its just the ones that fall within that zip_high, zip_low I'm interested in.

 

[KenshiroUK]

I've attached a copy as well. Since I've been puzzled over how to write this query.

 

[plog]

Create a query based on Imported_Orders and paste this code into a field in it:

ExtnededArea: DLookUp("[extended]","tblExtended_Areas","zip_low<='" & [ship-postal-code] & "' AND zip_high>='" & [ship-postal-code] & "' AND country='" & [ship-country] & "'")


You should add a record in Imported_Orders that doesn't have a match in tblExtended_Areas just to make sure it works correctly.

 

[KenshiroUK]

Hi, thank you for the code. This has partly worked as I matched country to country, however it has returned everything single line of Argentina, duplicated over and over again. It could be partly due to the duplicate country names, or codes?

I've just tried 'Group By' and its removed duplicates, not sure if thats the correct way but it seems to have worked.

 

[plog]

This has partly worked as I matched country to country

Matched? There's no matching going on. Build your query based off of Imported_Orders and that's it.

 

[KenshiroUK]

Hahaha thank-you! It worked perfectly. Now just a quick question is there a way to reverse it so, when a run both intended queries, one gives me a list of extended like we've got and the other gives me a list without?

I also notice the DLookup is running very very slow.

 

[plog]

Yes, Dlookup will be slow--it has to look through every record of ExtendedArea for every record of Imported_Orders.

If you want a list of those with and one without, then you should use that query as a query in 2 other queries. One would have criteria of "Y" in the ExtendedArea field you created, the other would be Null in that field.

 

[KenshiroUK]

I see that has worked! How would I get around this duplicate countries, but with different zip_low, zip_high? I mean I have a list with Argentina for example quite a few times with a list of the different postcodes in zip_low, zip_high.

 

[plog]

I don't understand the issue. Upload a sample database or post data to help explain

 

[KenshiroUK]

Okay, because this is really bizarre. As you will see, Query1 works fine, but if you have a look at the second query you'll see what I mean.

 

[plog]

Why do you have those 2 other tables in your queries? What do you think they are doing for you?

 

[KenshiroUK]

I'll removed those links then, but as your can see it gives you a lot of those countries, but if you click on the extended field within the query and click A-Z , you'll notice many of those disappears!?

 

[plog]

I still don't see what you mean. Repost what you have, and what you expect to have.

 

[KenshiroUK]

Ah nevermind it seems to have worked okay! Thanks for that.

 

[KenshiroUK]

Thank you for code yesterday, however it seems to have completely slowed my database down. Is there an alternative method?

 

[plog]

Not that i know of with the data you have. Because you have ranges, you have to look through every record and can't link the tables.

The only solution I know of is to get rid of ranges and have specific codes listed. Meaning, instead of this:

Counry, zip_high, zip_low
Argentina, 3008, 3002

You would have this:

Country, zip
Argentina, 3002
Argentina, 3003
Argentina, 3004
...

With data like that you could simply join the tables to determine if a zip was Extended or not. With ranges, you have to use a method like I provided.