By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,584 Members | 1,774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,584 IT Pros & Developers. It's quick & easy.

New to access. stumped on query. Column to Row conversion Any ideas?

P: n/a
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

Dec 9 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.
Dec 10 '05 #2

P: n/a
If the Computer gets audited more than once in it's life you might not want
ComputerName to be the Primary Key unless you don't care about a history and
will be replacing the values.

You might also consider having two fields from this table instead of creating
a field for each original record in the funky table ...

ComputerName
AuditInfo

Actually I would think something like:

table = tblComputers with fields = ComputerName, CompLocation, CompPurchased,
etc.
table = tblAudits with fields = AuditID, ComputerName, AuditDate, AuditNotes,
etc.
table = tblAuditDetails with fields = AuditDetailID, AuditID, AuditInfo

A separate record for each audit info, would make sense if it is all text.
If the audit info reports would create different fileds in different
situations then this would make it easy to combine the info from different
computers - the fields would all be the same ...

Hope this helps,
Roger

He******@gmail.com wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200512/1
Dec 10 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.