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.
'''''''''''''''''''''
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
------------
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).
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;
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?)
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?
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:
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
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
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 DAO’s QueryDef object, and lets you execute DDL and DML commands on a conncetion.
my accApp is declared right now, though.
Last edited by chupacabra; 06-19-2007 at 02:29 PM.
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
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 10:54 AM.
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
Converting Flat Table to Relational
Reply With Quote
Originally Posted by chupacabra
(assuming access doesn't crash, haha)
Bookmarks