Hefestus@gmail.com wrote in
news:1134162898.656791.51090@g44g2000cwa.googlegro ups.com:
[color=blue]
> 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
>[/color]
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.
