Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: split up values in a column table into two separate fields

    I have a column in a table that have -1 and 0 values. I'm trying to create a query to split up these values into two separate columns, but all I can get are same values in both columns.


    SELECT dbo_custconcerns.qcappleprob AS A, dbo_custconcerns.qcappleprob AS B
    FROM dbo_custconcerns
    WHERE (((dbo_custconcerns.qcappleprob)=-1)) OR (((dbo_custconcerns.qcappleprob)=0));

    I would like -1 store in A column and 0 in B column.

    How can I do that?

    Thanks!

  2. #2
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91

    Re: split up values in a column table into two separate fields

    Try this:

    SELECT Iif(dbo_custconcerns.qcappleprob = -1, -1, Null) AS A, Iif(dbo_custconcerns.qcappleprob = 0, 0, Null) AS B
    FROM dbo_custconcerns;

  3. #3
    Join Date
    May 2002
    Posts
    395

    Re: split up values in a column table into two separate fields

    You’ve been a BIG help showing me in using the IIf in the SELECT statement. Thanks!!
    But I’m still having trouble to display it correctly.

    This is my SQL statement:

    SELECT Format([dateentered],"mm/yyyy") AS [Date], IIf(tblcustconcerns. prob=-1,Count(tblcustconcerns.prob),0)) AS Apple, IIf(tblcustconcerns. prob=0,Count(tblcustconcerns.prob,0)) AS Other
    FROM tblcustconcerns
    GROUP BY Format([dateentered],"mm/yyyy"), (DatePart("yyyy",[dateentered])), (DatePart("m",[dateentered])), dbo_custconcerns.qcappleprob
    ORDER BY (DatePart("yyyy",[dateentered])), (DatePart("m",[dateentered]));
    Ex:
    Date Apple Other
    09/1988 0 1
    12/1998 8 0
    12/1998 0 6
    01/1999 12 0
    01/1999 0 3
    02/1999 17 0
    02/1999 0 11

    I think it is still comparing the same value(-1) that was counted & stored in the “Apple” instead of going to the next grouped value(0) for the “Other” field. How can I fix it?

  4. #4
    Join Date
    Aug 2002
    Location
    Singapore
    Posts
    91

    Re: split up values in a column table into two separate fields

    Actually I didn't understand why do you need to split up the values. What is your purpose and how the expected result looks like?

  5. #5
    Join Date
    Nov 2001
    Posts
    336
    In your case dbo_custconcerns.qcappleprob looks like a boolean field, so SQL below might work for you:

    SELECT dbo_custconcerns.qcappleprob AS A, Not dbo_custconcerns.qcappleprob AS B
    FROM dbo_custconcerns;

    HTH, Igor

  6. #6
    Join Date
    May 2002
    Posts
    395
    Thanks!
    However, this will give me two values.

    Ex:
    Date A B
    09/1988 0 -1
    12/1998 -1 0
    12/1998 0 -1
    01/1999 0 -1
    01/1999 -1 0


    How can I fix it?

  7. #7
    Join Date
    Nov 2001
    Posts
    336
    What kind of fix do you want? Give an example.

    Igor

  8. #8
    Join Date
    May 2002
    Posts
    395

    Re: split up values in a column table into two separate fields

    instead of looking like this
    Date A B
    09/1988 -1
    12/1998 -1
    12/1998 0
    01/1999 -1
    01/1999 0


    I would like the two values display in the same row by the date

    Date A B
    09/1988 -1
    12/1998 -1 0
    01/1999 -1 0

    Would it be possible to write in one query?

    Thanks much!

  9. #9
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    Did you have a chance to look at Crosstab query wizard in MS Access?
    It may produce the query you need.

    Igor

  10. #10
    Join Date
    May 2002
    Posts
    395
    Thanks! I'll check it out.

Posting Permissions

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