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!

 

[Pat Hartman]

You are running an append query rather than an update query. Append queries add new rows (assuming there are no key violations) and update queries update existing rows. Since you want to update an existing row, you need an update query.

 

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

 

[Pat Hartman]

Since your relationship is many-to-many, you might want to look at the sample db that I posted:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40821&highlight=manytomany