Dlook up alternative?

[KenshiroUK]

Yesterday I kind fellow here gave me a piece of code for a dlook which has worked fine. However Since pasting it into a query, it has completely slowed my database down.

Is there an alternative for this?

I basically have 2 tables, one with customer details etc, a second table with duplicate country names, but with 2 fields zip_low, zip_high and another field with Y. All I want is when the country postcode is equal to/more than, or equal to/less than to give me a Y within a query.

 

[pr2-eugin]

Using of Domain functions in general by itself is not a sought after method.. Using them in Queries is even more worse.. Domain functions like DCount, DMax, DLookUp are all simplified queries by itself.. So Query inside a Query is not going to help you.. If you could provide the link to the code the kind person gave you we might be able to understand the scenario better..

 

[KenshiroUK]

Sure that would help!

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

 

[KenshiroUK]

Okay I believe an IIF statement would probably do it. If I can tell ship-postal code to look about tblExtendedAreas zip_low => And =< zip_high


IIf("[extended]","Y","zip_low>='" & [ship-postal-code] & "' AND zip_high<='" & [ship-postal-code] & "' AND country='" & [ship-country] & "'")

 

[pr2-eugin]

I am unable to get to the root of the problem.. Where (i.e. the link to the thread) is the discussion about this set up?

 

[CJ_London]

You could try a subquery which will look like this

ExtendedArea: (SELECT First([Extended]) FROM tblExtended_Areas AS Tmp WHERE zip_low<= mytable.[ship-postal-code] AND zip_high>= mytable.[ship-postal-code] AND country=mytable.[ship-country])

You'll need to replace mytable with the name of the table the relevant fields come from in your query i.e.

SELECT .... AS ExtendedArea FROM mytable

EDIT: Also, it will help if the relvant fields you are using in the criteria are indexed

 

[KenshiroUK]

I am unable to get to the root of the problem.. Where (i.e. the link to the thread) is the discussion about this set up?

http://www.access-programmers.co.uk/forums/showthread.php?t=252995

 

[KenshiroUK]

You could try a subquery which will look like this

ExtendedArea: (SELECT First([Extended]) FROM tblExtended_Areas AS Tmp WHERE zip_low<= mytable.[ship-postal-code] AND zip_high>= mytable.[ship-postal-code] AND country=mytable.[ship-country])

You'll need to replace mytable with the name of the table the relevant fields come from in your query i.e.

SELECT .... AS ExtendedArea FROM mytable

EDIT: Also, it will help if the relvant fields you are using in the criteria are indexed

Thats almost worked! Thank you. I'm having trouble because of the duplicate country names with different zip_low, zip_highs. it now returns the data as many rows as the country names within extended ares table.

For instance if I have United Kingdom 3 times within my tbl_extended area. I I will now get it within the query. I've posted 3 links to the images.

http://screencast.com/t/mPlIjFaC

http://screencast.com/t/aB49N6rDrlO

http://screencast.com/t/Wkr8Phdc1yo0

 

[CJ_London]

I think all you need to do is remove the tblExtendedArea from your main query - it is being referred to in the subquery

I can't see the code you have wrtten as I advised but it is important that you use Tmp (or any other name) within the subquery

tblExtended_Areas AS Tmp

 

[KenshiroUK]

Hahahah That worked cheers!!!

 

[CJ_London]

An alternative structure would be to leave your query as you have it with the 4 tables, but remove the join between countrycodes and tblextended_areas (not required) and change the join between importedorders to tblextended_areas to a left join (i.e. same as one to Imported_Orders_done)

Then remove the extendedArea column and drag down extended in its place finally drag down ship-postal-code, untick the show button and add a criteria for this field as follows:

Between nz([zip-low]) and nz([zip-high])

This should achieve the same result