Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: Access Query to return a list for a month

    Hi, I have a problem that is driving around the bend! I know it should be simple but for the life of me I can't resolve it. I'm not brilliant at SQL but I am getting my head around it slowly!

    In part of my Access DB I have the following requirement.

    Item, Start Date, Finish Date for which I need to return a list for each month.
    For instance the Item "Mayonnaise" has a start date of 01/01/12 and a finish date of 30/04/12 and therefore the item will appear in the January list and no other. If however if the we are already in February then it will return the item in Februarys list because January has passed.

    So in principle the user will receive a prompt to enter the required month to return the list for that month which will normally be the start date month.

    Any advice and guidance will be very much appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so your where clause needs to include all items which start on or before the current date, and all items which on or after the current end date

    if you want month by month report then it will need to be a separate query for each month

    select my, column, list from my table
    where startdate <= now()
    and finishdate > format(now(),"#mm/01/yyyy#"
    ... (so thats any products with a start date of less than now, and a finish date that is greater than the first of this month)

    to find last months products we 'just' need to tinker with the date
    where startdate <= dateadd(m,-1,now())
    and finishdate > format(dateadd(m,-1,now()),"#mm/01/yyyy#"

    und so weiter
    where startdate <= dateadd(m,-2,now())
    and finishdate > format(dateadd(m,-2,now()),"#mm/01/yyyy#"
    ..y'get the picture

    ..you could decide you want to include any product whose start date is less than the end of the current month, rather than less than the current date
    where startdate < format(dateadd(m,1,now()),"#mm/01/yyyy#")
    and finishdate > format(dateadd(m,-1,now()),"#mm/01/yyyy#"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by tadiw View Post
    Item, Start Date, Finish Date for which I need to return a list for each month.
    For instance the Item "Mayonnaise" has a start date of 01/01/12 and a finish date of 30/04/12 and therefore the item will appear in the January list and no other.
    Why ? I'm not sure to understand. We're in July, so why would the item only appear for January and not for February, March and April?
    Have a nice day!

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And since you're working with non-US date formats, you'll do well to read and bookmark Allen Browne's article on the subject:

    Microsoft Access tips: International Dates in Access

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jul 2012
    Posts
    2
    Thank you all for your replies.

    healdem. Thank you for you answer. I will give that a try and see how I get on.

    Sinndho. The item can only appear in one list, otherwise it would indicate I have more than one of the unique individual item. For instance if it appeared on Jan & Feb report then one of them would actually be a duplicate and not the real item.

    Missinglinq: Thank you for the article link - could save me a few headaches!

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by tadiw View Post
    Sinndho. The item can only appear in one list, otherwise it would indicate I have more than one of the unique individual item. For instance if it appeared on Jan & Feb report then one of them would actually be a duplicate and not the real item.
    Quote Originally Posted by tadiw View Post
    For instance the Item "Mayonnaise" has a start date of 01/01/12 and a finish date of 30/04/12 and therefore the item will appear in the January list and no other. If however if the we are already in February then it will return the item in Februarys list because January has passed.
    Sounds like you are having some conflicts in your requirements. The part in red would have put mayo in the April list but your statement says January

    Steve

Posting Permissions

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