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

[Widge]

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:

=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.

[NeoPa]

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:

=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.

[Widge]

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

Thanks, I will give this a go!

[NeoPa]

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

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time