s there such a plug in or piece of code that can handle the
following scenario?
1) I have a list of suppliers, goods they sell and addresses/postcodes
2) Customer comes along, says they need item X but must be from a
supplier within 7 miles of their location
3) I pump in their postcode and I get the result being the location of
all relevant suppliers within that radius who are able to stock item X*
*Item X will always have the same code between suppliers (we will be designating it an ID).
I have heard of someone who has something that can find the location
of suppliers within a radius set up in an Excel spreadsheet, but they
WON'T share it! So throwing myself on you guys for assistance. If it can be Excel-ised, I'm sure it can be Access-ed!
Trawled the net to no real avail. So relying on the experience of
others. Any info would be great really!
Thanks
Si
5 5420 maxamis4 295
Recognized Expert Contributor
The closest thing I have ever heard that can do this for you is a streets and trip reference library that you can add to access. I am not sure how familiar you are with code but if you purchase streets and trips and add the library reference to access, it will allow you to access the streets and trips mapping library. who ever has a spread sheet basically created a distance address library, similiar to map quest, which uses arrays to determine total distance or a radius.
other than that I would search google for distance measurement databases.
I did some conferring with a mate and he came up with this based on your advice. Needed:
1) ukpostcodes list tab (you'll have to find UK postcodes)
2) my suppliers list (with postcodes in column B)
3) tab called InsertCustomerP ostcode
You then stick a formula in the suppliers list at the end of your data: - =SQRT(((ABS(VLOOKUP(TRIM(LEFT(InsertCustomerPostcode!$B$3,LEN(InsertCustomerPostcode!$B$3)-3)),'uk-postcodes'!$A$2:$C$2822,2, FALSE) -VLOOKUP(TRIM(LEFT(Suppliers!B2,LEN(Suppliers!B2)-3)),'uk-postcodes'!$A$2:$C$2822,2,FALSE)))^2)+((ABS(VLOOKUP(TRIM(LEFT(InsertCustomerPostcode!$B$3,LEN(InsertCustomerPostcode!$B$3)-3)),'uk-postcodes'!$A$2:$C$2822,3,FALSE)-VLOOKUP(TRIM(LEFT(Suppliers!B2,LEN(Suppliers!B2)-3)),'uk-postcodes'!$A$2:$C$2822,3, FALSE)))^2))/1609
all you need to do then is stick in a postcode in InsertCustomerP ostcode B3 and then the lookups do their work.
Of course, now I need to find a way of getting this in Access and then restricting to x radius.
I now have this in a spreadsheet driven by a Userform. Is there any way of getting Access to interact with this at all?
Tried to think of a way to do what Excel is doing by using a query and Dlookups, but the complexity of it all caused a minor brain explosion.
Not entirely sure if what I was doing was possible. I was basically trying to get an amend query to perform the above calculation except with Access syntax like Dlookups and calculate distances when you input values into a form. You would have a textbox for inputting the postcode into, the query then uses the first part of the postcode to dlookup through the list of ukpostcodes, pulling off the location values etc... all what the above vlookups were doing basically. But so complicated to knock up in expression builder that I just couldn't get it to work.
So looking to see if I can find an easier way of doing things. Utilising something I know is working with my database (since I want to have everything running from one central point).
If you REALLY want to have a mess around with this, I can upload the UK Postcode file that I have and can give you a bit more of an indepth runthrough of what my setup is.
Hi, do you this program written? and along with uk post code database?
Hi sm8082. I doubt you will get an answer from an old thread (dating as it does from 2007). In any event, in the UK Royal Mail does not supply postcode databases for free. Although there are some royalty-free alternatives which try to provide similar data, including eastings and northings gathered on the ground by volunteers, there is simply no easy alternative to the data supplied commercially by Royal Mail (or by Ordnance Survey, which also uses Royal Mail data for the postcode and household information).
Unless posters use publically-available sources they would be in breach of copyright rules to publish UK postcode data sourced from Royal Mail or Ordnance Survey sources. We could not condone or assist in such illegal activities.
-Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bacci |
last post by:
I'm creating a zip code radius search. I have a general understand of how
this works, but am puzzled by how to get my final list of company locations.
I have setup two database tables: one with zip codes w/latitudes and
longitudes and the other with company names and addresses. The user enters
their zip code and the corresponding latitude and longitude is returned. I
then use this to return a list of zips, latitudes and longitudes within...
|
by: nortelsale |
last post by:
i have a site that allow people to post for sale, for rent info, I want
to have advanced search function that can search all the posting (in my
site) by zip code, and by certain miles say 10, 20 miles within a zip
code?
My site is written in PHP with a mySQL database.
|
by: Phil |
last post by:
Hi.
I have a table containing primary address, secondary address,
road,town, postcode, there are otherfields within the table requestid,
round, day, etc.
I want to find all instances of the address if it occurs > 2 in a
given period. I need a report that gives the address and other fields.
I have tried using the find duplicates wizard, i need to run a query
twice, once to pick up the primary address and street and postcode and
one to...
|
by: Steph |
last post by:
Hi.
I'm very new to MS Access and have been presented with an Access database
of contacts by my employer.
I am trying to redesign the main form of the database so that a button
entitled 'search' may be clicked on by the user and the user can then
search all records by postcode. I want to do this to prevent duplicate
data entry.
|
by: Gary T. |
last post by:
Another table called tbl_Customers has the following fields:
JobNo (primary key)
fkeyQuoteNo
SiteName
SiteAddr1
SiteAddr2
CompanyName
CompanyAddr1
CompanyAddr2
| |
by: momo |
last post by:
Can anyone help me? I am writing an ASP.NET app. and I need to be able to
have people search based on X radius of a zip code. Please help.
Thanks,
Momo
|
by: premgrps |
last post by:
Hi,
I have a database with two tables
a) A table of 2 million records with city, zip and associated
information (say XYZ) and
b) zipcode latitude, longitude table having >40,000 records/zip codes
PROBLEM:
I need to find the the XYZs within the the range of a certain zipcode.
This zipcode and radial range in miles is entered by the user (web
interface).
|
by: Wayneyh |
last post by:
Hello all
I am trying to create a search form. I have a table called tblContact which has the field for postcodes in it. I have another table called tblSales which i have created a query to link the tblContacts to. On the frmSales the Postcode is a combo box. I am using the code below to search for the postcodes. I can't get it to find a postcode via the combo box. Sorry if i am not being very clear as my coding is very basic. Please help,...
|
by: Wayneyh |
last post by:
Hello Everyone
I have tblSales Which has CustomerID, Address1, Address2, Town, County, Postcode, Phone, DateOfVisit Fields and Paid chkbox. I have frmNewSales which is linked to tblSales.
What i am trying to do is:- when a user enters a Postcode into the postcode field and tabs away from the field. I want the OnLostFocus event to search tblSales to find any records with that postcode and if the DateOfVisit is over 30 days and Paid is...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |