Results 1 to 8 of 8
  1. #1
    joebox8 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jun 2011
    Posts
    13

    combining 3 columns into 1 new column!


    hi there.. I have 3 columns/fields in my master table(T_MASTER), class size and type... examples would be LAC_BP_13010 where LAC is the class, BP is the type and 13010 is the size. There are roughly 7 different classes, 4 types and many sizes..
    What i want to be able to do is combine the values from each of the 3 columns and have that combined value in its own column... Im sure its a very easy Query however its a Monday morning and my brain will not work!
    Any ideas would be great!
    thanks

  2. #2
    joebox8 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jun 2011
    Posts
    13
    its ok.. got it!


    UPDATE T_MASTER SET T_MASTER.sizetype2 = [Class] & "_" & [TP] & "_" & [Size_Type];

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi

    Perhaps this would work if placed in a blank field of a query based on your "T_MASTER" table.

    Code:
     
    Combined:[Class] & "_" & [Type] & "_" & [Size]
    BTY: "Type" is a reserved word in access. It would be wise to change the name of this field before it gives you problems.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    joebox8 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jun 2011
    Posts
    13
    Thanks Bob,
    I have it working behind the scenes off a button that's used for calculations, However I dont really want it update every row in my new column.. how would i restrict the above Query so it would only concatenate the selected Class, TP, and size from their corresponding combo boxes?

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi
    I'm not sure, but it sounds as though you need to add some criteria with a WHERE. Maybe something like:
    Code:
    UPDATE T_MASTER SET T_MASTER.sizetype2 = [Class] & "_" & [TP] & "_" & [Size_Type] WHERE (((T_MASTER.sizetype2)=" & [COMBOBOXNAME].Value & "));
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    joebox8 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jun 2011
    Posts
    13
    Can you think of a better way of doing this? That query is to slow and updates nearly 3500 entries aswell as the one I want concatenated...

    Basically i have 3 cascading combo boxes, each corresponding to a field in my table(T_MASTER), what i really want to do is on entry a corresponding passfail criteria(just a number, eg 15) is added to a column(PASSCRIT) for that entry so I can then make a simple calculation whether that entry is a pass or a fail...

    I know I'm prob complicating things however its just the way i have things laid out here.. Thanks for the help bob, what do you think?

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi

    I'm a little confused.
    You said that the Update Query updates 3500 records as well as the ONE that you want to update. Is it the case that 3500 records meet the stated criteria and the table has more records than this, or that the criteria is being ignored and the 3500 records are all the records of the table?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    joebox8 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jun 2011
    Posts
    13
    hi bob, The criteria seems to be ignored altogether.. Updates every entry in table, Ive decided to go down another route anyhow so thanks alot for your help..

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

Similar Threads

  1. Replies: 15
    Last Post: 01-12-2011, 05:13 PM
  2. Combining values of 2 columns into one string
    By LAazsx in forum Programming
    Replies: 1
    Last Post: 11-25-2010, 08:36 PM
  3. Combining columns into rows
    By steeveepee33 in forum Queries
    Replies: 5
    Last Post: 04-30-2009, 09:18 PM
  4. split a column into two seperate columns
    By nybanshee in forum Access
    Replies: 2
    Last Post: 08-14-2008, 04:52 PM
  5. Replies: 1
    Last Post: 06-21-2007, 01:02 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