Results 1 to 8 of 8
  1. #1
    sultanuzzaman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2020
    Posts
    3

    Report that has columns as per the row valuea

    I need to generate a report like the following



    ITEM DATE CH-NO: D.ROSE LT GREY SEND RED BLCK
    TOTAL
    S/THREAD REQUIRD 143 557 337 40 980 2057
    RCVD 21.03.2020 3704 143 557 337 40 980 2057
    RCVD 0
    TOTAL 0 143 557 337 40 980 0 2057
    BALANCE 0 0 0 0 0 0 0 0
    Main label REQUIRD 35816 35816
    RCVD 23.03.2020 5441 4511
    RCVD 18.05.2020 5478 27115 27115
    RCVD 18.05.2020 5476 4190 4190
    TOTAL 0 35816 0 0 0 0 0 35816
    BALANCE 0 0 0 0 0 0 0 0
    CARE LABEL REQUIRD 132582 132582
    RCVD 21.03.2020 3704 30640
    RCVD 22.03.2020 5428 74480 74480
    RCVD 22.03.2020 5436 27462 27462
    TOTAL 0 132582 0 0 0 0 0 132582
    BALANCE 0 0 0 0 0 0 0 0
    BARCODE LABEL REQUIRD 52804 52804
    RCVD 21.03.2020 3704 12014
    RCVD 22.03.2020 5428 13010 13010
    RCVD 22.03.2020 5436 9154 9154
    TOTAL 0 34178 0 0 0 0 0 34178
    BALANCE 0 -18626 0 0 0 0 0 -18626

    From the source excel sheet as below

    Order No Item Name Color Req qty Rcvd Qty Bal to Rcv Rcv Date Rcv Invoice. Uunit
    NHK/19-20/PRKG/80 Hangers BLACK 89794 6000 83794 01/06/2020 4599/01 Pieces
    NHK/19-20/PRKG/80 Hangers BLACK 89794 26400 63394 02/06/2020 4599/02 Pieces
    NHK/19-20/PRKG/80 Adhesive Paper WHITE 1258 1258 0 02/06/2020 Gs/2020/306 Pieces
    NHK/19-20/PRKG/80 Snap Button SILVER 32006 32006 0 11/06/2020 B0001801 Pieces
    NHK/19-20/PRKG/80 Snap Button SILVER 359176 100800 258376 13/06/2020 B0001849 Pieces
    NHK/19-20/PRKG/80 Sewing Thread(M) GREY 1108000 1108000 0 15/06/2020 29948 Mts
    NHK/19-20/PRKG/80 Label - Care BLACK 21275 4000 17275 16/06/2020 419 Pieces
    NHK/19-20/PRKG/80 Label - Care BLACK 16446 4000 12446 16/06/2020 419 Pieces
    NHK/19-20/PRKG/80 Label - Care BLACK 9409 9409 0 16/06/2020 419 Pieces


    Access report source excel file and report format.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Suggest removing spaces and punctuation from field names (underscore only exception).

    What field is CH-NO from?

    A CROSSTAB query can pivot on Color field and sum quantity.
    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.

  3. #3
    sultanuzzaman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2020
    Posts
    3
    Please see the excel files in attachment. Report format is in LPP ACCESSORIES STATUS- 2020.xlsx and the data source is in PMIR---800.xlsx. I tried the cross tab but it was not as per the format.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I did look. That's why I suggested CROSSTAB.

    CROSSTAB pivots the data. Then you have to use that query as RecordSource for a report. Use report Sorting & Grouping features with aggregate calculations in group and report footers.
    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.

  5. #5
    sultanuzzaman is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2020
    Posts
    3
    I tried nut failed to get a look like this

    Click image for larger version. 

Name:	Untitled.png 
Views:	15 
Size:	9.3 KB 
ID:	42300

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Database doesn't work like a spreadsheet. Getting an Access report to look close to that could be quite an effort. If you feel it is beyond your abilities, suggest you find a professional consultant and pay them to build.
    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.

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Do you only ever deal with the following Colours?

    Color
    BLACK
    WHITE
    SILVER
    GREY
    as per approval
    NUDE
    DARK GREEN

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    The nearest you will get to the layout you need is as shown in the attached Report.
    Attached Files Attached Files

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

Similar Threads

  1. Columns on a report
    By dwaterman in forum Reports
    Replies: 1
    Last Post: 03-25-2016, 07:35 PM
  2. Replies: 2
    Last Post: 11-12-2014, 02:19 PM
  3. 2 columns on report
    By banny04 in forum Reports
    Replies: 2
    Last Post: 09-07-2011, 05:08 AM
  4. report has several columns
    By Fretless in forum Reports
    Replies: 0
    Last Post: 03-04-2011, 03:31 PM
  5. help with columns in report
    By bjsbrown in forum Reports
    Replies: 8
    Last Post: 06-14-2010, 09:17 PM

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