Creating duplicate rows based upon cell criteria

[Garling_Beard]

I am working with some data that needs to create duplicate rows if a cell has any number greater then 1. Here is an example; I have a column named 'Number of Rooms', if the entry is 3, I need the specific columns duplicate 3 times in new rows under the existing row.

Number of Rooms   Hotel   User   Check In    Check Out   Date Entered
3                 Hilton  Tom    10/10/10    10/12/10    6/5/10

I need it to show:

Number of Rooms   Hotel   User   Check In    Check Out   Date Entered
3                 Hilton  Tom      10/10/10    10/12/10    6/5/10
3                 Hilton           10/10/10    10/12/10    
3                 Hilton           10/10/10    10/12/10

I have a VBA Macro in Excel that will copy the rows and add rows with all info after the 'Number of Rooms' column, but I would like to try and do this in Access.

 

[Poppa Smurf]

Here is some code that will add additional records where the hotel rooms are greater than 1

' *************************************************************
' this module will perform the following
' 1. Read the source table into an array where
' column 0 - number of rooms
' column 1 - hotel
' column 2 - user
' column 3 - check_in
' column 4 - check_out
' column 5 - date_entered
' 2. Read each row in the array and APPEND new records where
' the number of rooms is greater than 1
' if the number of rooms is 3 then 2 records are appended for that hotel
' *************************************************************
Sub add_rooms()
Dim db As DAO.Database
Dim rst_input As DAO.Recordset
Dim counter As Integer
Dim rooms_array() As Variant
Dim row_cnt As Integer
Dim rooms_counter As Integer

Set db = CurrentDb()

'array used to assemble the figures
' change the value 20000 in the next line to more than the number of records
ReDim rooms_array(20000, 10)


Set rst_input = db.OpenRecordset("tbl_rooms_old", dbOpenDynaset)

row_cnt = 0
With rst_input
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF

rooms_array(row_cnt, 0) = ![no_of_rooms]
rooms_array(row_cnt, 1) = ![hotel_name]
rooms_array(row_cnt, 2) = ![hotel_user]
rooms_array(row_cnt, 3) = ![check_in]
rooms_array(row_cnt, 4) = ![check_out]
rooms_array(row_cnt, 5) = ![date_entered]
rooms_array(row_cnt, 6) = ![room_ref]

.MoveNext
row_cnt = row_cnt + 1
Loop
End If ' record count
End With

'create table from assembled array

counter = 0
Do Until counter > row_cnt - 1 ' deduct 1 from row_cnt as the above code has added 1.
' This will prevent a blank record added as a last record

If rooms_array(counter, 0) > 1 Then

rooms_counter = 2 ' 1st record already added
Debug.Print rooms_array(counter, 0)
Do Until rooms_counter > rooms_array(counter, 0)

rst_input.AddNew

rst_input("no_of_rooms") = rooms_array(counter, 0)
rst_input("hotel_name") = rooms_array(counter, 1)
rst_input("hotel_user") = rooms_array(counter, 2)
rst_input("check_in") = rooms_array(counter, 3)
rst_input("check_out") = rooms_array(counter, 4)
rst_input("date_entered") = rooms_array(counter, 5)
rst_input("room_ref") = rooms_array(counter, 6)

rst_input.Update
rooms_counter = rooms_counter + 1
Loop
End If ' number of rooms

counter = counter + 1
Loop
End Sub

 

[Garling_Beard]

Thanks, so far its working like a charm.

 

[DJkarl]

Just curious, why is this needed? This is not the way you normally want to use Access, storing the same information more than once is a formula for trouble later.

 

[Garling_Beard]

We have a web form that a user enters info, that is exported out and used internally. We needed a way to create duplicate entries for each number of hotel rooms needed. I know this is not the best way to do this, but my employer wanted to use what they already have in place, so Access and Excel will work. I have suggested that we store the info on a SQL Server that is attached to the web form and have a process to create the info we need.

 

[DJkarl]

Fair enough, having to integrate with a system outside of your control is something I'm all too familiar with

 

[Garling_Beard]

Ok it is working as described but I have added an additional column called room #. What I need now is when there are more then one room, I need each duplicate record. To add 1 to the room # field. For example, 3 rooms, room 1 = 1, room 2 = 2, room 3 = 3.

 

[Poppa Smurf]

Here is the updated code, the Lines in BOLD are the new lines of code. or changed


' *************************************************************
' this module will perform the following
' 1. Read the source table into an array where
' column 0 - number of rooms
' column 1 - hotel
' column 2 - user
' column 3 - check_in
' column 4 - check_out
' column 5 - date_entered
' 2. Read each row in the array and APPEND new records where
' the number of rooms is greater than 1
' if the number of rooms is 3 then 2 records for rooms 1 and 2 are appended for that hotel
' *************************************************************
Sub add_rooms()
Dim db As DAO.Database
Dim rst_input As DAO.Recordset
Dim counter As Integer
Dim rooms_array() As Variant
Dim row_cnt As Integer
Dim rooms_counter As Integer
Dim new_rooms As Integer

Set db = CurrentDb()

'array used to assemble the figures
' change the value 20000 in the next line to more than the number of records
ReDim rooms_array(20000, 10)


Set rst_input = db.OpenRecordset("tbl_rooms_old", dbOpenDynaset)

row_cnt = 0
With rst_input
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF

rooms_array(row_cnt, 0) = ![no_of_rooms]
rooms_array(row_cnt, 1) = ![hotel_name]
rooms_array(row_cnt, 2) = ![hotel_user]
rooms_array(row_cnt, 3) = ![check_in]
rooms_array(row_cnt, 4) = ![check_out]
rooms_array(row_cnt, 5) = ![date_entered]
rooms_array(row_cnt, 6) = ![room_ref]

.MoveNext
row_cnt = row_cnt + 1
Loop
End If ' record count
End With

'create table from assembled array

counter = 0
Do Until counter > row_cnt - 1 ' deduct 1 from row_cnt as the above code has added 1.
' This will prevent a blank record added as a last record
new_rooms = 1 ' reset to 1 so that rooms 1 to no_of_rooms -1 are added
If rooms_array(counter, 0) > 1 Then

rooms_counter = 2 ' 1st record already added

Do Until rooms_counter > rooms_array(counter, 0)

rst_input.AddNew

rst_input("no_of_rooms") = new_rooms
rst_input("hotel_name") = rooms_array(counter, 1)
rst_input("hotel_user") = rooms_array(counter, 2)
rst_input("check_in") = rooms_array(counter, 3)
rst_input("check_out") = rooms_array(counter, 4)
rst_input("date_entered") = rooms_array(counter, 5)
rst_input("room_ref") = rooms_array(counter, 6)

rst_input.Update
rooms_counter = rooms_counter + 1
new_rooms = new_rooms + 1
Loop
End If ' number of rooms

counter = counter + 1
Loop
End Sub