Converting Flat Table to Relational


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: Converting Flat Table to Relational

  1. #1
    Join Date
    Jun 2006
    Posts
    168

    Unhappy Converting Flat Table to Relational

    Hi,

    I have a big table, with a bunch of data in it. but they get pretty big being they are flat instead of relational.

    I am taking this data and dividing it into 3 tables. I have populated the first two tables with data from the 3rd.

    this is the structure of the 2 tables:
    Code:
    CREATE TABLE TI (
    	[TI_id] COUNTER,
    	[PN] TEXT(16),
    	[Start_Date] DATE,
    	[Start_Time] TIME,
    	[TT(min)] SINGLE,
    	[TB] TEXT(22),
    	[SN] TEXT(32),
    	[C1] TEXT(32),
    	[TestStand] TEXT(24),
    	[TP] TEXT(32),
    	[CalFile] TEXT(72),
    	PRIMARY KEY (PN,Start_Date,Start_Time,TestStand)
    );
    
    CREATE TABLE TS (
    	[TS_id] COUNTER,
    	[T(C)] SINGLE,
    	[TPG] TEXT(16),
    	[RFPath] TEXT(16),
    	[Mode] TEXT(16),
    	[RFMod] TEXT(16),
    	[F(MHz)] SINGLE,
    	[Vcc(V)] SINGLE,
    	[Vr(V)] SINGLE,
    	[DCVr(%)] SINGLE,
    	PRIMARY KEY ([T(C)],[TPG],[RFPath],[Mode],[RFMod],[F(MHz)],[Vcc(V)],[Vr(V)],[DCVr(%)])
    );

    now....in the 3rd table, what i want to do is whereever the fields line up for a unique TS_id or TI_id, to update the table with those unique values.

    after this is done, i will DROP all those fields that are redundant, to leave only the other fields that i need, and the TS_id and TI_id.

    thereby greatly reducing my overhead.

    (although it wont be as relational as it can be...but it's definitely a step).

    is there a good way to create a query to do this?

    record by record would take forever.

    fyi
    table TI has 30 rows
    table TS has 726 rows
    and data table still has like 347,000 rows, with all of the redundant TI and TS data included. (this is a small database, for testing. the real deal will be bigger)


    If you have any ideas, I'd be glad to hear them. I'm not very good with complicated SQL queries.

    Thanks!
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  2. #2
    Join Date
    Jun 2006
    Posts
    168
    I could loop through the records in the TI table, updating the data table TI_id where the fields are = with the unique id from the TI table.

    then the same thing w/ the TS table.

    but is there a better way?
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  3. #3
    Join Date
    Jun 2006
    Posts
    168
    I am revisiting this. Actually finally getting around to the real thing.

    here is my other thread in the VB6 forum for reference: http://forums.devx.com/showthread.php?t=161135

    I'm posting here again because I think this may be more database related than VB related.
    I will copy my last couple of posts to make this easier.

    Quote Originally Posted by chupacabra
    I think I have done some of this. let me know if i am on the right track.

    for instance, the format of my data:

    id1 - field1a - field1b - field1c - id2 - field2a - field2b - field2c - id3 - fielda - field3b - field3c - field3d - field3e

    '''''''''''''''''''''
    id1 is thrown away, more of a marker. it is the same (=1) all the way through the text file. field1a, bc, are all a unique record in an info table.

    so i could mass copy this chunk into the other table, then drop these columns from the temp table and update the marker ID columns with the real values.

    '''''''''''''''''''''

    id2 is also another marker, same all through. these fields are in a stimulus column, but up and down, into two tables. one is a list.

    To do this...I would have to go record by record in the temp table, and then go left to right on each record through each field. right? or is there a better way? this is where i get kinda lost, and seems like it will crunch REALLY slow

    id - Parameter - value

    listid - paramID

    '''''''''''''''''''''''''''

    id3 is another marker, but the data behind it is also put in the vertical format
    data_id - Info_id - stimListID - parameter - value
    ------------

    Quote Originally Posted by chupacabra
    hello. i'm just now getting around to working on this.

    with the crosstab query, can i do the opposite of that?

    my data looks like the second example above (what I call horizontal).
    and I am trying to get it similar to the first example (what I call vertical).



    My data look like this (horizontal):

    Code:
    Temperature,Status,Mode,Voltage
    ------------------------------------
    25,On,RF,12
    I want it to be (vertical):

    Code:
    ID,Parameter,Value
    --------------------
    1,Temperature,25
    2,Status,On
    3,Mode,RF
    4,Voltage,12
    many more fields, but for example.

    Thanks for your help



    anybody have any ideas? good ways to go about this?


    I have 13 fields across to split into another table. And then between 20 and 30 fields to go into another table in this manner.
    Last edited by chupacabra; 06-05-2007 at 04:46 PM.
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    The only thing I can think of us to use a different query to handle each column. Like:
    INSERT INTO vParms (Parameter, Value)
    SELECT "Temperature" As Parameter, [Temperature] As Value From hParms;

    INSERT INTO vParms (Parameter, Value)
    SELECT "Status" As Parameter, [Status] As Value From hParms;

    INSERT INTO vParms (Parameter, Value)
    SELECT "Mode" As Parameter, [Mode] As Value From hParms;

    INSERT INTO vParms (Parameter, Value)
    SELECT "Voltage" As Parameter, [Voltage] As Value From hParms;

    If you are going to use this alot you coukd create a untion query as a base for your update query like:
    Update Query -> qup_vParms:
    INSERT INTO vParms (Parameter, Value)
    SELECT Parameter, Value From qun_hParms;

    Union Query -> qun_hParms:
    SELECT "Temperature" As Parameter, [Temperature] As Value From hParms
    UNION SELECT "Status" As Parameter, [Status] As Value From hParms
    UNION SELECT "Mode" As Parameter, [Mode] As Value From hParms
    UNION SELECT "Voltage" As Parameter, [Voltage] As Value From hParms;

  5. #5
    Join Date
    Jun 2006
    Posts
    168
    Ok, that may work. Definitely better than the way I was doing it in my first draft...hahaha.

    I was going from row to row, and column to column in each row...took forever. literally. At the time I knew it was dumb, but it was just a test.


    I'll give it a try in the way you suggested, I think it may just work that way.

    Maybe one day I'll get this project done, I keep getting pulled off to do other things, then basically just work on this in the spare time (what spare time?)


    thanks again!
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  6. #6
    Join Date
    Jun 2006
    Posts
    168
    Quote Originally Posted by Ron Weller
    The only thing I can think of us to use a different query to handle each column. Like:
    INSERT INTO vParms (Parameter, Value)
    SELECT "Temperature" As Parameter, [Temperature] As Value From hParms;

    INSERT INTO vParms (Parameter, Value)
    SELECT "Status" As Parameter, [Status] As Value From hParms;

    INSERT INTO vParms (Parameter, Value)
    SELECT "Mode" As Parameter, [Mode] As Value From hParms;

    INSERT INTO vParms (Parameter, Value)
    SELECT "Voltage" As Parameter, [Voltage] As Value From hParms;

    If you are going to use this alot you coukd create a untion query as a base for your update query like:
    Update Query -> qup_vParms:
    INSERT INTO vParms (Parameter, Value)
    SELECT Parameter, Value From qun_hParms;

    Union Query -> qun_hParms:
    SELECT "Temperature" As Parameter, [Temperature] As Value From hParms
    UNION SELECT "Status" As Parameter, [Status] As Value From hParms
    UNION SELECT "Mode" As Parameter, [Mode] As Value From hParms
    UNION SELECT "Voltage" As Parameter, [Voltage] As Value From hParms;
    the one question i have, is how can i store the qun_hparms to be called....

    basically how can i execute/generate this query in VB?
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  7. #7
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Through Automation. You will have to create an Access Application object that then opens the correct database. From there you will need to access the applications currentDb object, which in turn gives you access to the QueryDefs collection.
    All of this requires that you add the Microsoft Access Objects Library to your project references.
    Below is a code sample for creating and editing a query. This was done in Access so once you have created your Access Application Object variable and have opened the database the code here will work with minor modifications. Just by adding your Access Object Variable, call is accApp, in front of any CurrentDb references will make this work in VB as well. So for example:
    Code:
     CurrentDb.QueryDefs.Append qd
    ' -> becomes:  
     accApp.CurrentDb.QueryDefs.Append qd
    Here is the sample code:
    Code:
    Sub makqun()
      Dim qd As QueryDef
      Dim sql As String
      
      'as a test just put the first two queries in the new querydef
      'run it the first time creates it with the first two queries
      'run it a second time adds in the other two queries
      sql = "SELECT ""Temperature"" As Parameter, [Temperature] As Value From hParms" & vbCrLf & _
            "UNION SELECT ""Status"" As Parameter, [Status] As Value From hParms" & vbCrLf
      On Error Resume Next
      'see if it already exsists
      Set qd = CurrentDb.QueryDefs("qun_hParms")
      If qd Is Nothing Then
        'it did not exsist so create a new one
        Set qd = New QueryDef
        qd.Name = "qun_hParms"
        qd.sql = sql
        'append new querydef to the querydefs collection
        CurrentDb.QueryDefs.Append qd
      Else
        'query already exsisted so I could change it right here
        
        'here is the add in of the other two queries
        sql = sql & "UNION SELECT ""Mode"" As Parameter, [Mode] As Value From hParms" & vbCrLf & _
            "UNION SELECT ""Voltage"" As Parameter, [Voltage] As Value From hParms;"
        qd.sql = sql
      End If
      'all done so release memory for querydef object
      Set qd = Nothing
    End Sub

  8. #8
    Join Date
    Jun 2006
    Posts
    168
    that answered my question exactly!

    thanks, ron!

    I should have this project done in no time, now (assuming access doesn't crash, haha)
    Last edited by chupacabra; 06-19-2007 at 03:29 PM.
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  9. #9
    Join Date
    Jun 2006
    Posts
    168
    what do i DIM the qd as?

    querydef is undefined datatype. I am using ADO, is that part of the DAO library maybe?

    i added this:
    Code:
    Dim accApp As Application
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  10. #10
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    No, DAO is like ADO they are both for data access.
    You need the Microsoft Access ?.? Objects Library where the ?.? is the version like 9.0
    Code:
       Dim accApp As Access.Application
        
        ' Create new hidden instance of Access.
        Set accApp = New Access.Application
        ' Show this instance of Access.
        accApp.Visible = True

  11. #11
    Join Date
    Jun 2006
    Posts
    168
    i have added the access objects 11.0 reference

    but the querydef is not defined


    I was reading this link:
    http://p2p.wrox.com/topic.asp?TOPIC_ID=3746
    that's why i was thinking maybe it was specific to DAO

    The other fundamental difference is that ADO introduces a Command object to represent a SQL statement or stored procedure. The Command object is the ADO replacement for DAOs QueryDef object, and lets you execute DDL and DML commands on a conncetion.
    my accApp is declared right now, though.
    Attached Images Attached Images
    Last edited by chupacabra; 06-19-2007 at 03:29 PM.
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  12. #12
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Looks like VB does not like the idea of creating an empty querydef, setting it's properties, and then appending it to the querydefs collection. No worries you can use the CreateQueryDef() function instead. You will also need to add the references for the DAO, Data Access Objects, as well. QueryDef Objects are part of the DAO Objects Library. It's ok to have both DAO and ADO libraries in your references. Some objects are the same in both so when you define them you should qualify them with either DAO or ADODB. I even do this on objects that exist in only one library, to help document where the object definition came from. Example:
    Code:
      Dim rsDao As DAO.Recordset
      Dim rsAdo As ADODB.Recordset
      Dim qd As DAO.QueryDef
    Here is a revised version that actually works in vb. Sorry I could not do this for you before; but I was at work and I don't have VB at work, just MS Access:
    Code:
    Sub makqun()
      Dim qd As DAO.QueryDef
      Dim sql As String
        
      Dim accApp As Access.Application
      ' Create new hidden instance of Access.
      Set accApp = New Access.Application
      ' Show this instance of Access.
      'accApp.Visible = True
      
      accApp.OpenCurrentDatabase App.Path & "\db1.mdb"
      
      
      'as a test just put the first two queries in the new querydef
      'run it the first time creates it with the first two queries
      'run it a second time adds in the other two queries
      sql = "SELECT ""Temperature"" As Parameter, [Temperature] As Value From hParms" & vbCrLf & _
            "UNION SELECT ""Status"" As Parameter, [Status] As Value From hParms" & vbCrLf
      On Error Resume Next
      'see if it already exsists
      Set qd = accApp.CurrentDb.QueryDefs("qun_hParms")
      On Error GoTo 0
      If qd Is Nothing Then
        Set qd = accApp.CurrentDb.CreateQueryDef("qun_hPArms", sql)
      Else
        'query already exsisted so I could change it right here
        
        'here is the add in of the other two queries
        sql = sql & "UNION SELECT ""Mode"" As Parameter, [Mode] As Value From hParms" & vbCrLf & _
            "UNION SELECT ""Voltage"" As Parameter, [Voltage] As Value From hParms;"
        qd.sql = sql
      End If
      'all done so release memory for querydef object
      Set qd = Nothing
      
      accApp.Quit
      Set accApp = Nothing
      
    End Sub

  13. #13
    Join Date
    Jun 2006
    Posts
    168
    oh no problem, i was just kinda mixed up with it. I don't really know much at all about DAO. I've always declared my ado stuff as ADODB.<whatever>, just figured it was good practice.


    thanks for all the help

    I'll use this code you've given me, hopefully i can get it to work with this database i'm doing. I'll have to modify it to load all my fields....they vary, sometimes only like 30 fields, and sometimes more like 40 or 50. sucks but that's how it goes w/ this kind of data.


    my NEXT question is....is it possible to do these queries in another way, without using access, like just SQL?

    If/when I migrate to a bigger server and start using another database engine (like SQLServer or MySQL), i want to be able to re-use as much code as possible so i wont have to redo everything.

    is there a good SQL resource or something that I can read through? I'm decent with SQL, but when it gets to the really complicated kind of stuff, i'm not too confident with it (yet)

    Thanks again!
    Last edited by chupacabra; 06-20-2007 at 11:54 AM.
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  14. #14
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Quote Originally Posted by chupacabra
    is there a good SQL resource or something that I can read through?
    Here are some links that you might find interesting.

    http://www.sqlcourse2.com/
    http://www.smart-soft.co.uk/Oracle/a...l-tutorial.htm
    http://www.1keydata.com/sql/advanced.html
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  15. #15
    Join Date
    Jun 2006
    Posts
    168
    Quote Originally Posted by Hack


    thanks!

    btw (OT), Justlinux.com is the sister site to this one? I LOVE that site, I've been on there for years, longer than i've been on here! never even knew
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

Similar Threads

  1. Re: (No subject)
    By Joe in forum Database
    Replies: 0
    Last Post: 04-04-2003, 06:13 PM
  2. Replies: 1
    Last Post: 08-01-2002, 04:40 PM
  3. Converting XML data into a HTML Table.
    By sharmila in forum XML
    Replies: 2
    Last Post: 02-05-2001, 04:54 PM
  4. relational table design
    By Harolyn in forum VB Classic
    Replies: 5
    Last Post: 11-02-2000, 03:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center