He******@gmail.com wrote in
news:11*********************@g44g2000cwa.googlegro ups.com:
Hey everyone,
I have never really used access before and i now find myself
knee deep
in it trying to solve a problem. Hopefully someone has an
idea. Anything would be great. Here's the problem:
I am working on a network invrntory solution for my
department.
We're using a great little utility to audit each machine on
the network, storing all kinds of information about hardware
and software in an access database on one of the public
network drives. The program formats the table in a slightly
funky way. One column contains the primary key (just a number
corresponding to each unique bit of inormation which has been
audited) and the second coulmn contains the actual audit
information (for example text describing the amount of memory,
the version of windows etc.)
I have been trying to find a way to convert the entries of
the
second colum into a single record (new table), using the first
entry of the second colum (computer name) as the primary key.
I have yet to find a way to do this, but i'm sure it can be
done. My SQL skills are limited to say the least. Any ideas
would be great. Thanks!
Sam
Easily done in Visual Basic using a recordset, but a PITA using
SQL.
First step is to get a list of all possible field names
Use this to create the new table. (note: code not debugged may
contain typos)
Dim rs1 as recordset
dim strSQL as string.
set rs1 = currentdb.openrecordset("SELECT DISTINCT column2 from
Funkytable;")
do until rs1.EOF
strSQL = (strSQL + ", ") & rs1!column2 & " Char"
rs1.movenext
LOOP
strSQL = "CREATE TABLE NewName (" & StrSQL & ");"
currentdb.execute strSQL
rs1.close
then walk the original reading each column2 and column3, writing
the column3 value to the column2
Dim rs1 as recordset
dim strSQL as string.
dim strFields as string
dim strValues as string.
set rs1 = currentdb.openrecordset("SELECT * from Funkytable
ORDER BY [primary key];")
do until rs1.EOF
strfields = (strfields + ", ") & rs1.column2
strValues = (strValues + ", """) & rs1.column3 & """"
rs1.movenext
if rs1.EOF then Exit DO 'goto LAST_1
if rs1.column2 = "Computer Name" then
strSQL = "INSERT INTO newtable (" _
& strfields & ") VALUES (" _
& strValues & ");"
currentdb.execute strSQL
strfields = ""
strValues = ""
endif
loop
Last_1:
strSQL = "INSERT into newtable fields (" _
& strfields & ") VALUES (" _
& strValues & ");"
currentdb.execute strsql
rs1.close
Work on a copy of your database until you are comfoirtable that
the code works.
--
Bob Quintal
PA is y I've altered my email address.
