Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    14

    Question Unanswered: How To Separate a Field into 2 fields

    Hi,

    I have this problem:

    I have a MEMBERSHIP table. There are 3 fields : MEMBER#, ITEM, and ITEM_VALUE.

    ITEM has only 2 value "REGULAR" or "EXPRESS"

    and the ITEM_VALUE contains the date of the item.

    I want to separate the ITEM field to create a table that have the following fields (divide the ITEM into REGULAR and EXPRESS)

    MEMBER# REGULAR EXPRESS
    1234 2/3/03 2/5/02
    .....
    ...
    ...



    Please show me how to do it in Access 2002. Thanks a lot.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you did not say that there were always exactly one of each of REGULAR and EXPRESS items for each member number, so to allow for the possibility of one or the other missing, it requires a full outer self-join:
    Code:
    select reg.memberno
         , reg.item_value as regular
         , exp.item_value as express
      from membership reg
    left outer 
      join membership exp
        on reg.memberno = exp.memberno
     where reg.item = 'REGULAR'
       and exp.item = 'EXPRESS'
    union all
    select exp.memberno
         , reg.item_value as regular
         , exp.item_value as express
      from membership reg
    right outer 
      join membership exp
        on reg.memberno = exp.memberno
     where exp.item = 'EXPRESS'
       and reg.memberno is null
    rudy
    http://r937.com/

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    My solution assumes that only two instances of ITEM_VALUE will be in the results, and since this is a date field, the maximum of each date is pulled within REGULAR and EXPRESS for each MEMBER#.

    This may not be what you wanted (only 2 dates) but it is what I understand by your explanation.

    Here is the code and there is an attached small database.

    I created 1) a query for only "REGULAR", 2) a query for only "EXPRESS" and 3) a query that includes the table and the above queries to get the exact format you displayed in your example list of data.

    SELECT Table1.[MEMBER#], Query1.MaxItem AS REGULAR, Query2.MaxItem AS EXPRESS
    FROM (Table1 LEFT JOIN Query1 ON Table1.[MEMBER#] = Query1.[MEMBER#]) LEFT JOIN Query2 ON Table1.[MEMBER#] = Query2.[MEMBER#]
    GROUP BY Table1.[MEMBER#], Query1.MaxItem, Query2.MaxItem;
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •