Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining data from two fields in a query (2000-SR1)

    I have a query with shirt orders--two styles, say long-sleeved and short-sleeved. Each can be ordered in different colors. How can I combine the data from the "LS" field with that in the "SS" field, but sorted by color, such that ALL red shirts are together, regardless of style, ALL green shirts together regardless of style, etc.? Right now, the query sorts by style first (which is determined by the field), then by color in each group. Does this make sense? I tried to write an expression for a new column in hte query, but couldn't get that to work.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining data from two fields in a query (2000-SR1)

    Not sure what you want to do

    To combine two Table Field in a Query

    FieldName:[FirstFieldName]&[SecondFieldName]

    LongSleeve36

    For spaceing
    FieldName:[FirstFieldName]&" "&[SecondFieldName]

    LongSleeve 36

    Hyphen Space
    FieldName:[FirstFieldName]&"-"&[SecondFieldName]

    LongSleeve-36

    FieldName:[FirstFieldName]&" "&"Size-"&[SecondFieldName]

    LongSleeve Size-36

    FieldName is a New Field Name such as Shirts

    Shirts
    LongSleeve Size-36


    To keep the colors together, in your query place the ColorField last or where you want it and sort ascending or what ever you need

    Hope this helps
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Combining data from two fields in a query (2000-SR1)

    Sort by colour first then by style.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining data from two fields in a query (200

    Thanks. That's a good step in the right direction...now my data looks something like this, due to combos and multiple from different styles:

    First color: Second color:
    Black
    Black Orange
    Black Gold
    BlackBlackBlack

    I need to do a count for each possibility-- black/orange, black gold, black (only)--even when there are multiples returned by the query due to the concatenated fields. Is there a way to do a count from the text that will pull them all correctly?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2005
    Location
    Wichita, Kansas, USA
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining data from two fields in a query (200

    Or, now the data is re-organized as follows...how can I count each occurrence of each color?
    First Second Third

    Orange
    Purple/Grey
    Royal Blue
    Grey Grey
    Black
    Black Black Black
    Black/Red
    Black/Orange

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Combining data from two fields in a query (200

    ws,
    I'm not sure whether I understand your question. What do you want to query and where do you get stuck?
    Initially, you wanted to combine the colours of two shirt types... and in your last post you have a three colour combination and there's no shirt type mentioned anymore - how come?
    Can you explain a bit more about the table structure you use? tblName = field1 + field2 + ...
    And what process/shirt business you're involved in? What's this colour combination counting's purpose? Initially I thought you were selling/... always a set of two shirts, a small and a large on each of a deliberate colour - and you needed to count these. In that case, it would have been solved by a rather simple summation query (grouping on LS and then SS, and then counting SS). But apparently that's wrong.
    Hasse

Posting Permissions

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