Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2005
    Location
    Mamaroneck, New York, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Group by query (A2K/9.0.4402 SR-1)

    I am tracking engineering drawings through development from layout to release. There are 8 phases of the cycle and 20 "locations" within those phases. 1 phase has 2 locations, 1 has 3, and 1 has 10. The remainder are 1 for 1. I have a form that displays the 8 phases and their start and finish dates. For the 1 for 1 phases, that's not a problem. To accommodate the others, I have a group query for all the phases, showing the Min of start dates (this is fine), and the Max of finish dates, which is a problem.

    For those phases with multiple locations, there is a "final" location whose finish date is the only determinant of whether the phase is finished. How do I show the Min(Start) and the finish of the "final" location?

    To make the problem even more "interesting", the "final" location will not even be represented in the source table, tblDates, until it is started. So I have to show a null for it's phase finish date, even though it's location doesn't yet exist in the source table.

    I have dumped sql and sample data into the attached excel workbook. qryActualDates is a subquery for qryActPhase, which is the datasource for my continuous subform.

    Thanks for your help!

    Bill

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Group by query (A2K/9.0.4402 SR-1)

    It would have been MUCH easier if you had attached the database instead of letting us reconstruct it from the spreadsheet.

    I don't think you can get the start and finish dates in one query. See attached database - it contains a series of queries to obtain the result.

  3. #3
    New Lounger
    Join Date
    Nov 2005
    Location
    Mamaroneck, New York, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group by query (A2K/9.0.4402 SR-1)

    I apologize for the Excel workbook. I don't know why it didn't occur to me to send a stripped down version of my db. Next time.

    Thanks for you suggestions. I will test it out on my database.

    Bill

Posting Permissions

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