0

I have a query with multiple criteria, where I would like to get 2 field criteria with AND condition and the rest of other fields with OR condition, but in my situation, it is considering complete criteria as OR condition. Attached is the image for your reference (Fields highlighted in RED BOXES to be compulsory fields as they are from Combo boxes. And fields highlighted in BLACK BOXES are to be worked as OR condition separately from RED Highlighted fields)

enter image description here

Currently, it is showing all the records despite considering the form combo box values. I have attached the SQL code for your reference.

SELECT
    Master_DataBase.Function,
    Sum(IIf([Status_of_Project] = "Delayed", 1, 0)) AS Delayed,
    Sum(IIf([Status_of_Project] = "Awaiting Approval", 1, 0)) AS [Awaiting Approval],
    Sum(IIf([Status_of_Project] = "Testing Ongoing", 1, 0)) AS [Testing Ongoing],
    Sum(IIf([Status_of_Project] = "On-Hold", 1, 0)) AS [On-Hold],
    Year([Project_Start_Date]) AS Expr1
FROM
    Master_DataBase
GROUP BY
    Master_DataBase.Function,
    Year([Project_Start_Date])
HAVING
    (
        (
            (Master_DataBase.Function) = Nz([Forms] ! [Navigator_Form] ! [FilterbyFunction],[Master_DataBase].[Function])
        )
        AND (
            (Year([Master_DataBase].[Project_Start_Date])) = Nz([Forms] ! [Navigator_Form] ! [FilterbyYear],Year([Master_DataBase].[Project_Start_Date])            )
        )
    )
    OR (
        ((Sum(IIf([Status_of_Project] = "Delayed", 1, 0))) >= 1)
    )
    OR (
        (
            (Sum(IIf([Status_of_Project] = "Awaiting Approval", 1, 0))) >= 1
        )
    )
    OR (
        (
            (Sum(IIf([Status_of_Project] = "Testing Ongoing", 1, 0))) >= 1
        )
    )
    OR (
        ((Sum(IIf([Status_of_Project] = "On-Hold", 1, 0))) >= 1)
    );

The desired results should be displayed only if either of the criteria (Delayed, Awaiting Approval etc.,) fields has some value. if all the fields have no value or 0 for a specific function, then that result should not be displayed. Attached for your reference (In the below image, the values highlighted in red boxes should not be displayed as those have no values or 0)

enter image description here

Please help!!

Table as Follows:

Function        Project Name        Status of the Project       Year
Function 1       Project 1              Completed               2021
Function 1       Project 2              On-Going                2020
Function 2       Project 3          Awaiting Approval           2021    
Function 3       Project 1              On-Hold                 2020
Function 4       Project 5                  -                   2021

etc.,

I have a form with 2 combo boxes (Year & Function), the results should be as per them and if they are empty it should show all the records which has value with any of the status

Desired Output:

Function        Completed   On-Going    Awaiting Approval   On-Hold
Function 1          1           1               0               0
Function 2          0           0               1               0
Function 3          0           0               0               1   

Note: Function 4 should not appear as it has no status
mrk777
  • 117
  • 1
  • 12
  • Edit question to show sample data and desired result as text tables. – June7 Feb 06 '21 at 20:13
  • In order not to consider all OR criteria, it would be ideal to check if you have the field and then create the query, being 4 criteria, you would have to have 4 different structures. It is necessary to use a combination for each possibility. I recommend using concatenation to create your query, increasing if necessary. see this procedure here https://stackoverflow.com/questions/36479386/how-to-concatenate-string-correctly-at-end-of-array-in-vba don't look at the question, see the answer. good luck – Julio Gadioli Soares Feb 06 '21 at 20:14
  • @June7, I have attached some more information for reference. – mrk777 Feb 06 '21 at 20:24
  • Not what was requested. Show sample data (raw table) and desired output as text tables, not images. – June7 Feb 06 '21 at 21:03
  • Still not what was requested. If you want to exclude those records then apply filter to the actual field: `NOT IS NULL`. – June7 Feb 06 '21 at 21:33
  • @June7, I'm new to the Access world, I'm sorry and I didn't get your question on "Show sample data (raw table) and desired output as text tables, not images." And I have tried applying a filter to the actual field: ```NOT IS NULL```, but still it isn't working. – mrk777 Feb 08 '21 at 15:55
  • @june7, I have added the text tables for your reference, please help. – mrk777 Feb 08 '21 at 18:02
  • So would [Status of the Project] actually have a `-` character as shown for Function 4? Your attempted query appears to be emulating a CROSSTAB. Why doesn't desired output show year? If it did, Function 1 should have two records. Strongly advise not to use spaces nor punctuation/special characters in naming convention. – June7 Feb 08 '21 at 20:02
  • Yes @June7, you are right, Function 1 should have 2 records. I have just given an example. And I haven’t used CrossTab query. The only concern is that if any Function has no field values/0 for all the column criteria (ie., Completed, On-Going etc.,), then it should not appear. – mrk777 Feb 08 '21 at 20:20
  • Output example should reflect sample data. If there are only 4 status values, it is simple to force a column to generate when there is no data. Review http://allenbrowne.com/ser-67.html#ColHead. Didn't answer question about `-` character. – June7 Feb 08 '21 at 20:26
  • There won’t be - Character, instead that field would be Blank – mrk777 Feb 08 '21 at 20:29
  • Did you get CROSSTAB to work? Edit question with your attempt if still need help. – June7 Feb 17 '21 at 13:48

0 Answers0