By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,571 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,571 IT Pros & Developers. It's quick & easy.

I'm trying to move this Excel spreadsheet to Access (complex!)

P: 56
I have made a spreadsheet that will calculate the distance from a set of addresses to a specified postcode in Excel. I'm looking to move this to Access so I can start thinking about hooking it up to a webpage. The main problem is the fact that I don't have a CLUE how to perform the lookup in Access. Basically we have 3 sheets. 1) where you enter your postcode and radius to search by, 2) a list of suppliers, their addresses and postcodes ... with a formula next to each address to calculate a) distance and b) whether it is within the radius, 3) a list of UK postcodes, their co-ordinates etc.

What happens is you put in the postcode and the formula updates the distance from postcodes column using:

Expand|Select|Wrap|Line Numbers
  1. =SQRT(((ABS(VLOOKUP(TRIM(LEFT(InsertCustomerPostcode!$B$3,LEN(InsertCustomerPostcode!$B$3)-3)),'uk-postcodes'!$A$2:$C$2831,2, FALSE) -VLOOKUP(TRIM(LEFT(Suppliers!B2774,LEN(Suppliers!B2774)-3)),'uk-postcodes'!$A$2:$C$2831,2,FALSE)))^2)+((ABS(VLOOKUP(TRIM(LEFT(InsertCustomerPostcode!$B$3,LEN(InsertCustomerPostcode!$B$3)-3)),'uk-postcodes'!$A$2:$C$2831,3,FALSE)-VLOOKUP(TRIM(LEFT(Suppliers!B2774,LEN(Suppliers!B2774)-3)),'uk-postcodes'!$A$2:$C$2831,3, FALSE)))^2))/1609
which is referring to the co-ordinates.

This is all driven by a form which then displays in a box, in order, what addresses are within the radius.

This is all fine in excel, but I haven't got a clue about transferring this to Access. I can imput all the data as tables but its the entire performing the distance search and displaying the results that I just can't fathom. I don't know where to start.

Can anyone point me in the right direction. And if you need any more info, just let me know.

Thanks.
Jun 11 '07 #1
Share this Question
Share on Google+
3 Replies

NeoPa
Expert Mod 15k+
P: 31,770
I have made a spreadsheet that will calculate the distance from a set of addresses to a specified postcode in Excel. I'm looking to move this to Access so I can start thinking about hooking it up to a webpage. The main problem is the fact that I don't have a CLUE how to perform the lookup in Access. Basically we have 3 sheets. 1) where you enter your postcode and radius to search by, 2) a list of suppliers, their addresses and postcodes ... with a formula next to each address to calculate a) distance and b) whether it is within the radius, 3) a list of UK postcodes, their co-ordinates etc.

What happens is you put in the postcode and the formula updates the distance from postcodes column using:

Expand|Select|Wrap|Line Numbers
  1. =SQRT(((ABS(VLOOKUP(TRIM(LEFT(InsertCustomerPostcode!$B$3,LEN(InsertCustomerPostcode!$B$3)-3)),'uk-postcodes'!$A$2:$C$2831,2, FALSE) -VLOOKUP(TRIM(LEFT(Suppliers!B2774,LEN(Suppliers!B2774)-3)),'uk-postcodes'!$A$2:$C$2831,2,FALSE)))^2)+((ABS(VLOOKUP(TRIM(LEFT(InsertCustomerPostcode!$B$3,LEN(InsertCustomerPostcode!$B$3)-3)),'uk-postcodes'!$A$2:$C$2831,3,FALSE)-VLOOKUP(TRIM(LEFT(Suppliers!B2774,LEN(Suppliers!B2774)-3)),'uk-postcodes'!$A$2:$C$2831,3, FALSE)))^2))/1609
which is referring to the co-ordinates.

This is all driven by a form which then displays in a box, in order, what addresses are within the radius.

This is all fine in excel, but I haven't got a clue about transferring this to Access. I can imput all the data as tables but its the entire performing the distance search and displaying the results that I just can't fathom. I don't know where to start.

Can anyone point me in the right direction. And if you need any more info, just let me know.

Thanks.
Start off by creating tables to match the VLookup data sheets. Each column in the table should be matched by a named field in the new table. The first column should be the Primary Key (or PK).
I presume, as you created the spreadsheet, you can reproduce the nuts and bolts of the formula later, it's just the VLookup bit that you need help with.
Access is actually a lot better suited to that sort of thing, but obviously as you are unfamiliar with it it would be harder for you.
Jun 11 '07 #2

P: 56
So split the formula down into each component and try and piece it together again?

Thanks, I will give this a go!
Jun 12 '07 #3

NeoPa
Expert Mod 15k+
P: 31,770
If you can show the MetaData of the new tables we can help with the lookup parts.
Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Jun 12 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.