UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Putting Old Data Into New Records, Access 2010    
 
   
nuclear_nick
post Apr 17 2017, 10:29 AM
Post#1



Posts: 1,296
Joined: 5-February 06
From: Ohio, USA


I had/have a database and programming that 'reads' an Excel file and puts cell values into the database based on the cell. For example, A1 is the 'form type', H3 is the company, A6 is the order number...

So I put them into a table...
CODE
tblAttachmentDetail
------------------------------
AttachmentID
PlBrand
PlPO
PlTotalCartons
PLCompleteOrder
...

Not all cells had a value, not all cells were needed... so that had to change, so I did. I created a table of the cell locations and a name and programming that skips recording the data if it is blank on the Excel form. It's structured like this...
CODE
tblMessageAttachmentDetail
---------------------------------------
AttachmentID
PackingListFieldID
PackingListFieldValue


Simple.

So I've gone through the database, modifying the programming/queries for the new data.

Now I need to keep the old data and put it into the new structure. For instance...

CODE
Old record
<field name>, <value>
------------
PlBrand, 1
PlPO, 54329
PlTotalCartons, 23


CODE
New Record
------------------
Attachment       |  FieldID       |  FieldValue
      1           |     1       |      1
      1           |     2       |      54329
      1           |     3       |      23


So... basically normalizing the table.

Is there some magic SQL? Or am I going to have do some coding? I'm fine either way, just wanted a fast method, as I have 211,194 records to do this way.
This post has been edited by nuclear_nick: Apr 17 2017, 10:31 AM

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Doug Steele
post Apr 17 2017, 11:21 AM
Post#2


UtterAccess VIP
Posts: 21,058
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I think you're going to have to provide a little more detail.

I often link to a spreadsheet, then use Append queries (INSERT INTO...) on that linked spreadsheet to correctly place the data into the normalized tables.

--------------------
Go to the top of the page
 
nuclear_nick
post Apr 17 2017, 12:16 PM
Post#3



Posts: 1,296
Joined: 5-February 06
From: Ohio, USA


Thank you for the feedback, Doug.

The 'Excel' file, originally, was a 'Packing Slip'... at the top are boxes for PO number, address, etc... and order details at the bottom.

When I originally 'imported' the slip, I had a table with fields for each box at the top, whether the box contained a value or not, so there were a lot of blank fields in the records.

So I changed the table to only hold those boxes that had a value (tblMessageAttachmentDetail, because the packing slips are emailed to us as an attachment). Instead of maintaining two separate forms (slips received before/after 'go live' date of new method), I'd like to just append old data into the new tables.

Does that help?

Thank you again.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Doug Steele
post Apr 17 2017, 01:50 PM
Post#4


UtterAccess VIP
Posts: 21,058
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Can you create a Named Range in your Excel workbook that only contains the useful information and link to that?

Once you've linked to the data so that you've got it in a useful form, you should be able to write queries to manipulate it for you.

--------------------
Go to the top of the page
 
nuclear_nick
post Apr 18 2017, 06:04 AM
Post#5



Posts: 1,296
Joined: 5-February 06
From: Ohio, USA


I think you may be missing something in the reading.

I've already captured the data. I've changed the table I put the data into, and would like to transfer the old data into the new, more normalized tables. The old TABLE had a field for each box on the 'packing slip'. Now my table has three fields... an ID for the packing slip (file information in another table), an ID for the field (field information, such as the Excel cell information, in another table), and the value in the cell.

So how to transfer the data quickly... that is my quandary.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Doug Steele
post Apr 18 2017, 07:51 AM
Post#6


UtterAccess VIP
Posts: 21,058
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Why did you choose to store the data in such an arcane fashion?

Rather than taking the intermediate step of storing the data in that table then putting the data into your permanent tables, why not just go directly from Excel to the final tables?

--------------------
Go to the top of the page
 
nuclear_nick
post Apr 18 2017, 08:09 AM
Post#7



Posts: 1,296
Joined: 5-February 06
From: Ohio, USA


The original database was done 6 years ago... now I know better. smile.gif

I don't have an 'intermediate' table... I do store the data directly from Excel. But what I have is 6 years worth of old data to import into my new structure. (Actually we're just going back a year... since there is a date when the packing list was received. The rest is archived on paper for business record reasons. smile.gif )

So that's why I was looking for the fastest way to, say, take data from a 'long table' into it's 'short form'.

Sorry for any confusion, and hope that clears it up. Let me know if I can clarify more.

Thank you.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Doug Steele
post Apr 18 2017, 08:46 AM
Post#8


UtterAccess VIP
Posts: 21,058
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Looks like I may have muddied the waters. Unfortunately, I know a number of people don't look at posts that have had a lot of discussion already, so your question may not get the variety of input it should.

You may wish to repost your question, including a link to this thread so that people can read what's already been discussed. Once you've done that, come back here and post the link to the new thread so that I'm aware of it. Unfortunately, I may not have too much more availability this week, and I don't want you to have to wait too long to hear back from me!

Good luck with your project. smile.gif

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    30th April 2017 - 09:45 AM