Need to duplicate rows 1

[chrsab]

Hello

I am working on a query which extracts information from a couple of tables. But what i need is for when a certain word appears in a column, for that entire row (what contains that word) to duplicate onto the next row and then the same procedure to continue through all the results of the query.

Thank You for any assistance you can give.

 

[OnTheFly]

That is something you would have to do in a temp table, probably through a loop of some kind. A query simple returns information that is already in the tables, it does not have the ability to create records within itself.

Hope this helps.

OnTheFly

 

[Tranman]

chrsab,
You need to be careful about thinking of database tables as if they were spreadsheets. When you say that you want to duplicate a row "into the next row", that is not really possible. By definition, rows in a table have no order except what you assign to them when you fetch their data.

In Access, you can open a table in design view and open the properties sheet and assign an order to the table rows, but you are not really changing the order of the rows, just the order that Access delivers them up by default.

All that being said, you can probably accomplish what you want (if I'm understanding your problem correctly) by just running a simple insert query to duplicate the rows that contain the word(s) in question:

Insert into myTable select * from myTable where myField In("myWord1","myWord2","myWord3")

If the words you're concerned with are imbedded in Char(string) type fields, you will probably need to use the InStr function to find them, but that should not be a problem.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[chrsab]

That sounds very useful to know Tranman but i am receiving an error when i try to run the append back into the same table, but if i create a new table and run the query to insert into that, it works fine.

If this is the case, i can't use it as the duplicate data needs to be put into the original table.

 

[Tranman]

I can't imagine why that would not work...unless you are having a duplicate primary key problem. Is that the error you're getting?

If that is the case, you would need to revisit your plan of having exact duplicate rows in your table. I had imagined that you had no primary key defined if you were wanting to insert duplicate rows.

On the other hand, if you have a primary key, but it's an autonumber, and that's causing the error, you can get around that by enumerating the fields you want to insert in your sql statement.

Write back and let me know if that's the error you're getting.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[chrsab]

Excellent! That was the problem, as well as one of the columns was a yes/no. But i sorted that.

Thank you very much for your help.