Results 1 to 5 of 5
  1. #1
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    76

    How best to split this data up between two columns ?

    Hi,


    Access 2010
    Searched on internet but all I see is update query for entire column.,
    My first approach was copy data across to new field in one go, not finding out how to do that, I have done quite a few one at a time, its going to take a long time, so how does one copy over data from one field to another in same table for just some records ?

    I was then to use the Mk1 brain and generate I II or III to do the group making and physically delete out the old group data in the unit field., to follow the group rules below !

    Maybe instead I need to do this a more intelligent way, units 1 2 and 3 belong to I group, 4 5 6 to II , and 7 8 9 to III
    I have added a group field to separate out group from the unit, I need somehow to look at the number, and generate a I II or III for the Group field, stripping out the Gr from the unit field in the process as well as the roman numerals.

    I would need to see this as a file as my coding skills are beginners at best ! so telling me what sql statement to use leaves me floundering.

    Old design
    Unit or Group
    1
    III Gr
    7
    2
    III Gr
    4

    new design:-
    Unit Group
    1 I
    III
    7 III
    2 I
    III
    4 II

    attached a mockup of new table.

    Would still like to know though how to copy some fields across, good to know such anyway.

    Regards

    DBenz
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Go to SQL View of query builder and copy/paste the following SQL statement:

    SELECT Tbl_UnitGroup.ID, Tbl_UnitGroup.Unit, Switch([Unit] In ("1","2","3","I","I Gr"),"I",[Unit] In ("4","5","6","II","II Gr"),"II",[Unit] In ("7","8","9","III","III Gr"),"III") AS Grp FROM Tbl_UnitGroup;

    If you want to actually populate Group field, use:

    UPDATE Tbl_UnitGroup SET Tbl_UnitGroup.[group] = Switch([Unit] In ("1","2","3","I","I Gr"),"I",[Unit] In ("4","5","6","II","II Gr"),"II",[Unit] In ("7","8","9","III","III Gr"),"III");

    If you want to remove " GR" from [Unit] field, a simple Find/Replace can accomplish that. But do you really want to change the original data?
    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
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    76
    Hi,
    I will give that a go, but need to double check, I have already done the alteration for a number of records manually one at a time, so will this do the remainder ?
    Yes I do need to change the original data, only to have numbers or blanks in the unit field.

    Cheers

    DBenz

    Quote Originally Posted by June7 View Post
    Go to SQL View of query builder and copy/paste the following SQL statement:

    SELECT Tbl_UnitGroup.ID, Tbl_UnitGroup.Unit, Switch([Unit] In ("1","2","3","I","I Gr"),"I",[Unit] In ("4","5","6","II","II Gr"),"II",[Unit] In ("7","8","9","III","III Gr"),"III") AS Grp FROM Tbl_UnitGroup;

    If you want to actually populate Group field, use:

    UPDATE Tbl_UnitGroup SET Tbl_UnitGroup.[group] = Switch([Unit] In ("1","2","3","I","I Gr"),"I",[Unit] In ("4","5","6","II","II Gr"),"II",[Unit] In ("7","8","9","III","III Gr"),"III");

    If you want to remove " GR" from [Unit] field, a simple Find/Replace can accomplish that. But do you really want to change the original data?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, will do remainder.

    Run the UPDATE and if that provides correct result then do Find/Replace for each of the Roman number values you want to remove from Unit field.

    Suggest you first make a copy of table just in case something goes wrong and need to start over.
    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.

  5. #5
    DBenz is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2014
    Posts
    76
    Hi, That worked a treat.
    I can use that with different field and tbl entries in the future, wish I understood sql more.
    I did in fact try that as I have altered two field names but got a syntax error, re did and success, not sure why as I was so careful,
    me and coding, I always get a problem.

    anyway many MANY thanks.

    DBenz

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

Similar Threads

  1. Split data into serperate columns
    By Pure Salt in forum Queries
    Replies: 14
    Last Post: 02-17-2017, 09:19 AM
  2. Replies: 3
    Last Post: 11-03-2015, 11:19 PM
  3. Hiding columns in split form.
    By cementblocks in forum Forms
    Replies: 5
    Last Post: 03-11-2014, 07:16 AM
  4. Replies: 15
    Last Post: 01-12-2011, 05:13 PM
  5. split a column into two seperate columns
    By nybanshee in forum Access
    Replies: 2
    Last Post: 08-14-2008, 04:52 PM

Posting Permissions

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