Multi-query issue

Discussion in 'Microsoft Access Queries' started by Antavas, Jul 14, 2008.

  1. Antavas

    Antavas Guest

    I have a data table that contains an open date and close date for each entry.
    I've been asked to create a report that does the following 3 things:

    allows a date range for when items are opened (easy enough based off the
    open date)
    allows a date range for when items are closed(also easy because of the
    between [] and [])

    now the real issue I'm having is this third item. Based off of whatever open
    range picked in Item #1 I need to generate a list of issues opened prior to
    whichever start date is chosen and out of those issues I need to show only
    the ones that stayed open past the end date of the range.

    I've gone around this a few different ways and I can't get it to work. I've
    tried to do this as seperate quieries (the open date query and close date
    query have been easy) I can't get this third one to work though. I just want
    to be able to have a query that looks at the date range I put in, gives me
    the cases opened before the start of my selected date and still open at the
    end of my selected range.

    I also need to turn this all into a report (hopefully in one report)

    Can everything be done in 1 query?
     
    Antavas, Jul 14, 2008
    #1
    1. Advertisements

  2. Antavas

    John Spencer Guest

    The criteria should look something like the following.

    WHERE [OpenedDate] <= [Start Date of Period] and [ClosedDate] >= [End Date of
    Period]

    I'm not sure what you are attempting to accomplish from your description.


    John Spencer
    Access MVP 2002-2005, 2007-2008
    The Hilltop Institute
    University of Maryland Baltimore County

    Antavas wrote:
    > I have a data table that contains an open date and close date for each entry.
    > I've been asked to create a report that does the following 3 things:
    >
    > allows a date range for when items are opened (easy enough based off the
    > open date)
    > allows a date range for when items are closed(also easy because of the
    > between [] and [])
    >
    > now the real issue I'm having is this third item. Based off of whatever open
    > range picked in Item #1 I need to generate a list of issues opened prior to
    > whichever start date is chosen and out of those issues I need to show only
    > the ones that stayed open past the end date of the range.
    >
    > I've gone around this a few different ways and I can't get it to work. I've
    > tried to do this as seperate quieries (the open date query and close date
    > query have been easy) I can't get this third one to work though. I just want
    > to be able to have a query that looks at the date range I put in, gives me
    > the cases opened before the start of my selected date and still open at the
    > end of my selected range.
    >
    > I also need to turn this all into a report (hopefully in one report)
    >
    > Can everything be done in 1 query?
    >
     
    John Spencer, Jul 14, 2008
    #2
    1. Advertisements

  3. "Antavas" <> wrote in message
    news:...
    >I have a data table that contains an open date and close date for each
    >entry.
    > I've been asked to create a report that does the following 3 things:
    >
    > allows a date range for when items are opened (easy enough based off the
    > open date)
    > allows a date range for when items are closed(also easy because of the
    > between [] and [])
    >
    > now the real issue I'm having is this third item. Based off of whatever
    > open
    > range picked in Item #1 I need to generate a list of issues opened prior
    > to
    > whichever start date is chosen and out of those issues I need to show only
    > the ones that stayed open past the end date of the range.
    >
    > I've gone around this a few different ways and I can't get it to work.
    > I've
    > tried to do this as seperate quieries (the open date query and close date
    > query have been easy) I can't get this third one to work though. I just
    > want
    > to be able to have a query that looks at the date range I put in, gives me
    > the cases opened before the start of my selected date and still open at
    > the
    > end of my selected range.


    SELECT * FROM YourTable WHERE StartDate < Forms![YourForm]![StartDate] AND
    EndDate > Forms![YourForm]![EndDateRangeClose]

    ?

    HTH;

    Amy
     
    Amy Blankenship, Jul 14, 2008
    #3
  4. Antavas

    Antavas Guest

    Basically my first query allows me to submit a date range for all issue open
    dates using the Between [] and [] criteria. The next step I'm trying to
    accomplish is take the start date of that range and return all issues that
    occurred before that date. These issues must also not have a close date that
    falls within that date range.

    I hope this clarifies my need. I know what I posted before was a bit
    convoluted and thank you for your advice.
     
    Antavas, Jul 14, 2008
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Simon Glencross

    Multi Multi Select List boxes

    Simon Glencross, Jan 25, 2006, in forum: Microsoft Access Queries
    Replies:
    10
    Views:
    280
    Douglas J Steele
    Jan 26, 2006
  2. Rich

    SQL Query Double Counts on Multi-Table Query

    Rich, Jul 5, 2006, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    222
  3. Guest

    Query Design View - multi table query problems

    Guest, Sep 23, 2006, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    362
    Neil Sunderland
    Sep 23, 2006
  4. roccogrand

    Like and Multi-criteria with Multi-Values

    roccogrand, Jul 14, 2008, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    907
    Allen Browne
    Jul 15, 2008
  5. Smacs

    Help resolving multi to multi relationship

    Smacs, Mar 13, 2010, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    162
    XPS350
    Mar 13, 2010
Loading...

Share This Page