Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Posts
    118

    Question Unanswered: Is it possible to combine 2 columns into 1?

    I have 2 columns identifying the records as being old or new.


    old New

    1 0
    1 0
    1 0
    0 1
    0 1


    What I'm trying to do is sum and combine these so i can show the number of old and new records in a report and also be able to group by them.

    so I need something like this

    age

    3
    3
    3
    2
    2

    Anyone got any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select old + new as age
    from yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    It's not that easy, those fields are generated in a query, I cant select from thesame query im outputing to. It all needs to be in 1 column and added up correctly.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "I cant select from thesame query im outputing to"

    no, of course not

    i would like to know what "outputing to" means


    "It all needs to be in 1 column"

    old + new as age is one column

    you should show your query if it is more complex than your example
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    118
    Here is the query:

    SELECT KE0G_Drawings.DwgTag, KE0G_Drawings.DwgSht, KE0G_Drawings.DwgTitle1, KE0G_Drawings.DwgRevInt, KE0G_Drawings.DwgIssue, KE0G_Drawings.DwgType, KE0G_Drawings.DwgCommnt,
    DateDiff("y",[KE0G_Drawings].[dwgIssue],Now()) AS DaysDifference,
    KE00_Disciplines.Description, KE0G_Drawings.DwgDiscipline,
    IIf([DaysDifference]<14,"Less Than 2 Weeks","More Than 2 Weeks") AS age,
    IIf([DaysDifference]<14,1,0) AS ageLessThan,
    IIf([DaysDifference]>=14,1,0) AS ageMoreThan
    FROM KE00_Disciplines INNER JOIN KE0G_Drawings ON KE00_Disciplines.DisciplineCode = KE0G_Drawings.DwgDiscipline
    WHERE (((KE0G_Drawings.DwgRevInt) Like "p%") AND ((KE0G_Drawings.DwgIssue) Is Not Null))
    GROUP BY KE0G_Drawings.DwgTag, KE0G_Drawings.DwgSht, KE0G_Drawings.DwgTitle1, KE0G_Drawings.DwgRevInt, KE0G_Drawings.DwgIssue, KE0G_Drawings.DwgType, KE0G_Drawings.DwgCommnt, KE00_Disciplines.Description, KE0G_Drawings.DwgDiscipline
    ORDER BY DateDiff("y",[KE0G_Drawings].[dwgIssue],Now()) DESC;


    I would really like to sum the iif expressions, but it doesent seem to do anything (i still end up with 1 and 0 in the fields and not the sum of all 1s and 0s).

    And I also need those 2 expressions in 1 column, in this query (cant use a second query for 1 report).

    The result needs to display in a sub group header "age" (hence the need to have everything in 1 columns in the original query).

    I also cant use ageLessThan + ageMoreThan since it will mix all the 1s together (need to add them all up first).
    Last edited by YevSnow; 04-13-04 at 10:38.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this looks really familiar

    what do you mean by "sum the iif expressions" -- horizontally or vertically?

    in this thread, you wanted old + new as age which adds values across each row, but in your other thread, you wanted SUM() with GROUP BY, which sums values in a column across a number of rows

    "I also cant use ageLessThan + ageMoreThan since it will mix all the 1s together (need to add them all up first)."

    i am, like, so totally confused about what you really want

    how about a few sample rows, all relevant columns, and a sample of expected output
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Posts
    118


    Sorry, this report is a bit confusing.


    In the previous thread I couldnt get sum to work at all. And after I did, it didnt do anything.

    Basically the report goes like this

    Report Header
    -------------------------------------
    Title
    total records counter
    -------------------------------------
    Page Header

    doc type, sheet num, doc title.......
    -------------------------------------
    Age Header

    Age field

    this is the first if statement IIf([DaysDifference]<14,"Less Than 2 Weeks","More Than 2 Weeks") AS Age

    <---- Here I need a counter of all the documents in this category

    ------------------------------------
    Detail

    All the data from the query is here

    -----------------------------------
    Age footer
    -----------------------------------
    Page footer
    -----------------------------------
    Report footer
    -----------------------------------


    As you can see the only fields relevant to this counter is DaysDifference, which is calculated from the DateIssued field.

    I cant use 2 separate counters because it is in the age header and it would defeat the whole point of the exersise.

    And to have just 1 counter I need ready made values in a column to display.

    By counter I mean a field that displays the number of records per category.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what exactly is a category?

    doc type, sheet num, doc title....... ?

    these are the columns in your GROUP BY?

    you cannot have a count of the number of rows in a group (category) and at the same time, print the detail rows in a group

    well, technically, you can, but it requires some real fancy footwork

    so basically you can have two age headers, one after the other, and for each one, either count or list the detail rows, but not both

    at least, not in the query, perhaps in the report???

    i still don't get it, i guess
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2004
    Posts
    118
    Category is the group, in this case Age.

    Yea I already though about splitting the Age group up into 1 less than and 1 more than, but it seems like a step backwards to me...

    In theory I can create a really complicated SQL statement as RowSource for a text box....

    I'll give that a try now, I'll be very suprised if this works.

  10. #10
    Join Date
    Mar 2004
    Posts
    118
    it didnt work...

    Used this select statement in a list box:


    SELECT KE0G_R3.ageLessThan, KE0G_R3.ageMoreThan, Sum(KE0G_R3.ageLessThan) AS SumOfageLessThan, Sum(KE0G_R3.ageMoreThan) AS SumOfageMoreThan, [SumOfageLessThan]+[SumOfageMoreThan] AS ageCount, KE0G_R3.age
    FROM KE0G_R3
    GROUP BY KE0G_R3.ageLessThan, KE0G_R3.ageMoreThan, KE0G_R3.age;

    KE0G_R3 is the query I am using for the report.

    I think I may have to give up on it...

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'd love to help you further but you aren't answering any of my questions, and meanwhile by writing other queries you are doing other things i cannot follow, e.g. how am i supposed to know why KE0G_R3 isn't working if i cannot even see KE0G_R3

    anyhow, unless you answer some of my earlier questions, i shall have to let you carry on on your own
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2004
    Posts
    118
    I posted it... ke0g_r3 is the first query i posted

  13. #13
    Join Date
    Mar 2004
    Posts
    118
    I will try to answer your questions as best I can.



    There is 1 group on this report, it is Age.

    Age is devided into 2 categories: Less than 14 days and More than 14 days.

    Q: i would like to know what "outputing to" means

    A: It means displaying the output on a report. In this case I am outputing from KE0G_R3 ( the big query I posted erlier)


    Q: what do you mean by "sum the iif expressions" -- horizontally or vertically?

    A: The MoreThan and LessThan expressions return a 1 or a 0 if the condition is met. In this case the condition is < || >= 14 days. The Days field is calculated from the DateIssued field. Since the entire objective is to count the number of records, every time a "1" is returned it needs to be added to the total records for that group. So, the report has a total of 10 records, and 6 return "1" it needs to count them and display "6" in the appropriate category (more than or less than, whichever it happens to apply to).

    Q: what exactly is a category?

    A: A category is either "Less than 14 days" or "More than 14 days" in the Age group.

    Q: doc type, sheet num, doc title....... ? these are the columns in your GROUP BY?

    A: Yes, for this problem this data is not relevant, but it is what the report is trying to sort.

    Q: so basically you can have two age headers, one after the other, and for each one, either count or list the detail rows, but not both. At least, not in the query, perhaps in the report???

    A: Having 2 age headers is a possibility, but then I might as well put 2 counters on the report header. Because every time there is a new age category I would have to create a new age header. I have tried to do this as an SQL string in a list box, which i posted above, but that returns "0".


    Thanks for all your help thus far

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here is the best i can do:
    Code:
    select Dr.DwgTag
         , Dr.DwgSht
         , Dr.DwgTitle1
         , Dr.DwgRevInt
         , Dr.DwgIssue
         , Dr.DwgType
         , Dr.DwgCommnt
         , DateDiff("y",Dr.DwgIssue,Now()) as DaysDifference
         , Di.Description
         , Dr.DwgDiscipline
         , IIf([DaysDifference]<14
                ,"Less Than 2 Weeks" 
                ,"More Than 2 Weeks") as age
         , count(*)
      from KE00_Disciplines Di
    inner 
      join KE0G_Drawings Dr
        on Di.DisciplineCode 
         = Dr.DwgDiscipline
     where Dr.DwgRevInt like "p%" 
       and Dr.DwgIssue is not null
    group 
        by Dr.DwgTag
         , Dr.DwgSht
         , Dr.DwgTitle1
         , Dr.DwgRevInt
         , Dr.DwgIssue
         , Dr.DwgType
         , Dr.DwgCommnt
         , DateDiff("y",Dr.DwgIssue,Now()) 
         , Di.Description
         , Dr.DwgDiscipline
         , IIf([DaysDifference]<14
                ,"Less Than 2 Weeks" 
                ,"More Than 2 Weeks")
    order 
       by Dr.DwgTag
         , Dr.DwgSht
         , Dr.DwgTitle1
         , Dr.DwgRevInt
         , Dr.DwgIssue
         , Dr.DwgType
         , Dr.DwgCommnt
         , DateDiff("y",Dr.DwgIssue,Now()) desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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