Results 1 to 4 of 4
  1. #1
    anjemalo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    3

    How to make a complex querie from columns to one row

    Hello:


    I need to make a report and I don't find the way to achieve this:

    My querie has this fields and data:

    sample:

    [user] [date] [hour] [status]

    -1- - 1/5- -9:00- -In

    1 1/5 10:05 Break

    1 1/5 10:30 Break

    1 1/5 14:30 Break

    1 1/5 15:30 Break

    1 1/5 18:00 Out

    ...

    An I need in this format in one line grouped by day and user

    for example:



    user -- date -- In --Break-- Break-- Break -- Break --Out

    -1- ---- 1/5 --- 9:00 -- 10:05-- 10:30 -- 14:30-- 15:30 --18:00
    -2 ---- 1/5 ...
    -3 ...

    I attach a document where you can see it better.


    Can you help me please?

    Many many thanks
    Attached Thumbnails Attached Thumbnails Querie.jpg  

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you need a crosstab query. Maybe try the query wizard for this if not familiar with them.
    They don't make good queries for reports if that's what you are doing - not without special tweaking.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    anjemalo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    3
    yes. I know crosstab. the problem is that I have columns name repeated and dont separate them into single columns but grouped. the columns are: Break There are four with several hours. thank you

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider:

    TRANSFORM Max(Table1.hour) AS MaxOfhour
    SELECT Table1.user, Table1.date
    FROM Table1
    GROUP BY Table1.user, Table1.date
    PIVOT [status] & IIf([status]='break',DCount("*","Table1","status='break' AND user=" & [user] & " AND ID<" & [ID] & " AND [date]=#" & [date] & "#")+1,"") In ("In","Break1","Break2","Break3","Break4","Out" );

    The IN() function to specify field headings is optional or you can add more BreakX for however many breaks are possible. Including this forces the In header to be first in output.

    Note the use of unique record identifier field ID - autonumber. Assuming date and hour are date/time fields, alternative: & " AND [date]+[hour]<#" & [date]+[hour] & "# AND

    Advise not to use reserved words as names for anything. Date and Hour are reserved words. IN is also a reserved word and might consider using Begin or Start as data in table or an alias in query so IN does not become a column header. Or not. Might not encounter any issue. Just keep in mind in case.
    Last edited by June7; 06-01-2019 at 12:06 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Querie Understanding
    By Joshwenny in forum Access
    Replies: 3
    Last Post: 07-16-2018, 06:13 PM
  2. How to make complex queries editable?
    By Thomasso in forum Queries
    Replies: 9
    Last Post: 01-31-2017, 03:42 PM
  3. how to make columns in ListBox
    By LUTINO in forum Programming
    Replies: 7
    Last Post: 09-10-2016, 02:12 AM
  4. Querie Help
    By Jramosent in forum Queries
    Replies: 1
    Last Post: 09-18-2013, 02:22 PM
  5. querie problem
    By TIMT in forum Queries
    Replies: 2
    Last Post: 05-31-2009, 10:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums