Results 1 to 3 of 3

Importing and transforming data

  1. #1
    keith2511 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2017
    Location
    UK
    Posts
    35

    Importing and transforming data

    I export data from a system in the format shown in Table 1 (and I cannot change this format).




    In order to import the data into Access I use Excel to transform it to the format shown in Table 2.


    Once in Access, I then update each record with additional fields (SubGroup1 etc), as shown in Table 3.


    I would like to avoid having to transform the data. So two questions:


    1. Is there a way I can import the data in Table 1 format into a format in Access that I can then manipulate?


    2. When I'm importing data into Access, can I use a run-time prompt for the data, which then becomes a field in my target Access table?


    Any advice on this would be gratefully received.
    Click image for larger version. 

Name:	Capture220519.PNG 
Views:	13 
Size:	25.7 KB 
ID:	38461

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,229
    Where is data imported from? Can you set a link to that data? Are the product columns always the same?

    Access can manipulate the Table1 structure.

    A UNION query can rearrange Table1 data to Table2 structure.

    Popup prompts are difficult to validate user input. Best to have users enter data into a textbox on form and code refer to that control.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    On top of what June has said, I see a bunch of other issues here. It appears as though your exported data is the product of a crosstab query or the source data structure is bad and this is going to cause you grief over time. For instance, what is your product name changes, even by 1 character, all your history will be useless to you. Same thing for the site descriptions. I agree with June also on pop up prompts. Far better to do your import first, then go back and fill the data as necessary. Or better yet, if it's logically possible to calculate your subgroup1 and subgroup2 based on the data in the import I would do that instead.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Transforming data
    By keith2511 in forum Access
    Replies: 2
    Last Post: 09-18-2018, 01:33 AM
  2. Replies: 4
    Last Post: 06-14-2018, 10:27 AM
  3. Transforming a spreadsheet - queries?
    By CP611 in forum Access
    Replies: 7
    Last Post: 11-08-2017, 06:22 AM
  4. Transforming Rows into Columns
    By tonygg in forum Queries
    Replies: 12
    Last Post: 01-08-2017, 06:07 PM
  5. transforming text to number
    By eladz949 in forum Import/Export Data
    Replies: 5
    Last Post: 01-05-2011, 04:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums