INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Convert imported column into row

Convert imported column into row

(OP)
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).

CODE -->

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

RE: Convert imported column into row

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

RE: Convert imported column into row

2
Consider using a crosstab query with SQL like:

CODE --> SQL

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

RE: Convert imported column into row

(OP)
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

RE: Convert imported column into row

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

Thank you!
Brian

RE: Convert imported column into row

@Duane! Thx!

CODE

TRANSFORM Min(Alpha-Numeric Field) 
I must have missed that very obvious technique!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Convert imported column into row

@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

RE: Convert imported column into row

(OP)
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.

RE: Convert imported column into row

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

CODE --> SQL

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:

CODE --> 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

RE: Convert imported column into row

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

CODE

DELETE tblImport1.*
FROM tblImport1; 

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

CODE

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

CODE

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

RE: Convert imported column into row

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

CODE --> SQL

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

RE: Convert imported column into row

(OP)
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

RE: Convert imported column into row

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

CODE --> SQL

MyID: Forms!frmLibrary!txtID 

Duane
Hook'D on Access
MS Access MVP

RE: Convert imported column into row

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

CODE -->

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

RE: Convert imported column into row

I'd use the PARAMETERS declaration, eg:

CODE --> SQL

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Convert imported column into row

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

http://support.microsoft.com/kb/209778

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

CODE

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."

RE: Convert imported column into row

(OP)
Ok, got it.

http://www.pcreview.co.uk/forums/error-3061-a-t3665097.html

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close