Convert imported column into row 2

[bcooler]

Hello-

I have imported .CSV information into a temporary table called tblImport1. The difficulty is that the first 13 rows of info are header info, while the rest is data (see below). I'm wanting to move these 13 rows/1 column (Field2) into another table (tblHeader) with one row and 13 columns. Then I need to move the data (starting at ID=2651) into a related table (as is).

ID	Field1	                  Field2	       Field3	        Field4	      Field5	    Field6
2638	HD Data File					
2639	Acquisition Date:	NOT IMPLEMENTED				
2640	Acquisition Time:	20140408_143148				
2641	Integration Time(ms):	1700				
2642	Laser Power:	100				
2643	Device Pixel Count:	512				
2644	Mode:	CUSTOM Screening Mode				
2645	Primary:Inconclusive or Not In Library.					
2646	Secondary:					
2647	S Hardware Version:	NOT IMPLEMENTED				
2648	S Software Version:	NOT IMPLEMENTED				
2649	S Serial Number:	G1005				
2650	Software Build Date:	Feb 11 2014				
2651	WN	Sub	For	Back	Fil1	Fil2
2652	253.5	7020	28103	21633	2173	1102
2653	258.0	6869	26858	20564	2040	952
2654	262.6	6659	29232	23123	1817	791

I keep seeing websites recommending crosstab queries, but I don't have experience there (I tried the wizard, but not much luck either). Also, I really only need to talk to the first 13 rows here. Maybe a recordset method to surgically extract the info? Just not sure where to go here.

Thanks!
Brian

 

[dhookom]

Are the values in Field1 for the first 13 records always the same? Can we assume there aren't another set of similar records later in the import table?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Consider using a crosstab query with SQL like:

TRANSFORM Min(tblImport1.Field2) AS MinOfField2
SELECT 1 AS RowHead
FROM tblImport1
WHERE (((tblImport1.Field1) Between "A" And "V"))
GROUP BY 1
PIVOT tblImport1.Field1;

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Duane-

Thanks for responding. Yes, the Field1 is always the same. Also, I am emptying the temp table before I execute the above code you wrote, so it will be unique header info.

If you have a second, can you point me to a good resource on how to create these types of queries?

Thanks!
Brian

 

[bcooler]

Ok, I tried it and it works great! Exactly what I needed.

Thank you!
Brian

 

[SkipVought]

@Duane! Thx!

TRANSFORM [i][b]Min(Alpha-Numeric Field)[/b][/i]

I must have missed that very obvious technique!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[dhookom]

@Skip,
Thanks for the star. I've used the technique several times in the past.

@booler,
If you are going to add these records to permanent tables and keep them relational, you will need to create primary and foreign key values.

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Yes, I'm noodling that right now. I figure I will append the info from the temp table into a permanent record and somehow capture the record number Access gives me for later use.

To summarize....I recognize the need, but not sure how.

 

[dhookom]

I don't typically like using domain aggregate functions in queries however you could change the crosstab SQL to:

TRANSFORM Min(tblImport1.Field2) AS MinOfField2
SELECT DMin("ID","tblImport1") AS PrimaryKey
FROM tblImport1
WHERE (((tblImport1.Field1) Between "A" And "V"))
GROUP BY DMin("ID","tblImport1")
PIVOT tblImport1.Field1;

And then pull the related records with this SQL:

SELECT DMin("ID","tblImport1") AS ForeignKey, tblImport1.Field1, tblImport1.Field2, 
tblImport1.Field3, tblImport1.Field4, tblImport1.Field5, tblImport1.Field6
FROM tblImport1
WHERE Field1 Between "0" And "9999";

This solution makes some assumptions about your data that might not be true.

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Ok, having difficulty with the foreign key....

Let me explain what I am doing (in greater detail).

I have a table (tblLibrary) where I am keeping manually entered user information. I am trying to link the above .CSV text file information to the tblLibrary table. The thought is that the user opens a record in tblLibrary (using a form called frmLibrary). Inside the form, they have the command button to import the .CSV info. Once they press the button, The code is:

1.) Deleting the temporary info in the tblImport1 table (query called qryDeleteImport1):

DELETE tblImport1.*
FROM tblImport1;

2.) Using your code to transpose the necessary info from the .CSV file (query called qryHeaderXTab).

TRANSFORM Min(tblImport1.Field2) AS MinOfField2
SELECT 1 AS HeaderID
FROM tblImport1
WHERE (((tblImport1.Field1) Between "A" And "V"))
GROUP BY 1
PIVOT tblImport1.Field1;

3.) Using an append query to copy the data into tblHeader table (query called qryHeaderDataMove).

INSERT INTO tblHeader ( [Acquisition Date:], [Acquisition Time:], [Integration Time(ms):], [Laser Power:], [Device Pixel Count:], [Mode:], [Primary:], [Secondary:], [Spectrometer Hardware Version:], [Spectrometer Software Version:], [Spectrometer Serial Number:], [PGR Software Build Date:] )
SELECT qryHeaderXTab.[Acquisition Date:], qryHeaderXTab.[Acquisition Time:], qryHeaderXTab.[Integration Time(ms):], qryHeaderXTab.[Laser Power:], qryHeaderXTab.[Device Pixel Count:], qryHeaderXTab.[Mode:], qryHeaderXTab.[Primary:], qryHeaderXTab.[Secondary:], qryHeaderXTab.[Spectrometer Hardware Version:], qryHeaderXTab.[Spectrometer Software Version:], qryHeaderXTab.[Spectrometer Serial Number:], qryHeaderXTab.[PGR Software Build Date:]
FROM qryHeaderXTab;

The next problem I am faced with is tying the header info (tblHeader) to the User info (tblLibrary) via a foreign key.

My thought is to somehow append the frmLibrary's primary key (form's field name=txtID) to the tblHeader foreign key (table's field name = "Library_ID") at the same time as the above append query. I mean, I have the correct key info sitting in the form.....I just need to append it at the same time as the above append query.

Does this make sense? Not sure what to do next. I recognize that you sent me some help on foreign keys, but I don't think that it helps with what I actually need (my fault for not explaining thus far).

Brian

 

[dhookom]

What about my suggestion to use the minimum value of the ID column?

TRANSFORM Min(tblImport1.Field2) AS MinOfField2
SELECT DMin("ID","tblImport1") AS PrimaryKey
FROM tblImport1
WHERE (((tblImport1.Field1) Between "A" And "V"))
GROUP BY DMin("ID","tblImport1") 
PIVOT tblImport1.Field1;

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Ok, I did try that when you suggested it. However, since I am only using tblImport1 as a temporary table, its primary key is not important to me (right?).

I want to connect tables tblHeader (foreign) and tblLibrary (primary). As the above append query executes, it copies data from the crosstab query into table tblHeader. While it is doing that, I was hoping it could also connect the above tables by appending the currently opened form's (frmLibrary)primary key (called txtID, connected to tblLibrary) into the tblHeader's foreign key location (called Library_ID).

Clear as mud?

Brian

 

[dhookom]

You can use a column like the following for the Row Heading.

MyID: Forms!frmLibrary!txtID

Duane
Hook'D on Access
MS Access MVP

 

[bcooler]

Ok, as best I understand, I tried the following by replacing "HeaderID: 1" with what you suggested:

TRANSFORM Min(tblImport1.Field2) AS MinOfField2
SELECT [Forms]![frmLibrary]![txtID] AS MyID
FROM tblImport1
WHERE (((tblImport1.Field1) Between "A" And "V"))
GROUP BY [Forms]![frmLibrary]![txtID]
PIVOT tblImport1.Field1;

However, I receive the following error, "The Microsoft Access database engine does not recognize '[Forms]![frmLibrary]![txtID]' as a valid field name or expression."

I tried to use the builder to recreate the reference (maybe I got the textbox name wrong), but I received the same error.

Ok, so what am I doing wrong?
Brian

 

[PHV]

I'd use the PARAMETERS declaration, eg:

PARAMETERS [Forms]![frmLibrary]![txtID] INTEGER;
TRANSFORM Min(tblImport1.Field2) AS MinOfField2
SELECT [Forms]![frmLibrary]![txtID] AS MyID
FROM tblImport1
WHERE (((tblImport1.Field1) Between "A" And "V"))
GROUP BY [Forms]![frmLibrary]![txtID]
PIVOT tblImport1.Field1;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bcooler]

ok, I found this article which says I must explicitly declare the form reference in a crosstab query:

[URL unfurl="true"]http://support.microsoft.com/kb/209778[/url]

Now the queries work outside the VBA code (manually). However, when I run them using:

Set qdf = CurrentDb.QueryDefs("qryHeaderDataMove")
DoCmd.SetWarnings False
qdf.Execute 'copy header info into tblHeader
DoCmd.SetWarnings True

I get an error: 3061, "Too few parameters. Expected 0.

 

[bcooler]

Ok, got it.

[URL unfurl="true"]http://www.pcreview.co.uk/forums/error-3061-a-t3665097.html[/url]

This site suggests that the parameters in a crosstab query must be explicitly assigned in DAO. The important part here is:

Dim prm As DAO.Parameter
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm

Not why this is necessary, but it works.

Thanks for all the help and sorry PHV.....I was posting my previous response when you sent me your solution. Thanks!

Brian