Is it the outcode that connects the customer to the store?
If so, looking at your data, I think we can join the tables instead of blending - then the calcs will be much easier.
Thanks for responding so quickly.
The outcode can not be connected between stores and customers.
A customer may be very close to a store but have a completely different outcode.
The problem I have is that there isn't a field to join/blend.
So you don't know which store the customer visited?
One option is to draw concentric circles at a distance around your stores (but that doesn't help if they actually went elsewhere)
You say you had some success by combining the two tables using csv files but you can't achieve this using the tables in the database... Can you explain what you did with the csvs and why you can't do the same in the DB? Any joining you've done with the csvs should be possible in the db too
There is one salient fact that I forgot to mention.
The customers are loyalty customers and may have only just enrolled as new members of the scheme.
They may not have even purchased anything from a store!
Therefore all I have is a table of stores and a table of customers, with post codes.
I want to see where the customers live in relationship to their nearest store.
I created a CSV file that contained both customers and stores and I used a Dimension field called "Type"
i.e. "Store" or "Customer".
The attached Workbook (22/1/13) shows what I achieved.
The map looks great but shows all stores and all customers.
But I can't achieve this using my database.
I think that I have now overcome the issue.
I have joined the Stores and Customers by post-code but have used:
store.post-code <> customer.post-code
I am working on the premise that a customer will not live very close to a store and so have
the same post code.
This means that each store will be joined to every customer - exactly what I need.
I then select a store (simple filter on store) and then calculate the distance to the selected store.
I can then use a parameter to view all customers within a certain radius.
The only thing I can't do at the moment is to plot the position of the selected store.