Query to lookup a result.

[djkay2637]

Right OK. I hope I explain this well.:banghead:

I have a large table of customers that all have a postcode. I have 4 training centers in the country and i want a function that would suggest the closest center for them based on their postcode.

What i have done is created another table with the starting parts of all UK postcodes and then in the neighboring column i have manually written the closest center geographically. In excel i have created a suitable VLOOKUP function that selects the 2 leftmost characters and then looks those up in a separate table to return the correct value. All works great!

My question is, how can i do this in Access where my live data exists. I have created a query, put the expression in a column that returns to two left characters but then i am stuck. Getting it to do a DLOOKUP or equivilant just won't work Please see the example spreadsheet to see what I mean.

 

[isladogs]

Yes it will work.
Put your lookup data in a table
If using a form with the person's postcode, then use a DLookup function as follows

=DLookup("ReturningValue","tblLookupName","PostcodeStart='" & Left(Me.Postcode,2) & "'")

Alternatively set a string variable strPostcode equal to the postcode and put the above code in a query

 

[djkay2637]

Thank you for that. I am pretty sure i could do it within a form textbox but i an wanting to some some sorting and filtering too within a query. Is there a way to do this in a query expression? (using the GUI)

 

[isladogs]

Yes. Use the expression I supplied (but with a string variable) as a query field

 

[djkay2637]

OK. Ill give it a go. By "string variable" you you mean just insert it into the title header?

Thanks again.

 

[isladogs]

OK. Ill give it a go. By "string variable" you you mean just insert it into the title header?

What title header?

 

[djkay2637]

normally if i have created a calculated query field i would put the formula / expression in the column title. Is that what you mean by the string variable.

 

[djkay2637]

OK i have got something working now however, every record selected by the query is being repeated 184 times which is coincidentally how many different postcode parts are within the lookup table. Why might this be?

Thanks again.

 

[isladogs]

We'll its not a coincidence!
I would need to see your query SQL to know why for certain.

My guess is your query contains two tables and you haven't made a join between the tables. As a result you get every possible result.
If so, join the tables with a suitable field common to both tables.

If that's not the case, try setting the query properties to Unique values = yes.
If viewing the query SQL, this is the same as SELECT DISTINCT .....

 

[djkay2637]

Unique values seems to have done it! I will have to test it more extensively and i have included some more complex criteria but for now, thank you!

 

[isladogs]

Excellent. Good luck with the next stage of your project

 

[djkay2637]

All tested and it seems to work. However, I have another challenge...... I think id better create a new thread!