Insert row at specific location within table

[tdrBates]

I need to insert a row below the row that has fields A thru E 004-01, SOUTHBOUND, 18, CHIC, MLK with the same fields except that field C should be 4 instead of 18. This row occurs several times throughout the table and I want to insert a row after each occurrence.

Any suggestions on how I might do this?
I'm using MS Access 2000.

A B C D E
004-01 NORTHBOUND 18 6TH BRUSH
004-01 NORTHBOUND 18 SANJ 11TH
004-01 NORTHBOUND 18 CHIC MLK
004-01 NORTHBOUND 18 SPRI MLK
004-01 NORTHBOUND 18 TECH CENTR
004-01 SOUTHBOUND 18 TECH CENTR
004-01 SOUTHBOUND 18 SPRI MLK
004-01 SOUTHBOUND 18 CHIC MLK
004-01 SOUTHBOUND 4 SANJ 11TH
004-01 SOUTHBOUND 4 IH35 E7TH
004-01 SOUTHBOUND 4 PVAL E7TH
004-01 SOUTHBOUND 4 VARG H183
004-01 SOUTHBOUND 4 ACC RIVE
004-01 NORTHBOUND 4 ACC RIVE
004-01 NORTHBOUND 4 VARG H183
004-01 NORTHBOUND 4 7TH PVAL
004-01 NORTHBOUND 18 6TH BRUSH
004-01 NORTHBOUND 18 SANJ 11TH
004-01 NORTHBOUND 18 CHIC MLK
004-01 NORTHBOUND 18 SPRI MLK
004-01 NORTHBOUND 18 TECH CENTR
004-01 SOUTHBOUND 18 TECH CENTR
004-01 SOUTHBOUND 18 SPRI MLK
004-01 SOUTHBOUND 18 CHIC MLK
004-01 SOUTHBOUND 4 SANJ 11TH
004-01 SOUTHBOUND 4 IH35 E7TH
004-01 SOUTHBOUND 4 PVAL E7TH

 

[traingamer]

You can't "insert a row below a row". Rows are added to the end of the table (but you can sort them however you want).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[tdrBates]

Suppose the last row of this table occurs 75 times
within the table with a different time for each row.

How could I insert 75 matching rows with a 4 in Field D
instead of 18?

A B C D E F
004-01 6:52 NORTHBOUND 18 6TH BRUSH
004-01 7:00 NORTHBOUND 18 SANJ 11TH
004-01 7:10 NORTHBOUND 18 CHIC MLK
004-01 7:17 NORTHBOUND 18 SPRI MLK
004-01 7:25 NORTHBOUND 18 TECH CENTR
004-01 7:33 SOUTHBOUND 18 TECH CENTR
004-01 7:41 SOUTHBOUND 18 SPRI MLK
004-01 7:50 SOUTHBOUND 18 CHIC MLK

 

[traingamer]

One way (tested) - make an append query:

INSERT INTO tblTrans ( a, b, c, d, e, f )
SELECT tblTrans.a, tblTrans.b, tblTrans.c, "4", tblTrans.e, tblTrans.f
FROM tblTrans
WHERE (((tblTrans.a)="004-01") AND ((tblTrans.b)="7:50") AND ((tblTrans.c)="SOUTHBOUND") AND ((tblTrans.d)="18") AND ((tblTrans.e)="CHIC") AND ((tblTrans.f)="MLK"));

My example file has all text fields. If your field d is numeric, remove the quotes from the "4" and the "18".
This query will append as many rows as meet the selection criteria.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[MichaelRed]

You should back up a step of two. Relational database operations do not lent them selves to the process which you are describing. I suggest you visit your local bookstore and peruse some of the tomes you can find in the computer section on MS Access -or any relational database. There are numerous (actually 12 "Classic") 'rules' of relational database concepts, several of which would be violated in constructing "A TABLE" like you describe.





MichaelRed