Solved Create a new table using ..

[Number11]

So i dont think its going to be possible, i need to create a new data table that has a Y or N next to each postcode...

Short Postcode Delivery Availability
BN1 Y
BN3 Y
BN45 Y
BN17 N
BN18 N

and so on..

this is the only data source have

BN	BN1,3,45	4
BN	BN11,14-15	6
BN	BN12-13,16	14
BN	BN17-18	0
BN	BN2,9-10	6
BN	BN20-21,25-26	22
BN	BN22-24,27	22
BN	BN5,41-44	2
BN	BN6-8	3

 

[Minty]

You would have to build a custom function to extract the numbering, and the spacing seems inconsistent, which would make it more interesting.
Would you need 41-44 split out to 41,42,43,44 as well?

 

[Number11]

You would have to build a custom function to extract the numbering, and the spacing seems inconsistent, which would make it more interesting.
Would you need 41-44 split out to 41,42,43,44 as well?

Yeh i thinks its really a long shot

 

[arnelgp]

how do you determine which has Y and which has N?

 

[Gasman]

how do you determine which has Y and which has N?

I took it to be those with a number > 0 ?
Not sure why the BN2x are on different records?
@Number11 How do you get that data in the first place?

 

[Number11]

how do you determine which has Y and which has N?

if the number is greater than zero

 

[isladogs]

It may be easier to start again by obtaining a complete list of postcodes data, one postcode per record.
Do you only need Brighton (BN) postcodes or the whole of the UK?

 

[Number11]

It may be easier to start again by obtaining a complete list of postcodes data, one postcode per record.
Do you only need Brighton (BN) postcodes or the whole of the UK?

The whole of the UK

 

[arnelgp]

here is a query using VBA.
see FinalQuery.

 

[Number11]

here is a query using VBA.
see FinalQuery.

Wow you are a genius Loving It

 

[Number11]

@arnelgp

so its throwing up some incorrect data.. example is this postcode

BS	BS24-29, TA8-9

results here..
its not picking up TA8-9 but creating BS0 >

ID

BN

Expr1

Expr2

Expr3

Expr4

Expr5

Expr6

Expr7

Expr8

Expr9

Expr10

Expr11

Expr12

Expr13

Expr14

Expr15

Expr16

Expr17

Expr18

Expr19

Expr20

Expr21

Expr22

Expr23

Expr24

Expr25

VALUE

YN

1891

BS

BS24

BS25

BS26

BS27

BS28

BS29

BS0

BS1

BS2

BS3

BS4

BS5

BS6

BS7

BS8

BS9

10

Y

 

[Gasman]

Why on earth have you got TA8-9 on a BS record?

 

[Minty]

They might be in a grouped area.
BS being Bristol area and TA Taunton, it's possible they border each other?

 

[Number11]

They might be in a grouped area.
BS being Bristol area and TA Taunton, it's possible they border each other?

Yes thats correct the issue is that the query is not populating the TA postcodes TA8 & TA9 instead its created postcodes BS0-BS9 I think its because the Area is set as BS so the VBS is getting confused not sure If that can be corrected

 

[arnelgp]

here i change the code, and also Query1.
on data table, i added "BS24-29, TA8-9".

see query1 and finalQuery.

 

[Number11]

here i change the code, and also Query1.
on data table, i added "BS24-29, TA8-9".

see query1 and finalQuery.

Thank you yes thats sorted it all now

 

[Gasman]

Yes thats correct the issue is that the query is not populating the TA postcodes TA8 & TA9 instead its created postcodes BS0-BS9 I think its because the Area is set as BS so the VBS is getting confused not sure If that can be corrected

Sorry, I cannot fathom how a TA postcode should be in a BS record.
Swansea where I live is SA. I live in SA4. Pemboke which is miles away and has Carmarthenshire between Swansea and Pembrokeshire still has SA codes?
What is the next anomaly you are going to produce?

 

[Minty]

I suspect that the number count is for an area, that includes a set of postcodes, not necessarily in a single postcode area, but geographically grouped. as you can see below:

TA8 and TA9 border BS23-28, so that grouping makes sense geographically, well it does to me.