Using split_code in an append query

[Jabell]

I don't know how I've managed to do it, but I've messed up my SELECT query which is used by an append query.

I have two queries. The first is a simple query that contains the Pk from my main table [prospects] and a split_code query to take the first one or two digits of a postcode.

code:
SELECT prospects.ID, Left([pcode],[no of digits]) AS split_code
FROM prospects;

my second table then takes the text digit(s) and appends them to a postcode/territory table using the first query.

code:
INSERT INTO territory_lookup ( id, territory )
SELECT postcode_splitter.ID, postcode_territory_map.territory
FROM postcode_splitter INNER JOIN postcode_territory_map ON postcode_splitter.split_code = postcode_territory_map.postcode
WHERE (((postcode_territory_map.territory) Between [first territory] And [last territory]));


I designed it so that I could take the first two letters of the postcode and asign them to a territory. I could then take the postcodes who only use one letter to replace the ones with a letter and a number (from the 2 digit split) only problem is, the one digit splitter doesn't overwrite the 2 digit split and so I get two records.

help!

 

[Jabell]

Ah well, you see that is what I want. I want to assign each company with a territory, but I have two territory's for each postcode, thus one postcode can have many territory's, one territory can have many postcodes, to solve the I designed a territory_lookup table to hold just the id of the company and the territory's they are in.

Hopefully the sample will help you understand, I'm almost there but I have spent the last two days trying to figure this out.

 

[Mile-O]

I want to assign each company with a territory, but I have two territory's for each postcode, thus one postcode can have many territory's, one territory can have many postcodes, to solve the I designed a territory_lookup table to hold just the id of the company and the territory's they are in.

In a situation such as this you need to create a junction table.