Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2005
    Posts
    5

    Unanswered: Question about Complex SQL Query in Access

    Hey everyone,

    I'm currently working on a network inventory project. We have a tool which takes a snapshot of a machine and exports the information to an access database. The problem is in how the table housing the newly created information is formated.
    Basicly, each item (OS version, Processor type etc.) is assigned a number. A second field named ItemValue1 contains the actual data (Windows XP, Intel Celeron etc). I have created a second table with the computer's asset tag as the primary key. I have setup individual fields for each item in this table. The problem i have been trying to solve is how to create an SQL query (INSERT) which uses multiple sub-select statements to extract the information in the
    itemvalue1 field and places that data in the respective fields of the second table? Succintly i need to convert the entries of a column into a single record. Any ideas? I'm using the latest version of access (not my choice). Any help is greatly appreciated.

    Thanks,

    Sam

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Possibly a crosstab query may be appropriate

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'll trade you a tranformation solution for your snapshot solution...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Nov 2005
    Posts
    5
    Sounds like a deal to me. We're using a freeware auditing tool called WinAudit, downloadable at the following address:

    http://www.pxserver.com/WinAudit.htm .

    It's a powerful tool which grabs more information than many other auditing tools. The audit itself can be saved in various formats (HTML, CSV etc), or directly exported to a database. The only thing that we don't like about it is how the program formats the tables of the database. I wrote to software's author and he explained why the table is formatted the way it is, but for our purposes we need to tweak it a bit. Please let me know if you can help us with the query.

    Thanks!

    Sam

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Looks neat. I'll dig into a bit later and see what you're after.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2005
    Posts
    5

    Bump!

    Bump!

    Anyone have any ideas?
    Thanks,

    Sam

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    My bad, I'll be getting around to this later in the week. I have to take a rather high-profile .net app from inception to beta by Friday.

    Oh, PM me on this, I'm curious about a few things...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Nov 2005
    Posts
    5

    bump!

    Bump! any ideas???

  9. #9
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I'm a newbie so there's no way I can help you, but I do know that Teddy has helped me out a lot! From my experience he's pretty reliable, I'm sure he'll get back to you when he can. Hang in there!

  10. #10
    Join Date
    Oct 2005
    Posts
    178

    Wink

    Quote Originally Posted by Hefestus
    Hey everyone,

    I'm currently working on a network inventory project. We have a tool which takes a snapshot of a machine and exports the information to an access database. The problem is in how the table housing the newly created information is formated.
    Basicly, each item (OS version, Processor type etc.) is assigned a number. A second field named ItemValue1 contains the actual data (Windows XP, Intel Celeron etc). I have created a second table with the computer's asset tag as the primary key. I have setup individual fields for each item in this table. The problem i have been trying to solve is how to create an SQL query (INSERT) which uses multiple sub-select statements to extract the information in the
    itemvalue1 field and places that data in the respective fields of the second table? Succintly i need to convert the entries of a column into a single record. Any ideas? I'm using the latest version of access (not my choice). Any help is greatly appreciated.

    Thanks,

    Sam
    Can you open the resulting file using notepad or MSWord?. Sound like the resulting file is in text format. Does it have a "csv" extension or "log"? I bet you that the words are tab-delimited too.
    If it is, you should be able to parse it and dump them into the corresponding fields that you created in ACCESS.
    UPload a sample of the WINAUdit file here in forum and I will dump it into an MS ACCESS format for you.
    Email me as soon as you have it for me.

    I just went to that website and downloaded the WInAudit and did it with my computer. I parsed and exproted the resulting data into MSAccess in a minute or two .
    See below
    Is this what you wanted to do export the WinAudit data to MS ACcess?
    Attached Thumbnails Attached Thumbnails kelby.bmp  
    Last edited by fredservillon; 12-11-05 at 13:43.

  11. #11
    Join Date
    Nov 2005
    Posts
    5

    Formatting the data

    Thanks for the posts! I'm able to export the same data from our machines into the database (as you posted in your screenshot). What i need to do is find a way to extract the entries of the IntemName and ItemValue1 column entireley. The ItemName colum entires would be used to create a new table with colums corresponding to the entries of the column. Computer name would be the primary key. After the table is created, i need to take all of the entries of the ItemValue1 column and ceate a new row in the second table. Thats why i asked for help building a query which converts columns to rows. I'll post some screenshots in a bit.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •