Help : Query based on data from multiple rows

[GeorgeB]

Hello guys



I am here again with help needed

this is a simple of what I am trying to do :

ContractNo ProjectNo WorkNo WoStatus WoType
11111 2222 1 A x
11111 2222 2 B Y
11111 2222 3 B Z
11111 2222 4 A S
11111 3333 1 B Y
11111 3333 2 C Z

and so on

I need to select or have a column that gives me only the "ProjectNo"s that got WoStatus 1 and 2and 3 and 4 in WoStatus A or B And WoType X and Y and Z and S

if this possible ?

I've read about aggregations but I didn't work our for me

Thanks very much

[recyan]

Just check if below gives some guidelines :

SELECT 	myTable.ContractNo, myTable.ProjectNo, myTable.WorkNo,myTable.WoStatus, myTable.WoType FROM 	myTable WHERE (	((myTable.WorkNo)="1") 	OR 	((myTable.WorkNo)="2") 	OR 	((myTable.WorkNo)="3") 	OR 	((myTable.WorkNo)="4")) AND (	((myTable.WoStatus) = "A") 	OR 	((myTable.WoStatus) = "B") ) AND (	((myTable.WoType) = "X") 	OR 	((myTable.WoType) = "Y") 	OR 	((myTable.WoType) = "Z") 	OR 	((myTable.WoType) = "S") );

Edit : The Code tags don't appear to be working. Thanks

[GeorgeB]

Thanks very much Recyan for your input

to clarify what I want

I want to extract the Project number Where :

1st condition) : It must have at least 4 "WorkNo"
2nd Condition) : The "WoStatus" must be whether A OR B only
3rd Condition) : Must have the 4 different "WoType" (x,y,z,s)


Thanks again

[recyan]

Totally misread your requirement.
Is there a possibility that the same project number can have 2 or more same "WoType".
Eg:.
ContractNo ProjectNo WorkNo WoStatus WoType
11111 2222 1 A x
11111 2222 2 B Y
11111 2222 3 B Z
11111 2222 4 A Z

Thanks

[GeorgeB]

Yeah in some cases in happens

[recyan]

Did not ask you earlier, but am assuming that there will be no duplication of "WorkNo" against a particular "ProjectNo".
If there is a possibility, then ignore / try modifying logic of below :

Just check out if below gives some guidelines :
Save below as sub-query qryWoType :

SELECT 
    myTable.ProjectNo, 
    myTable.WoType
FROM 
    myTable
WHERE 
    (
        (
            (myTable.WoStatus)="A"     Or (myTable.WoStatus)="B"
        )
    )
GROUP BY 
    myTable.ProjectNo, 
    myTable.WoType
HAVING 
    (((myTable.WoType)="X" Or (myTable.WoType)="Y" Or (myTable.WoType)="Z" Or (myTable.WoType)="S"));

Run this as final query :

SELECT 
    qryWoType.ProjectNo, 
    Count(qryWoType.WoType) AS CountOfWoType
FROM
    qryWoType
GROUP BY 
    qryWoType.ProjectNo
HAVING 
    (((Count(qryWoType.WoType))>=4));

You can check what is happening if something goes wrong, by running the sub-query seperately & taking a look at the results.

Thanks

[GeorgeB]

Hi Recyan

I tried the first query but I get an error msg "You tried to execute a query that does not include the specified expression WO_STATUS as part of an aggregate function" do you have any idea why

SELECT WO.SP_NUMBER,
WO.WO_STATUS
FROM
WO
WHERE
(
(
(WO.WO_STATUS)="dlp" Or (WO.WO_STATUS)="fc" Or (WO.WO_STATUS)="pw"
)
)
GROUP BY
WO.SP_NUMBER,
WO.CPU_TYPE
HAVING
(((WO.CPU_TYPE) like "mobiles*detai*" Or (WO.CPU_TYPE) like "mobiles*Pre*" Or (WO.CPU_TYPE) like "mobiles*rf*" Or (WO.CPU_TYPE) like "mobiles*sae*"));

The actual code

by the way this my first time to deal with SQL

[recyan]

In the SELECT you have :

SELECT 
    WO.SP_NUMBER, 
    WO.WO_STATUS

and in the GROUP BY you have :

GROUP BY 
        WO.SP_NUMBER, 
        WO.CPU_TYPE

Instead of "WO.WO_STATUS" in the SELECT have "WO.CPU_TYPE". Again, check your data functionality. I have written based on the fields you had provided.

Thanks