Hey guys/gals,

This is my forst post here, so I am trying to make it worth your time reading it...

I am currently working on a mapping project that uses MapPoint and Access. We are breaking down our contacts by county, and of course we have one table instead of a table for each county. What I want to do is create a macro that will allow me to create multiple tables from the county field in the single table that we have.

I started doing this with the Make Table Query, but after ten it got really tedious, and that still leaves me with 149 to go.

Any suggestions?

Patrick

Keep 1 table and have the countryID in the table and have a country table.

That is the correct way of setting up your DB.

It's following normalisation rules.

Welcome to Utter Access Discussion Forums, Patrick.

Just to second and expand a bit on what Danny said, Patrick, you should not store similar data in multiple tables. When you want to retrieve information about contacts in a particular county, use a query with the county you want as a selection criteria. Your database appears to be dealing with at least two entities ('things') and this implies that you should have two related tables (contacts, and counties.) The counties table would have one record for each county or approximately 159 records. Each contact record would have a field (usually called a foreign key) that would store the value of the primary key for the county to which the contact belongs. This structure assumes, of course that there is a one to many relationship between counties and contacts such that each contact relates to only one county and each county may relate to many contacts.

Dividing your data into multiple tables, one for each county will complicate retrieving data immensely because you will have to create one query for each of the counties and then have to choose the particulary query to run when you want to a see a particular county's contacts. And, what if you want to retrieve the contacts from multiple neighbouring counties or produce an overall list of contacts. Dividing your data as you have suggested will create far too much work in the future and make you data extremely difficult to manage and use.

Glenn

Thank you both,

The only problem with your suggestions is when I go to retrieve the data (while using MapPoint) I can only retrieve the entire table, not just the selected fields. That is why I need to have seperate tables. I will only be using it one county at a time, never together.

P.

Are you trying to import all the countries everytime or do you do 1 country one day and another country another day?

Can you retrieve queries instead of tables?

You could just run a query where the user is prompted for the country first.. or......

You could keep the data the way me and glenn suggested and create a make table that prompts you for the criteria of the country, then when you select the country it create the table with that data in to import. This of course would not be practical if you had to do them all everytime but... something that you could do is use the make table query idea I detailed and then run do.. loop code to run through the country table.

The only issue with this is you'll have all your data duplicated in your DB and so it will be twice the size :(

This is something that I will have to do weekly. We are tracking growth of numbers and where they are.

No, it does not retrieve queries.

Could you explain the loop code idea. Does that mean run the make table query once and it creates multiple tables?

I am not worried about the size. Have an 80 Gig HHD exculsively for this.

Thanks,

P.