Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    6

    Unanswered: Help with dates in query

    I have had great success searching in the past for solutions but I am stumped by this one.

    I have a listing of people as well as their start and end dates in separate fields. The nature of the data could have one line with John Doe and start date of 1/1/12 and End date of 1/8/12 then the same John Doe has a new line with start date of 1/9/12 and end date of 1/15/12.

    I can get a GROUP BY to work where I show John Doe of 1/1/12 and end date of 1/15/12 with summary lines of $$ from other fields in the data.

    My issue is say John doe has a start date of 1/1/12 and end date of 1/8/12 then another line with start date of 1/20/12 and end date of 2/1/12. I would want those 2 lines to be separate in the query result unlike the first example where one line would adequately cover the time period.

    I used query builder for the first example with First on start date and Last on end date with a sum for the $$ figure in another field but it ignores the gaps in the second example.

    Help!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You already found out that by using GROUP BY on the name, FIRST and LAST on the dates, and SUM on the $, that you will wind up with one composite record. You want multiple records; the solution that's obvious to me is to use GROUP BY on all the fields. Alternatively, turn off the TOTALS query symbol, and run a straight SELECT query. Then you'll simply get each record separately.

    Sam

  3. #3
    Join Date
    May 2012
    Posts
    6
    I see what you are saying but what I want is each record spearate when they are not consecutive on dates.

    Data
    Name / Start Date / End Date / Dollars
    John Doe 1/1/12 1/5/12 $5
    John Doe 1/6/12 1/12/12 $7
    John Doe 1/20/12 2/1/12 $ 6


    I would want that to show

    John Doe 1/1/12 1/12/12 $12
    John Doe 1/20/12 2/1/12 $6

    Any ideas if this is possible?

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Not in a query, unfortunately. You would have to analyze it in VBA and then put each new record in a new table. Not the simplest way to do things.

    Sam

  5. #5
    Join Date
    May 2012
    Posts
    6
    I assumed it may be tough to do in query form. Thanks for the help!

Posting Permissions

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