Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Unanswered: simply impossible

    Hello everyone - I'm drowning in a teaspoonful of water - our company receives items on different dates and in different qties. I just want to know when the first shipment for each item will arrive, and how many will be in the package..

    Here's my data:

    name due date qty
    A 02-Jul-04 8
    A 01-Jul-05 6
    A 03-Mar-04 2
    A 04-Mar-04 1
    A 01-Oct-04 3
    B 02-May-04 3
    B 02-Jun-04 9
    B 05-May-04 8
    B 01-May-04 6
    B 01-Mar-04 4
    C 02-Jun-04 9
    C 03-Jun-04 9
    D 02-May-04 2
    D 03-Mar-04 2
    E 02-May-04 4

    Here's what i want to see in my query:

    A 03-Mar-04 2
    B 01-Mar-04 4
    C 02-Jun-04 9
    D 03-Mar-04 2
    E 02-May-04 4

    I'm reeeaaaally stuck - can anyone help??

    Thanks in advance

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: simply impossible

    This query should return your desired results:

    SELECT Name, MIN(Due_Date), Qty
    FROM TableName
    GROUP BY Name, Qty
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    13
    Hello SCIRROCO, I've tried this, but it returns one record for each unique name-date-qty combination. So instead of one answer per name i.e 5 records, it returns 13 records:

    name MinOfdue date qty
    A 04/03/2004 1
    A 03/03/2004 2
    A 01/10/2004 3
    A 01/07/2005 6
    A 02/07/2004 8
    B 02/05/2004 3
    B 01/03/2004 4
    B 01/05/2004 6
    B 05/05/2004 8
    B 02/06/2004 9
    C 02/06/2004 9
    D 03/03/2004 2
    E 02/05/2004 4

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select YourTable.name, YourTable.[due date], YourTable.qty
    from YourTable
    inner join
    (select name, min(due date) as mindate from YourTable group by name) SubQuery
    on YourTable.name = SubQuery.name
    and YourTable.[due date] = SubQuery.mindate


    I haven't checked the above code for Access SQL compatibility, but you should be able to figure out the methodology used.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Mar 2004
    Posts
    13
    Originally posted by blindman
    select YourTable.name, YourTable.[due date], YourTable.qty
    from YourTable
    inner join
    (select name, min(due date) as mindate from YourTable group by name) SubQuery
    on YourTable.name = SubQuery.name
    and YourTable.[due date] = SubQuery.mindate


    I haven't checked the above code for Access SQL compatibility, but you should be able to figure out the methodology used.
    Thanks blindman. I know v.little about programming or SQL , apart from what I see when i press the 'SQL view' button when I'm in query design view. I think I can manage to make your code match that SQL syntax though. I'll get back to you!!

  6. #6
    Join Date
    Mar 2004
    Posts
    13
    Originally posted by blindman
    select YourTable.name, YourTable.[due date], YourTable.qty
    from YourTable
    inner join
    (select name, min(due date) as mindate from YourTable group by name) SubQuery
    on YourTable.name = SubQuery.name
    and YourTable.[due date] = SubQuery.mindate


    I haven't checked the above code for Access SQL compatibility, but you should be able to figure out the methodology used.
    IT WORKS!!

    Here's the Access SQL that came out (after an hour of trial and error!):

    MainQuery:
    SELECT YourTable.name, YourTable.[due date], YourTable.qty
    FROM YourTable INNER JOIN SubQuery ON (YourTable.[due date] = SubQuery.[MinOfdue date]) AND (YourTable.name = SubQuery.name);

    SubQuery:
    SELECT YourTable.name, Min(YourTable.[due date]) AS [MinOfdue date]
    FROM YourTable
    GROUP BY YourTable.name;

    I would have liked to get the subquery inside the main query which is what i think you meant, but i couldn't see how to do it, so i had to make a seperate query which i called 'subquery'. It may not be as elegant but it works for now! I'll try doing a nested query next.

    Thanks alot blindman!

Posting Permissions

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