Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    9

    Unanswered: Not getting required result !!!

    Hi,
    I am working with a vb application using Access 2003 database.
    My table is like this.

    ID - Auto number
    CaseId - Text
    CaseDate - Date
    Stage - Int

    Sampel data is like this:
    ID CaseId - CaseDate Stage
    1. 101 10/12/2008 3
    2. 102 17/12/2008 2
    3. 103 13/12/2008 2
    4. 101 18/12/2008 4
    5. 108 11/12/2008 1
    6. 107 01/12/2008 1
    7. 102 20/12/2008 3
    8. 103 28/12/2008 3
    9. 107 25/12/2008 2
    10. 101 22/12/2008 5

    Now i am trying to generate a report with the list of caseid's for which case date is not available till 12/12/2008
    (On user input date of 12/12/2008 the list should give caseid's which do not have dates after 12/12/2008)
    with the sample data, i should get only
    5. 108 11/12/2008 1

    like wise, for the input date of 26/12/2008, the report should generate the list of cases with last (the maximum) date entry only
    like
    10. 101 22/12/2008 5
    7. 102 20/12/2008 3
    9. 107 25/12/2008 2
    5. 108 11/12/2008 1

    With my little knowledge in access query , i could not succeed to generate the required output.
    Pls help me in solving the report.

    thanks

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by hbkelkar
    Hi,
    I am working with a vb application using Access 2003 database.
    My table is like this.

    ID - Auto number
    CaseId - Text
    CaseDate - Date
    Stage - Int

    Sampel data is like this:
    ID CaseId - CaseDate Stage
    1. 101 10/12/2008 3
    2. 102 17/12/2008 2
    3. 103 13/12/2008 2
    4. 101 18/12/2008 4
    5. 108 11/12/2008 1
    6. 107 01/12/2008 1
    7. 102 20/12/2008 3
    8. 103 28/12/2008 3
    9. 107 25/12/2008 2
    10. 101 22/12/2008 5

    Now i am trying to generate a report with the list of caseid's for which case date is not available till 12/12/2008
    (On user input date of 12/12/2008 the list should give caseid's which do not have dates after 12/12/2008)
    with the sample data, i should get only
    5. 108 11/12/2008 1

    like wise, for the input date of 26/12/2008, the report should generate the list of cases with last (the maximum) date entry only
    like
    10. 101 22/12/2008 5
    7. 102 20/12/2008 3
    9. 107 25/12/2008 2
    5. 108 11/12/2008 1

    With my little knowledge in access query , i could not succeed to generate the required output.
    Pls help me in solving the report.

    thanks
    Hi hbkelkar,

    I am trying to figure out exactly what you are doing and trying to achieve. In your first example, if the user inputs the date 12/12/2008 ONLY #5 will be the result, with that date 11/12/2008? Then in your next example the user will input 26/12/2008 and that will result in 22/12/08, 20/12/08, 25/12/08 and 11/12/08. Then 11/12/08 is the same result you got in the first example. Maybe it's late but I can't figure out how you're doing that.
    Now are you wanting to search for dates up to and including 12/12/08 in your first example? In your second example are you wanting everything AFTER 12/12/08 or BEFORE?

    BUD

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would have thought a simple where clause would satisfy that requirement
    eg where casedate<= <*mytargetdate*>
    so that could be where casedate<="#12/13/2008#"

    however there is one possible problem I can forsee, depending on how you set the case date in the first place. if you used date() as the value or you specified a date you should be OK, if you used now() to specify the date you have a time element.

    date() returns the current system date
    now() returns the current system date AND current system time

    if you used now() then you need to do a bit more manipulation
    use the dateadd function to add 1 day to your target date and then look for values that are ONLY less than the new target date
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Posts
    9
    Hi Bud,
    In my first example, as the date is 12/12/2008, i should only get #5 which has the casedate 11/12/2008 and no other entries exists with casedate greater than 12/12/2008.

    In the second example 26/12/2008, i should only get 4 DISTINCT records as these cases do not have casedate >= 26/12/2008.

    Here i need only the records with the heighest casedate, for casesdate <26/12/2008

    healdem : I used the same condition in my query which returned all the rows which fall below 26/12/2008 (as in second example).
    But i need only Single row for each case with the heighest casedate entry.

    I hope i have explained my problem clearly.

    thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so use a group by sub clause
    probably group by Case ID

    you may need a....
    select top stage
    group by case id

    it may be that you need a subquery
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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