none
Query group before and has numbered in access 2003 ? RRS feed

Answers

  • I have not looked at your file, but on the basis of the sample data you have posted I'd envisage a query along these lines

    SELECT
        (SELECT COUNT(*)
         FROM
            (SELECT DISTINCT mavach
             FROM YourTable) AS T2
             WHERE T2. mavach <= T1.mavach) AS stt,
    tenkho, mavach, tensp, SUM(Ton) AS totalton
    FROM YourTable AS T1
    GROUP BY tenkho, mavach, tensp;

    Ken Sheridan, Stafford, England

    • Marked as answer by lamtriendong Sunday, February 19, 2017 4:00 AM
    Saturday, February 18, 2017 1:43 PM
  • It's running okay, thank you very much
    • Marked as answer by lamtriendong Friday, March 10, 2017 1:33 AM
    Sunday, February 19, 2017 4:00 AM

All replies

  • Hi lamtriendong,

    Access 2003 is end support, I suggest you try to check whether below workaround will work for you.

    Since there is no Row_number function in Access, it would be much inconvenienet to get the serial number.

    If the serial number is not required, I would suggest you try below query.

    SELECT WAREHOUSE as TENKHO,BARCODE as MAVACH,GOODS AS HANGHOA, sum(NUMBERIMPORT) AS TON 
    FROM Sheet2
    group by WAREHOUSE,BARCODE,GOODS
    order by sum(NUMBERIMPORT)  desc

    If it is, I would suggest you create a table with above result, and then add serial number by AUTOINCREMENT PRIMARY KEY.

    You could follow below steps.

    1.Run below query to create Table

    SELECT WAREHOUSE as TENKHO,BARCODE as MAVACH,GOODS AS HANGHOA, sum(NUMBERIMPORT) AS TON Into NewTable
    FROM Sheet2
    group by WAREHOUSE,BARCODE,GOODS
    order by sum(NUMBERIMPORT)  desc

    2. Run below query to add serial number

    ALTER TABLE NewTable  ADD [ID] AUTOINCREMENT PRIMARY KEY NOT NULL

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 15, 2017 7:04 AM
  • You misunderstand me, I just want to query group and numbered , you see a attachments file access 2003, you see the table TABPHIEUKHOCTTMP and query qryResults, including [STT] column when I run it does not result in the desired order. you see my attachments: http://www.mediafire.com/file/t3jajr3pv1r2hjm/data.rar 

    unexpected results (column [STT] results is false while query qryResults)

    STT TENKHO MAVACH TENSP TON
    1 repository 2 456789 Apple 11
    1 repository 2 456789 Apple 3
    1 repository 2 456789 Apple 4
    4 repository 2 4654126 Cocacola 1
    4 repository 2 4654126 Cocacola 4
    6 repository 2 6492455 pesi 5

    I need results when queries (result is true)

    STT TENKHO MAVACH TENSP TON
    1 repository 2 456789 Apple 18
    2 repository 2 4654126 Cocacola 5
    3 repository 2 6492455 pesi 5

    Do you understand ? do you known to write Vietnamese ? 

    Saturday, February 18, 2017 4:52 AM
  • I have not looked at your file, but on the basis of the sample data you have posted I'd envisage a query along these lines

    SELECT
        (SELECT COUNT(*)
         FROM
            (SELECT DISTINCT mavach
             FROM YourTable) AS T2
             WHERE T2. mavach <= T1.mavach) AS stt,
    tenkho, mavach, tensp, SUM(Ton) AS totalton
    FROM YourTable AS T1
    GROUP BY tenkho, mavach, tensp;

    Ken Sheridan, Stafford, England

    • Marked as answer by lamtriendong Sunday, February 19, 2017 4:00 AM
    Saturday, February 18, 2017 1:43 PM
  • It's running okay, thank you very much
    • Marked as answer by lamtriendong Friday, March 10, 2017 1:33 AM
    Sunday, February 19, 2017 4:00 AM