Help! How to generate a new table from existing data 1

[dataplayer]

I have a table call tblUnits that has info of all the housing units, such as unit_IDs, numbers of bedrooms, living rooms, baths, kitchens, hallways, and other areas in each unit. Now, I need to generate another table from the unit info, a table that list all the areas as the records.

For example, in tblUnits:
Unit_ID#1, #Bedroom = 8, #LV=2,#bath=3
Unit_ID#2, #Bedroom = 5, #LV=1,#bath=2
Unit_ID#3, #Bedroom = 3, #LV=1,#bath=1

Now, the output table "tblAreaList" should be
Area_ID#1, Unit_ID#1, AreaName = U#1_Bed#1
Area_ID#2, Unit_ID#1, AreaName = U#1_Bed#2
...
Area_ID#8, Unit_ID#1, AreaName = U#1_Bed#8
Area_ID#9, Unit_ID#1, AreaName = U#1_LV#1
Area_ID#10, Unit_ID#1, AreaName = U#1_LV#2
...

Area_ID#14, Unit_ID#2, AreaName = U#2_Bed#1
...
Area_ID#26, Unit_ID#3, AreaName = U#3_bath#1.

Basically, it generates areas record from unit to unit. The output tblAreaList must be able to add a few more fields later for data entry. I am familiar with common calculation within a field (no need to explain this part). But I don't know how to generate records automatically based on calculation results. I know how to use make-table query, but it does not generate records.

Any hint or info would be highly appreciated. Thanks.

Frank

 

[barny2006]

the problem is that you are trying to generate multiple records for each record in the unit tbl. for example a row in the unit tbl has 3bd, 1LV, 2bth, etc... you have to insert multiple records in the area tbl. to do this, you need a .vbs script to read each unit record in a record set, and through a loop, create necessary area records for bd, lv, bth, etc...
that's basically the process.
if you have problem constracting code to access/creating tables in vbs, post back.

 

[dataplayer]

Thanks, Barry,

You are right about what I want to do (creating a list from a table). The problem is that I am a novice with VBS script. Could you show me just one example. I might be able to go from there myself. Thanks again.

Frank

 

[barny2006]

i will,
give me a few minutes.
i'll post it here in a little bit.

 

[barny2006]

ok, while i am doing the vbs, why don't you create these tables:
tblunits:
fields: unit_id, num_of_bed, num_of_lv, num_of_bath
unit_id is text, the rest are numbers.

tblarealist:
fields: unit_id, area_id, area_name (all are text).

 

[barny2006]

did u create the tables?

 

[barny2006]

now,
copy this code and paste it to notepad ans save it as xxx.vbs. change file access database location to where the database is:

Const adOpenStatic = 3
Const adLockOptimistic = 3
dim filedname, searchstring, tablename, dbname, commandstring, colname, fieldname
dim strText, x, y, z, a, b, xx
strtext = ""
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = c:\your_file_location\sample.mdb"
' <b> your file location goes in above line </b>
tablename = "tblunits"

commandstring = "select * from " & tablename & ";"

objRecordSet.Open commandstring, objConnection, adOpenStatic, adLockOptimistic


objRecordSet.MoveFirst

area_id_num = 0

do until objrecordset.eof

if objrecordset.eof <> true then
unit_id = objRecordset.Fields.Item("unit_id")
num_bed = objRecordset.Fields.Item("num_of_bed")
num_lv = objRecordset.Fields.Item("num_of_lv")
num_bath = objRecordset.Fields.Item("num_of_bath")




' ------------- these middle loop is performed for each unit ------------
for i = 1 to num_bed
area_id_num = area_id_num + 1
area_id = "area_id_" & area_id_num
area_name = unit_id & "_bed_" & i
msg = msg & unit_id & " " & area_id & " " & area_name & vbcrlf
next

for j = 1 to num_lv
area_id_num = area_id_num + 1
area_id = "area_id_" & area_id_num
area_name = unit_id & "_lv_" & j
msg = msg & unit_id & " " & area_id & " " & area_name & vbcrlf
next

for k = 1 to num_bath
area_id_num = area_id_num + 1
area_id = "area_id_" & area_id_num
area_name = unit_id & "_bath_" & k
msg = msg & unit_id & " " & area_id & " " & area_name & vbcrlf
next





objRecordset.MoveNext

end if

loop
msgbox msg

'************************************************
msgbox ("finished processing......... " & a & " records.")

objRecordSet.Close
objConnection.Close


run this and you will see the data displayed in a msg box.
we'll do the insert later.

 

[dataplayer]

Thanks a lot. I will use it right away. If any more question, I will come back. Thanks again.

Frank

 

[barny2006]

the insert is not in that code.
here's the code with insert.

Const adOpenStatic = 3
Const adLockOptimistic = 3
dim filedname, searchstring, tablename, dbname, commandstring, colname, fieldname
dim strText, x, y, z, a, b, xx
strtext = ""
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = c:\your_db_path\sample.mdb"

tablename = "tblunits"
tablename2 = "tblarealist"
commandstring = "select * from " & tablename & ";"

objRecordSet.Open commandstring, objConnection, adOpenStatic, adLockOptimistic


objRecordSet.MoveFirst

area_id_num = 0

do until objrecordset.eof

if objrecordset.eof <> true then
unit_id = objRecordset.Fields.Item("unit_id")
num_bed = objRecordset.Fields.Item("num_of_bed")
num_lv = objRecordset.Fields.Item("num_of_lv")
num_bath = objRecordset.Fields.Item("num_of_bath")




' ------------- these middle loop is performed for each unit ------------
for i = 1 to num_bed
area_id_num = area_id_num + 1
area_id = "area_id_" & area_id_num
area_name = unit_id & "_bed_" & i

commandstring = "INSERT INTO " & tablename2 & " " & _
"(unit_id, area_id, area_name) VALUES (" & _
chr(39)& unit_id & chr(39) & ", " & _
chr(39)& area_id & chr(39) & ", " & _
chr(39)& area_name & chr(39) & ");"


objConnection.Execute commandstring
next

for j = 1 to num_lv
area_id_num = area_id_num + 1
area_id = "area_id_" & area_id_num
area_name = unit_id & "_lv_" & j
msg = msg & unit_id & " " & area_id & " " & area_name & vbcrlf
commandstring = "INSERT INTO " & tablename2 & " " & _
"(unit_id, area_id, area_name) VALUES (" & _
chr(39)& unit_id & chr(39) & ", " & _
chr(39)& area_id & chr(39) & ", " & _
chr(39)& area_name & chr(39) & ");"


objConnection.Execute commandstring
next

for k = 1 to num_bath
area_id_num = area_id_num + 1
area_id = "area_id_" & area_id_num
area_name = unit_id & "_bath_" & k
msg = msg & unit_id & " " & area_id & " " & area_name & vbcrlf
commandstring = "INSERT INTO " & tablename2 & " " & _
"(unit_id, area_id, area_name) VALUES (" & _
chr(39)& unit_id & chr(39) & ", " & _
chr(39)& area_id & chr(39) & ", " & _
chr(39)& area_name & chr(39) & ");"


objConnection.Execute commandstring
next

objRecordset.MoveNext

end if

loop
msgbox msg

'************************************************
msgbox ("finished processing......... " & a & " records.")

objRecordSet.Close
objConnection.Close