Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2003
    Posts
    67

    Question Unanswered: Sort query on more than one column

    I've created a query on which to base a report showing a three-year audit schedule. Some business areas (AAA, etc.) will be audited once in the three-year period, others twice, others not at all (thus the blank
    fields).

    Can I get the blanks at the bottom of each column without the dates being sorted backward (Dec-Jan)? Also, how do I eliminate the records that contain no date? For records with both 2004 and 2006 dates, I only need the 2004 dates in order, and obviously, the business areas are not involved in the sort at all.

    Following are the current results:

    ...........2004........2005.........2006

    AAA.....(blank).....(blank)......(blank)
    BBB.....(blank).....(blank)......(blank)
    CCC....(blank).....(blank)......(blank)
    DDD....(blank).....(blank)......(blank)
    EEE......(blank).....(blank)......01/01/06
    FFF.......(blank).....(blank) .....03/01/06
    GGG.....(blank).....(blank)......07/01/06
    HHH......(blank).....(blank).....12/01/06
    III........(blank).... 01/01/05....(blank)
    JJJ........(blank).....03/01/05....(blank)
    KKK......(blank).... 07/01/05 ...(blank)
    LLL. ....(blank).... 12/01/05....(blank)
    MMM....01/01/04...(blank).......01/01/06
    NNN.....03/01/04....(blank)......(blank)
    OOO....07/01/04....(blank)........07/01/06
    PPP......12/01/04....(blank).......12/01/06

    What I'm looking for is this:

    MMM....01/01/04...(blank).......01/01/06
    NNN.....03/01/04....(blank)......(blank)
    OOO....07/01/04....(blank)........07/01/06
    PPP......12/01/04....(blank).......12/01/06
    III........(blank).... 01/01/05....(blank)
    JJJ........(blank).....03/01/05....(blank)
    KKK......(blank).... 07/01/05 ...(blank)
    LLL. ....(blank).... 12/01/05....(blank)
    EEE......(blank).....(blank)......01/01/06
    FFF.......(blank).....(blank) .....03/01/06
    GGG.....(blank).....(blank)......07/01/06
    HHH......(blank).....(blank).....12/01/06

    with these records eliminated:
    AAA (blank) (blank) (blank)
    BBB (blank) (blank) (blank)
    CCC (blank) (blank) (blank)
    DDD (blank) (blank) (blank)

    Any help would be greatly appreciated!
    Last edited by Jaycee; 09-11-03 at 10:17.

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Lightbulb This is it

    WHERE (YOUR criteria)

    AND (((not isnull(2004) AND not isnull(2005) AND not isnull(2006))))

    Order BY 2004 , 2005, 2006 desc



    Expl.


    AND ((not isnull(2004) AND not isnull(2005) AND not isnull(2006)))
    This will exclude the "rows" that have 2004 - 2006 blank
    Be sure to use the extra ( and ) minimal twice



    Order BY 2004 , 2005, 2006 desc
    this wil order your result descending USE asc if you want to revers it
    It will first look at the 2004 then 2005 and then 2006
    If you wanna switch just put them in an other order

  3. #3
    Join Date
    Sep 2003
    Posts
    67

    Re: This is it

    Thanks for your quick response!

    Where do I enter this code -- in the query fields, in the report?? Sorry, I'm new at this level of complexity.


    Originally posted by Marvels
    WHERE (YOUR criteria)

    AND (((not isnull(2004) AND not isnull(2005) AND not isnull(2006))))

    Order BY 2004 , 2005, 2006 desc



    Expl.


    AND ((not isnull(2004) AND not isnull(2005) AND not isnull(2006)))
    This will exclude the "rows" that have 2004 - 2006 blank
    Be sure to use the extra ( and ) minimal twice



    Order BY 2004 , 2005, 2006 desc
    this wil order your result descending USE asc if you want to revers it
    It will first look at the 2004 then 2005 and then 2006
    If you wanna switch just put them in an other order [/SIZE][/QUOTE]

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    if your report is based on the query youl have to open the query in design mode then open the query in SQL mode (in the upper left corner just under file - do not click on file)


    Be sure to delete the ; if you want to put the code after this sign


    SO youll get


    SELECT A, B, C, D

    From ((A.A
    LEFT JOIN B.A ON A.A = B.A)
    RIGHT JOIN C.B. ON B.B =C.B)
    INNER JOIN D.C. ON C.C =C.C

    WHERE (Year = 2000) AND (((not isnull(2004) AND not isnull(2005) AND not isnull(2006))))

    ORDER BY 2004 , 2005, 2006 desc

    Save your query

  5. #5
    Join Date
    Sep 2003
    Posts
    67

    Date Sort Query

    Here's what the SQL view currently has for this query:

    SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006
    FROM RiskAnalysisDetails
    ORDER BY RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006;

    Does your code need to go after this, or replace it? Also, I'm not sure how the A, B, C, D fits in.

    Thanks for your patience!

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Cool Copy / Paste (instead of other code)

    SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006


    FROM RiskAnalysisDetails

    WHERE (((not isnull(RiskAnalysisDetails.Date2004)
    AND not isnull(RiskAnalysisDetails.Date2005)
    AND not isnull(RiskAnalysisDetails.Date2006))))


    ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006 asc

  7. #7
    Join Date
    Sep 2003
    Posts
    67
    First, thanks for your response on the decimal question -- your solution worked (of course)!

    Back to the query:

    I copied/pasted your code over the existing code. I saved the query, then ran it, but no results were produced. When I looked at the SQL view again, it had changed to:

    SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006

    FROM RiskAnalysisDetails

    WHERE (((IsNull([RiskAnalysisDetails].[Date2004]))=False) AND ((IsNull([RiskAnalysisDetails].[Date2005]))=False) AND ((IsNull([RiskAnalysisDetails].[Date2006]))=False))
    ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006;

    ...and back in Design view, three columns containing the Where statements had been added to the end.

    Where'd I go wrong this time?? Thanks!!!

  8. #8
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    a question

    Where you say Blank is

    the date 00/00/00 00:00:00
    or is the field realy blank
    or is the value NULL

  9. #9
    Join Date
    Sep 2003
    Posts
    67

    Question response

    The field is really blank.

  10. #10
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    That changes it a little bit

    Does this do what you want :



    SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity,
    RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006


    FROM RiskAnalysisDetails

    WHERE ((((RiskAnalysisDetails.Date2004 <> "")
    AND (RiskAnalysisDetails.Date2005 <> "")
    AND (RiskAnalysisDetails.Date2006 <> ""))))


    ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006 asc

  11. #11
    Join Date
    Sep 2003
    Posts
    67

    Error message

    I get an error message: Data type mismatch in criteria expression.

  12. #12
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Question This has to work !!

    If this doen't work can you zip your table and attach it here
    PS only that 1 table

    SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006


    FROM RiskAnalysisDetails

    WHERE ((not isnull(RiskAnalysisDetails.Date2004))
    AND (not isnull(RiskAnalysisDetails.Date2005))
    AND (not isnull(RiskAnalysisDetails.Date2006)))


    ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006 asc

  13. #13
    Join Date
    Sep 2003
    Posts
    67

    World's most stubborn query

    Sorry, the query returned no results. I've attached the file -- the zipped db, since I don't know how to send only a portion of it. Please don't laugh too hard at my structure and design; I've still much to learn.

    The table is RiskAnalysisDetails and the query is 04-06AuditScheduleQuerybyDate. Also, the report 04-06AuditScheduleRpt shows what I'm trying to fix; i.e., the blanks are at the top of each column instead of at the bottom and the records with no dates are not being excluded.

    Thanks again for your patience and help!!!

    Jaycee

    BTW, I'm also doing battle with the report 04-06AuditScheduleIntervals, trying to get the intervals between audits to calculate correctly in the report, as they do in the form Risk Analysis/AuditSchedule. Another guru was helping me with it, but I think he's given up on me. If the solution comes to you without taking too much of your time, please enlighten me! Thanks!
    Attached Files Attached Files

  14. #14
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Arrow Lets try this first

    Could you change the AND into OR

    So

    SELECT RiskAnalysisDetails.[2003RiskCategory], RiskAnalysisDetails.Team, RiskAnalysisDetails.AuditEntity, RiskAnalysisDetails.Office, RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006
    FROM RiskAnalysisDetails
    WHERE ((not isnull(RiskAnalysisDetails.Date2004))
    OR (not isnull(RiskAnalysisDetails.Date2005))
    OR (not isnull(RiskAnalysisDetails.Date2006)))
    ORDER BY RiskAnalysisDetails.Date2004, RiskAnalysisDetails.Date2005, RiskAnalysisDetails.Date2006 asc

    BTW i cant open the .mdb
    Coud you copy a range from your table into xls or something

    Thx

  15. #15
    Join Date
    Sep 2003
    Posts
    67

    Getting closer ....

    The last changes produced results and excluded the records with no dates, but the columns are still upside down. I've attached a Word document showing the table and query results. Hope this helps!

    Thanks much!
    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
  •