Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory modem monitor motherboard network printer problem ram registry router security slow software sound toshiba trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Access - field names as data

Reply  
Thread Tools
deepak23's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Nov 2009
Experience: Intermediate
21-Nov-2009, 03:44 AM #1
Access - field names as data
Hi,
Have a requirement to convert data for an ERP application. Currently the data is in the form as below

Fieldname Field1 - Field2 - Field3 - Field 4
----------------------------------------
Record1 Dat1a - Dat2a - Dat3a - Dat4a
Record2 Dat1b - Dat2b - Dat3b - Dat4b

The data is now required in the form below
Fieldname Fielda Fieldb Fieldc
---------------------------------
Record 1 Dat1a - Field2 - Dat2a
Record 2 Dat1a - Field3 - Dat3a
Record 3 Dat1a - Field4 - Dat4a
Record 4 Dat1b - Field2 - Dat2b
Record 5 Dat1b - Field3 - Dat3b
And so on..

Any easy way to do this?
My access programming knowledge is beginner - intermediate at best.

Thanks

Deepak
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
21-Nov-2009, 08:26 AM #2
Deepak, welcome to the Forum.
How many fields do you need to convert in this way?
What Field names will the new data using the old field names have?
deepak23's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Nov 2009
Experience: Intermediate
22-Nov-2009, 03:21 AM #3
Thanks OBP for the welcome
I have a table of 20 fields of which 14 fields needs to be converted. Table has 4500 odd records. Post convertion the table would have 8 fields and 63000 records.
I have attached a sample excel file that shows how the pre & post data needs to look like...

Deepak
Attached Files
File Type: xls techguy.xls (27.5 KB, 97 views)
deepak23's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Nov 2009
Experience: Intermediate
22-Nov-2009, 09:07 AM #4
Hello again,
I wrote a pretty ugly code to do this in excel. (In this case the total no. of fields are 9 and 7 of the fields need to be repositioned across 4 fields as explained in the first post.) The code is basically a few macros merged together in a loop and a condition to stop once all rows have been done. Though it did the job, am sure there is a better and faster way to do it. Looking forward to your help.

The code:

Sub Runprog()
Dim intx As Integer
Dim checka2 As String
intx = 1
Do Until intx = 0
Sheets("Sheet2").Select
Rows("2:8").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Range("A2:B2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2:A8").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C1:I2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _False, Transpose:=True
Sheets("Sheet1").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
checka2 = Range("A2").Value
If checka2 = "" Then
intx = 0
End If
Loop
End Sub

Thanks

Deepak
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
22-Nov-2009, 09:08 AM #5
deepak, I understand what you want to do, but not why you want to do it.
However it goes completely against Relational Database Design Rules as it will produce one very large Table with lots of Duplicated Text Data.
The data should be split it in to multiple tables to prevent this duplication, it is called Normalising the data.
So I would recommend the following Tables

Tag Table
KeyField - TagID - an Autonumber Field
Field - Tag - a Text Field to hold the Tag data.

Characteristic Table
KeyField - CharacteristicID - an Autonumber Field
Field - Characteristic - a Text Field to hold the Characteristic from the current Field Names.

Parameter Table
KeyField - ParameterID - an Autonumber Field
Field - Parameter - a Text Field to hold the Parameter from the Records of current Field


Finally a table to bring all the data together

Usage Table
KeyField - usageID - an Autonumber Field
Field - TagID - a Number type Long Field to hold the TagID.
Field - CharacteristicID - a Number type Long Field to hold the Characteristic ID.
Field - ParameterID - a Number type Long Field to hold the Parameter ID.
__________________
OBP
I do not give up easily
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
22-Nov-2009, 09:13 AM #6
Sorry, I didn't see your post I was busy typing.
If what you have created works, don't bother trying to improve it as you only need it for a one off Conversion.
deepak23's Avatar
Computer Specs
Junior Member with 4 posts.
 
Join Date: Nov 2009
Experience: Intermediate
22-Nov-2009, 09:21 AM #7
Thank you OBP.

Fact is that's exactly how my data is organized presently! But as I said earlier, my co is migrating to an ERP (SAP), and for some obscure reason the data is stored in the desired form there.

Requirement is oneoff, so life goes on with this bit of code. But am sure there are better ways to do this...

Thanks again for your help...

Deepak
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
22-Nov-2009, 09:47 AM #8
Whoever is in control of the ERP does not appear to understand Relational database Design, so good luck to your co.
Reply

Tags
access

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 11:00 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.