none
Group of records and assigning conditional group values ? RRS feed

  • Question

  • In Access I want to group and assign data according to conditions with 2 values 0 and 1 (true or false) and with the condition NGAYGIAO>= #01/01/2019# and NGAYGIAO <= #31/01/2019# me How to write commands ? The data I put in the excel file consists of two sheets: Original data and Result, where Original data is the original data sheet that is the result of the problem I need, when running the access issue, ask you to help me with the problem.

    http://www.mediafire.com/file/hkkw519bw2k3o9h/baitap_data2.xls/file

    Wednesday, March 27, 2019 8:07 AM

All replies

  • You'd do this in a computed column in a query, not in a column in a base tale, e.g.

        (NGAYGIAO >= #2019-01-01# AND NGAYGIAO < #2019-02-01#) AS Jan2019

    The expression would return a Boolean TRUE or FALSE, implemented in Access as -1 or 0.  Two points to note:

    1.  A date literal must be in US format or an otherwise internationally unambiguous format.  The above uses the ISO standard for date notation of YYYY-MM-DD.

    2.  By defining the range as on or later than 1 Jan 2019 and before 1 Feb 2019, any rows with a value on 13 Jan 2019 which includes a non-zero time of day element will be included.  Your expression would exclude such values from the range.

    Ken Sheridan, Stafford, England

    Wednesday, March 27, 2019 12:35 PM
  • from the original data I created the result, if the contract in the month has LOAIHD = "FBAN" and between (#01/01/2019# and #31/01/2019#) then [BOOL] = 1 else [BOOL = 0, I will describe the condition of [BOOL] in C# code

    [CODE]
    for (int i = 0; i < [Original data].Count; i++)
                { 
                    bool BOOLEAN = 0;
                    string SOHD = "";
                    if([NGAYGIAO].[i]>=#1/1/2019# && [NGAYGIAO].[i] <=#1/31/2019#) //dd/MM/yyyy
                    {
                       for (int j = 0; j < [Original data].Count; j++)
                       {
                           if([LOAIHD].[j] == "FBAN") 
                            {
                                BOOLEAN = 1;
                                SOHD = [Original data].[HD].[j];
                            }                  
                       }
                    }
                    if(SOHD == [Original data].[HD].[i] && SOHD != "")
                    {
                        [Result].[BOOL].[i] = BOOLEAN;
                    }
                }
    [/CODE]
    Monday, April 8, 2019 2:29 AM
  • You can extend the expression I gave you earlier as follows:

    (LOAIHD = "FBAN" AND NGAYGIAO >= #2019-01-01# AND NGAYGIAO < #2019-02-01#)

    This returns a Boolean TRUE or FALSE, which in Access is implemented as -1 or zero.  If you want to return integers 1 or 0 then you can call the IIF function:

    IIF(LOAIHD = "FBAN" AND NGAYGIAO >= #2019-01-01# AND NGAYGIAO < #2019-02-01#,1,0)

    Ken Sheridan, Stafford, England

    Monday, April 8, 2019 12:02 PM
  • I have not written a problem yet to produce results, you see the original data
    Tuesday, April 9, 2019 9:41 AM