Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: Multiple rows into single row

    Hello there.

    I am facing a problem joining two rows into one. Here is the structure of my table:

    ID Code DATE TIME
    ==================================
    01 ABC-1 01/01/2009 06:00 AM
    02 ABC-1 01/01/2009 10:00 PM


    Here is what I need the output for my query to be:

    Code DATE MIN(TIME) MAX(TIME)
    ========================================
    ABC-1 01/01/2009 06:00 AM 10:00 PM

    There are hundreds of records like ID: 1 and 2. but what I need for now is MinTIme and MaxTime record to be linked to one seperate query or view for the records having SAME DATE. I don't want to do this using table dump method.

    Is there any other mothod?

    Kindly reply...

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT     CODE, [DATE], MIN([TIME]) AS [MIN(TIME)], MAX([TIME]) AS [MAX(TIME)]
    FROM    meTable
    GROUP BY CODE, [DATE]
    BTW - these are dreadful column names:
    List of reserved words in Access 2002 and in later versions of Access
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2009
    Posts
    6

    same values coming for min and max

    Sorry dear,

    same values are coming in both fields min and max using SQL query:

    SELECT Srno, [LogDATE], MIN([LogTime]) AS [MIN(LogTime)], MAX([LogTime]) AS [MAX(LogTime)]
    FROM Log
    GROUP BY Srno, [LogDATE];
    Attached Thumbnails Attached Thumbnails query_result.bmp  

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your example in post #1 does not then properly match your actual data and table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2009
    Posts
    6
    Sorry for that, but is there any way to get what I need? I need minimum value of time for that code on one day as "A" and maximum value for time for that code on same day as "B". in the end, i need two fields Min and Max in one row with date field

    please note that these are actually logs generated by system and i want to merge the max and min of them to one row grouped by date and then code

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Redo your first post but this time use some real data and show what you want the result to be. I have no idea if what you want is possible because you have not yet shown us what your starting point is and what your desired end is.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2009
    Posts
    6

    Lets start from the beginning

    Ok let me start from the beginning. I have a table under ACCESS named as LOGS. Following is the structure of the table:

    ID_______EMP_CODE_______TIME_LOG
    ======================================
    234______007_____________01/02/09 07:00 AM
    345______007_____________01/02/09 17:30 PM

    What i want is, i need a query to run which should appear to me as:


    ID_____________EMP_CODE_______IN TIME_____________OUT TIME
    ================================================== ========
    234____________007_____________01/02/09 07:00 AM___01/02/09 17:30 PM

    Currently logs are dumped into this table each log as a row. There are maximum two logs per date and I want to merge them into one.

    Means the query should return me two records in one or in simple way: It should filter the emp code (007) on date (01/02/09) and return this emp minimum time (time in) and maximum time (time out) in two fields and this "view merge" should be in one row so I can use in reports.

    One more thing is that what can be done if more than two logs exists for same emp on same day

  8. #8
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    what I would do is

    make a query show only the start times group by emp [QUEMPSTART]

    then

    make and other query only showing the End times group by emp [QUEMPEND]

    then

    join
    [QUEMPSTART] and [QUEMPEND] by the emp
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  9. #9
    Join Date
    Feb 2009
    Posts
    6
    Thanks alot. It worked out for me.

Posting Permissions

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