Auto insert record/rowset from another table

[tj007]

Hi

I hope someone can help me. I have an Access table name bs_loc.

This table has an autonumber name LOC_ID. The other fields are DESCRIPTION, MUNICIPALITY, AMENITIES, DATE1, HISTORY, ADA, DIST_INT, MAIN, CROSS, AND RW. All of these fields are populated.

I would like to insert records from this table to a specific location in the Result table. The fileds are the same in the Result table, but the insert will break a sequence in the Result table and that sequence must be renumbered/resequenced after the insertion.

We are keeping an inventory of bus stops in the order the stops occur. So, if there is a need to add, remove, or move a stop, the renumbering must happen. I would really appreciate guidance on this. Believe me I have tried and I am a bit discourage at the moment.

 

[TonyJollans]

Hi tj007,

Autonumbers provide a mechanism for generating a guaranteed unique value on a record and should only be used when you don't care about the actual value assigned; that doesn't seem to be the case here.

The data in your Results Table exist in order to begin with; what controls that order? How do you specify where a new record wants to go in that order? How big is your table? How often does it change? Without answers to these questions at least, it's difficult to know how best to proceed. It's probably best to completely refresh the Results table every time you make a change but, as I say, it rather depends.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[tj007]

Tony, I am moving data from a dbase application to Access. The only autonumber field is in the bs_loc table. This table produce the LOC_ID which is the unique bus stop location with this info DESCRIPTION, MUNICIPALITY, AMENITIES, DATE1, HISTORY, ADA, DIST_INT, MAIN, CROSS, AND RW.

There is another table name bs_stop. This table has a field name STOP (text field) which hold data in this format 01001 up to 01133. The first two digits are the route number (Route 1) and the last three digits are the stop number. So, 01001 is route 1 and stop 1.

There 40 bus routes with this maming convention and some routes share the same stops. Also, within this bs_stop table is a field name STOP_ID (number) with increments of ten starting at 10 and increasing by 10 for each STOP. I think this is where the renumbering is accomplished then passed to the Results table.

The results table has all of the bus stops and is ordered by the STOP field. A new record (Stop) is determined by its sequence with in the bus route and is simulated in the database by inserting the record in the proper row and renumbering the stops to indicate the correct STOP.

For example, a new STOP is added to route 1. Route 1 has 133 stops (01133). The new stop was added between stops 25 and 26 which make the new stop 26 and stop 26 is moved to stop 27. You see? This happens frequently The table size is 1,200kb. If you or any one else could develop a program that could automate this process. It is safe to say, you have created a nice niche in the transportation industry.

 

[tj007]

I can send you the tables if you would like to take a closer look.

 

[TonyJollans]

Hi tj007,

If I read you right, you have a Table (bs_loc) with all the details of each stop, and another (bs_stop) which is a cross-reference of route / stop sequence to location. The final Table (Results) is an amalgamation of the two with full details of each stop by route. If this is so, I would have "Results" as a Query instead of a Table, so that it automatically reflected updates in the other tables.

I'm a bit confused by the STOP_ID field - is it used for anything other than renumbering?

The fact that STOP is a text field makes life a little more awkward, but if I wanted to add a new stop between 25 and 26 on route 1 (on the bs_stop Table), this is what I would do ..

(a) renumber all stops after the point where the new is one is to be added. SQL for this would be something like ..

[blue][tt]UPDATE bs_stop
SET STOP = Format(Val([STOP])+1,"00000")
WHERE (Val([STOP]) Mod 1000) > 25;[/tt][/blue]

.. and ..

(b) add a new stop, with SQL like this ..

[blue][tt]INSERT INTO bs_stop (STOP, LOC_ID, other fields, ...)
VALUES ("01025", Loc_id, other fields, ...);[/tt][/blue]

After this you would need to rebuild the Results.

If I have misunderstood something, please come back and/or, if you like, send me a copy of the db to Tony at Jollans dot com. I have no doubt it can be automated and, from what I can see, it shouldn't be too difficult (of course I'm sure there are complications you haven't mentioned - there always are )

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[tj007]

Tony

Thanks for your reply. The STOP_ID field has no other reason as I can see. I will test the sql statements and let you know my findings. Again Thank you

 

[tj007]

The more I look at this data, The more I am inclined to work with just the Results table and the bs_loc table. I am thinking about just creating a unique LOC_ID in the bs_loc table when there is a need to add or move a STOP.

The bs_loc & Results table have the same LOC_ID, DESCRIPTION, MUNICIPALITY, AMENITIES, DATE1, HISTORY, ADA, DIST_INT, MAIN, CROSS, AND RW fields.

The update would be from the LOC_ID, DESCRIPTION, MUNICIPALITY, AMENITIES, DATE1, HISTORY, ADA, DIST_INT, MAIN, CROSS, AND RW fields in the bs_loc table to LOC_ID, DESCRIPTION, MUNICIPALITY, AMENITIES, DATE1, HISTORY, ADA, DIST_INT, MAIN, CROSS, AND RW fields in Results table.

The renumbering would occur on the Results table through a query. Does this make any sense. Can you help me?

I am getting data type mismatch when I run the update query.

 

[TonyJollans]

Hi tj007,

Slow down!! You're losing me here! One of us is getting the physical stops and the route/stop combinations mixed up. If I may summarise:
[ul]
[li]You have a table of stops with whatever information you hold about them, and each stop has a unique Loc_id (it doesn't matter whether it's an autonumber or not).[/li]
[li]You have a separate table of route/stop combinations (slightly confusingly called bs_stop) which holds route number, stop sequence, and stop loc_id. That is all that is relevant here.[/li]
[li]You also have a results table which is generated by combining the loc and stop tables and contains no other data.[/li][/ul]

When you occasionally add a new physical stop, you must add details of that stop to the bs_loc table and give that stop a unique loc_id. Before you can use a stop on a route it must be added to this table.

When you add a(n existing) stop to a route you need to renumber the stops in the bs_stop table for that route (after that you can regenerate the Results table - if it has to be a table). The process does not involve new stops so the question of new loc_ids doesn't arise in the process.

Please do correct me if I have got any of this wrong.

Now your Type Mismatch. You did say that STOP was a text field. Are you sure? And, if so, is it possible you have any non-numeric text values in the field? Or any nulls?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[tj007]

Tony, I know it is confusing. You have everything correct. The bs_stop table has the route (5 digits) which is called STOP. The route number has the route (first 2 digits) and stop sequence (last 3 digits)


The STOP field is a text field and it does have nulls. I will delete them.