Converting Flat Table to Relational

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.dev-archive.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.

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

----

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):

Temperature,Status,Mode,Voltage
-----------
25,On,RF,12

I want it to be (vertical):

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.

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! :)

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: CurrentDb.QueryDefs.Append qd
' -> becomes:
accApp.CurrentDb.QueryDefs.Append qd
Here is the sample 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

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:
Dim accApp As Application

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. :)

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!

Here are some links that you might find interesting.

http://www.sqlcourse2.com/
http://www.smart-soft.co.uk/Oracle/advanced-sql-tutorial.htm
http://www.1keydata.com/sql/advanced.html

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

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)
Yes absolutly! The only reason you needed Access in this example was because you wanted to create and store the query in vb. If you just need to build and execute the query without saving the actual query in access then you don't need to use Automation i.e. (Access Objects Library) at all.
The basic steps are fairly simple. For each different database you create an ado connection object and open a connection to that database. I usually open my connection objects at the very beginning of my program and don't close them until the very end when my application is terminating. This way I have access to the connection objects throughout the app and I don't have to open and close them every time I need them.
With these connection objects you can use them when you create and open recordsets. Recordsets are used for running your queries. You take the sql text, just like you did in this thread, but now you use it in the Recordset.Open function. The Open function executes the SQL in your String variable. Once the recordset is open you can move through it and access each field of each record. You can edit and update the fields of each record, add new records, etc... If you goto www.freevbcode.com and search on ADO you will find lots of samples that will show you exactly how to do this.
As far as SQL goes, what I did when I first started with SQl was to build my queries in the Access query designer, also I could make sure they work, and then switch to SQL View, copy and paste the sql into my VB app. Once you paste it into VB you will need to store it as a string in a variable like we did with the Sql string variable in the previous posts. As you do this more and more and begin to edit the SQL even after building it in Access you will very quickly pickup on the SQL syntax. Just be aware that every database system has a different variation on it's implimentation of SQL. You will need to edit your SQL strings to fit the particular database system.

Sure! 'A Make-Table query would be more like this:
SELECT * INTO vParms
FROM (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
) AS xParms;

'And An Append Query would look like this:

INSERT INTO vParms
SELECT *
FROM (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
) AS xParms;

awesome, thanks!

another question though...in this query I want to take the first section of the flat table, and put into it's table (TI). I want to update the TI_id fields in the flat table to be = to the distinct values in the TI table.

For instance, there may be 500,000 rows in the flat table. if I take a "DISTINCT" of just the TI fields that are in the flat table, there may only be 10 unique. So i'm updating the ID field in the flat table w/ the id fields from the TI table where the other fields match.

is my WHERE statement in the correct place?

'tablefields() array of the fields in the TI table
query = "UPDATE " & DATATABLE & vbCrLf & " SET [" & DATATABLE & "].[TI_id] = " & vbCrLf & "(SELECT [TI_id] FROM TI "
query = query & "WHERE (" & vbCrLf
For i = 0 To intfields Step 1
If UCase(tableFields(i)) = TI_id Then i = i + 1
query = query & vbTab & "[TI].[" & tableFields(i) & "] = [" & DATATABLE & "].[" & tableFields(i) & "] "
If Not i = intfields Then query = query & ", " & vbCrLf & vbTab
Next i
query = query & ")" & vbCrLf & ");"

?query
UPDATE DATA_WLAN
SET [DATA_WLAN].[TI_id] =
(SELECT [TI_id] FROM TI WHERE (
[TI].[C1] = [DATA_WLAN].[C1],
[TI].[CalFile] = [DATA_WLAN].[CalFile],
[TI].[PN] = [DATA_WLAN].[PN],
[TI].[SN] = [DATA_WLAN].[SN],
[TI].[Start_Date] = [DATA_WLAN].[Start_Date],
[TI].[Start_Time] = [DATA_WLAN].[Start_Time],
[TI].[TB] = [DATA_WLAN].[TB],
[TI].[TestStand] = [DATA_WLAN].[TestStand],
[TI].[TP] = [DATA_WLAN].[TP],
[TI].[TT(min)] = [DATA_WLAN].[TT(min)])
);

update: the union select query worked great!

However now i'm completely confused as to how to do the next step... :confused: :(

I have populated my TS table. Now, I just need to populate the TS_set table and add those ID's to the flat DATA table before i move the data itself out w/ another union select query.

here is my db structure for reference:

First:

a testinfo table which is simple and just contains basic information.
(which I'm not including, because it's just easy as is)

Next:

CREATE TABLE TS (
[TS_id] COUNTER,
[Param] Text(8),
[nValue] SINGLE,
[sValue] Text(16),
PRIMARY KEY ([Param],[nValue],[sValue])
);

This table will contain a complete list of all of the parameters with distinct values. There are a fininte number of these.

a combination of several (5-10) of these parameters is a "set" of Test stimuli... a lsit of which is being stored in another table:

CREATE TABLE TS_Set (
[TSSet_id] COUNTER,
[TS_id] LONG,
PRIMARY KEY ([TSSet_id], [TS_id])
);

for example.
----------------
TS
id param nvalue svalue
1 temp 25 (null)
2 time 2150 (null)
3 mod (null) a180

TS_Set
set_id ts_id
1 1
1 2
1 3

------------------

Last, but not least. the actual data, which is lots and lots of rows, and is certainly one huge table.

CREATE TABLE DATA (
[Data_id] COUNTER,
[TI_id] LONG,
[TSSet_id] LONG,
[Param] TEXT(16),
[Value] DOUBLE,
PRIMARY KEY (TI_id,TSSet_id,Param)
);