If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Transpose table
Suppose I have imported a table from
a log output with a sample like this: fluxcapacitor .80 widgit in stock flibertygidit 3.2 (blank record) fluxcapacitor 1.10 widgit ordered flibertygidit 2.3 (blank record) Fuxcapacitor 4.01 widgit pending flibertygidit .734 (blank record) -There are about a dozen repeating metrics from the log file separated by a blank record. I'm trying to build a table like this: Record# fluxcapacitor widgit flibertygidit 1 .80 in stock 3.2 2 1.1 ordered 2.3 3 4.01 pending 7.34 Is this possible in a series of queries? |
#2
|
|||
|
|||
Amazing I just posted this exact Question. If you get an answer to this will
you please e-mail the answer to me. Thanks Marty "J Shrimps, Jr." wrote: Suppose I have imported a table from a log output with a sample like this: fluxcapacitor .80 widgit in stock flibertygidit 3.2 (blank record) fluxcapacitor 1.10 widgit ordered flibertygidit 2.3 (blank record) Fuxcapacitor 4.01 widgit pending flibertygidit .734 (blank record) -There are about a dozen repeating metrics from the log file separated by a blank record. I'm trying to build a table like this: Record# fluxcapacitor widgit flibertygidit 1 .80 in stock 3.2 2 1.1 ordered 2.3 3 4.01 pending 7.34 Is this possible in a series of queries? |
#3
|
|||
|
|||
Actually, marty, your question appears to be the exact opposite of this.
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "marty" wrote in message ... Amazing I just posted this exact Question. If you get an answer to this will you please e-mail the answer to me. Thanks Marty "J Shrimps, Jr." wrote: Suppose I have imported a table from a log output with a sample like this: fluxcapacitor .80 widgit in stock flibertygidit 3.2 (blank record) fluxcapacitor 1.10 widgit ordered flibertygidit 2.3 (blank record) Fuxcapacitor 4.01 widgit pending flibertygidit .734 (blank record) -There are about a dozen repeating metrics from the log file separated by a blank record. I'm trying to build a table like this: Record# fluxcapacitor widgit flibertygidit 1 .80 in stock 3.2 2 1.1 ordered 2.3 3 4.01 pending 7.34 Is this possible in a series of queries? |
#4
|
|||
|
|||
There might be some third party programs available that could help you, but
it's also not that difficult to do using VBA. Something along the lines of the following untested air-code should work: Dim intFile As Integer Dim intFirstSpace As Integer Dim sngCurrCapacitor As Single Dim sngCurrFliberty As Single Dim strCurrEntry As String Dim strCurrWidgit As String Dim strBuffer As String Dim strFile As String Dim strSQL As String strFile = full path to file to be read intFile = FreeFile() Open strFile For Input As #intFile Do While EOF(intFile) = False Line Input #intFile, strBuffer ' Check if it's the blank line. ' If it is, you should have all of the values you ' need stored in the 3 Curr variables If Len(strBuffer) = 0 Then strSQL = "INSERT INTO MyTable " & _ "(fluxcapacitor, widgit, flibertygidit) " & _ "VALUES (" & sngCurrCapacitor & ", " & _ Chr$(34) & strCurrWidget & Chr$(34) & ", " & _ sngCurrFliberty & ")" CurrentDb.Execute, strSQL, dbFailOnError Else ' Determine the value at the start of the row intFirstSpace = InStr(strBuffer, " ") strCurrEntry = Left$(strBuffer, intFirstSpace - 1) Select Case strCurrEntry Case "fluxcapacitor" sngCurrCapacitor = CSng(Mid$(strBuffer, intFirstSpace)) Case "widgit" strCurrWidget = Trim$((Mid$(strBuffer, intFirstSpace))) Case "flibertygidit" sngCurrFliberty = CSng(Mid$(strBuffer, intFirstSpace)) End Select End If Loop This assumes that record number is an AutoNumber field. If not, you can keep track of what record you're currently reading, and add that to the SQL statement. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "J Shrimps, Jr." wrote in message ... Suppose I have imported a table from a log output with a sample like this: fluxcapacitor .80 widgit in stock flibertygidit 3.2 (blank record) fluxcapacitor 1.10 widgit ordered flibertygidit 2.3 (blank record) Fuxcapacitor 4.01 widgit pending flibertygidit .734 (blank record) -There are about a dozen repeating metrics from the log file separated by a blank record. I'm trying to build a table like this: Record# fluxcapacitor widgit flibertygidit 1 .80 in stock 3.2 2 1.1 ordered 2.3 3 4.01 pending 7.34 Is this possible in a series of queries? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 12:55 PM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 07:02 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
COMPARE THE TWO TABLES | Stefanie | General Discussion | 0 | June 4th, 2004 04:36 PM |