Appending a Row Based on a an index

[bluemat]

I have a table. It is a directory of schools.

I took an existing database, and put it into access

I added a row. This row gives each town a number.

Now when I add more schools to this database. I would like that the ID number of the town is automatically apended.

For example - in my Table Manchester is number 25

When I add records. It may be one or several. I would like the ID number to be appended without me looking for it. SO for every entry from Manchester - the number 25 should be appended to the ID.

Can you help?

Thanks


Mathew

 

[FoxProProgrammer]

<I added a row. This row gives each town a number.>

Do you mean you added a column? This would be a field, not a record.

All you have to do is make the field for the ID of type AutoNumber. Access will automatically increment the ID when you add new records and prevent you from adding the same ID more than once.

 

[FoxProProgrammer]

Sorry, Mathew...I may have misunderstood what you are trying to do. Are you trying to concatenate a numeric value to the &quot;ID&quot; for each record in your table for that school? That's different from how I read it earlier.

 

[grnzbra]

I must assume that there is a table with town names and town numbers.

Create a query with the school table and the town name table, joined on the town name. take all the fields from the school table except the town number field. take that from the town table.

Use this to drive an update query to update the town number field of the school table.

for future entries, use a combo box on a form to look up the town numbers in the town table.

 

[bluemat]

HI guys. SOrry if I wasn't clear.

There is just one table.

1. I took the info from Excel tables available on an internet site

2. These lists are enormous. There are 140, 000 schools

3. There are 32 States and within those states there are several thousand 'sub areas' - ie towns and cities.

Simply I added a 'column' (sorry!!!! really, sorry to say row when I meant column)

Then for each different sub area I gave a number

So in sum, there is one table.

SO from your advice grnzbra, I would have to create a seperate table for towns

Then relate the tables and make a query with aan auto look up

so when a new town is entered, the ID is looked up....

This sounds good I will try it. I am sure I would have less problems if I working with less data....

Thanks grnzbra I will try and create the table and query relation you describe:_)

 

[grnzbra]

How about just using the zip code as the index? If you do that, I expect you could get a table from somewhere. What are you going to do with the index?

 

[bluemat]

NOt all entries have zip codes, less than 20%.

We will be creating a relational database and uoloading the info, so that people can search

Area/subArea/schoolType/school

Each area has an ID each sub area an ID and each school an ID

 

[grnzbra]

Well, if you need the index and don't have it, it'll have to be entered manually. If you do it in a separate table, at least you'll only enter each one once (for each town). Then use that to populate the school table.

 

[bluemat]

I started with zillions of entries and manually put the IDs using Excel

from 140 000 records I identified 3500 schools

This is done and fine, but if i add a list of schools or append it to the table, they will have all info except this ID number

I was looking for a way that access would append these

based on the name of the sub area. It frankly is difficult working with such a large database. I have never worked with so much info b4

It makes simple procedures significantly more difficult

Thanks for all your help by the way

 

[grnzbra]

Getting the number in there is easy enough if you already have in place something that relates the town and the index. If you are just looking for any old number for an index then it's really a piece of cake. Assuming you just want to assign a number to a town, create a table with an autonumber field and a text field. create a query based on the table you already have and select only the town name field. Change it to an append query to append to the name field of the two field table you just created and set the properties for unique records (so you have only one instance of each name ) and run the query. Now you have the town name and index in one table.

Create a make table query from the two tables (joined on the town name) which has all the fields of your original table EXCEPT the index field. Add that from the table created above. Now run the make table query. Rename your original table and rename this table to the name your original table had.

 

[bluemat]

Wow ...very interesting I will try it Thanks