Address DB split into Multiple territories w/o duplicate

[Japn]

Hi Guys,
This is my first post so please excuse any Mistakes,

I had a list of 1800 full addresses including Postcode on excel, I added 2 columns for Co-ordinates then one column that I use to input data when a call needs removing. I then imported to access table and started to use queries to sort our sales into territories of hopefully no more than 26 calls each,


I used the spare column to input a R for removed then only list null entries in queries then I keep adding postcodes in the postcode criteria column until I get around 26 per Query,

My Ultimate goal is to Build multiple Tables of no more than 26 calls that I can geocode onto maps, to make it easier and more cost effective for our sales team.

My Problems are.

• How do I Prevent Duplicates when entering addresses if new address are entered, I Currently have access creating its own Key
• I need to be able to track & prevent duplicate postcodes being assigned to more than one query/Territory table.
• I need either Access to either Produce a Table Report (As in excel) or might be easier if I just import my data back to excel table.

I kind of think I need new tables for Maps with relationships created between Map table and Territory table. but not sure if this is the best way to go or not.

Any help appreciated ....

[NTC]

How do I Prevent Duplicates when entering addresses if new address are entered, I Currently have access creating its own Key
** technically this is easy; the field property in the Table has a No Duplicates selection choice; however in the real world it is more complex due to 'near duplicates' i.e XYZ Co. - XYZ Co - XYZ Inc - XYZ Inc. None of these are duplicates technically but they are all duplicates from the human perspective. So one does need some management and user training to insist that one first looks for an item before entering it.... In more controlled situations - such as Part Lists - the administrator enters it only - and users must use a combo/list box to select it and are not allowed to free form entry.

I need to be able to track & prevent duplicate postcodes being assigned to more than one query/Territory table.
** more or less the same answer as above

I need either Access to either Produce a Table Report (As in excel) or might be easier if I just import my data back to excel table.
** the layout of an Access report can easily be 'continuous' or having an appearance similar to excel; the 'data back to excel' would be referred to as an export, not an import, from the Access perspective

[Japn]

[QUOTE=NTC;264920]How do I Prevent Duplicates when entering addresses if new address are entered, I Currently have access creating its own Key
** technically this is easy; the field property in the Table has a No Duplicates selection choice;

Where is this No Duplicates selction choice I cannot find it in Access 2010 table properties, Please explain.

I need to be able to track & prevent duplicate postcodes being assigned to more than one query/Territory table.
** more or less the same answer as above

What I meant is that Is there a way using relationships between two tables that when I assign an address to a map in a query that I could prevent that postcode being reused in another query.

[orange]

You can assign Primary Keys to tables. Primary key can not be duplicated. The database software will not allow it.
Depending on your set up, you can create unique compound indexes on combinations of fields and these will not allow duplicates.

Your bigger task in my view is to define your territories such that they follow/adhere to postal code boundaries. This may not be as difficult as it could be if you can agree to street addresses within postal codes to be uniquely assigned to a territory.

Sort out the details of exactly what a territory means to your business.
Google maps or mapquest etc may be able to help you with your mapping needs.

japn -- you should probably work through this tutorial from RogersAccesLibrary. It will help you with the concepts of Normalization, table and relationships etc.

Good luck

[Japn]

japn -- you should probably work through this tutorial from RogersAccesLibrary. It will help you with the concepts of Normalization, table and relationships etc.

Good luck[/QUOTE]
Thank you so much what a greatly written document, I have read loads on creating relationships but this has to be the clearest I have read so far, and I think creating relationships with a map table & separate postcode table might just work.

I can define my territories by post code but as they are not sequential but I do this manually with an almanac which is not to bad as I will only have to do it once.
Thanks again I will keep updating this thread as I get it working.

[Japn]

Just wondering where is the Tutorials section located, specifically Rogers access Library???

[orange]

Glad you found the tutorial helpful.
See this http://www.rogersaccesslibrary.com/