Solved

Help With Pasting or Assigning Data in Database

Posted on 2011-09-11
8
257 Views
Last Modified: 2012-05-12
Attached are 2 comma delimited results files (Sample) (First file are Rows 1 to 110 and the Second file are rows 111 to 206)  in of a group of 59k comma delimited results files.  In column A are 3 letters (H,R and S)  to identify each row of data.  The first is H which has in the row 2 fields that I need to assign/paste  to each row in the S Rows.  I do not need to do anything to the R rows.   I need to assign/past data from C1(SAR)  to Rows L12 to  L110  and also assign/paste data from  D1(20100901) to Rows M12 to M110.

The next file starts on Row 111 and would need to assign/paste  Data in Cell C111(SUF)  to rows L121 to L206 and assign/past  Data(20100901)  in D111 to Rows M121 to M206.

I need the above query/vba code to do this to all 59k results.
 
The result I am wanting would look like Sample 2.

H Row is always 1 row
R rows can be anywhere from 5 to 14 rows
S rows vary and could be anywhere from 45 rows to 170 rows.
Sample.csv
Sample2.csv
0
Comment
Question by:JackJackson54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 40

Expert Comment

by:als315
ID: 36520947
Do you like to have results in Access table or in csv file?
How files can be sorted (how can we determine files sequence)?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36521045
This doesn't look like structured and normalized data that you would put into a database.  Maybe a spreadsheet, but not a database.  What are you trying to do with the data in a database?
0
 
LVL 21
ID: 36521277
To me it sure loots like normalized data.  

Column A is the record type. Column B has the data to link the R record to the related S records.

If imported into a normalized structure you would have three tables.

H (1) --> (many) R () -->> (many) S

This would be easy to do with VBA code in Access. I will put together a sample.




 
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 36521560
I create a module the has a sub for importing the file

See attached database
 import-csv.accdb
0
 

Author Comment

by:JackJackson54
ID: 36522043
All I want to do is put all the S Rows in an Access database and the result would look like Sample 2
0
 

Author Comment

by:JackJackson54
ID: 36522086
I thought if if could be put in like Sample 2, I could query out all the S rows.
0
 
LVL 40

Assisted Solution

by:als315
als315 earned 250 total points
ID: 36522094
If you take Boyd's (TheHighTechCoach) example and add this query:
 
SELECT "S" AS S, tblS.S_1, tblS.s_2, tblS.S_3, tblS.S_4, tblS.S_5, tblS.S_6, tblS.S_7, tblS.S_8, tblS.S_9, tblS.S_10, tblH.H_2, tblH.H_3
FROM (tblH INNER JOIN tblR ON tblH.HRecordID = tblR.R_HRecordID) INNER JOIN tblS ON tblR.RRecordID = tblS.S_RRecordID;

Open in new window

it will be expected result
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question