This is the structure of three tables:
This is a form based on a cross tab query. Users open this form. Type a drawing number and click the filter button. A function creates a sql and use it as the record source of the table. The results are shown.
If I type AL18888BC601, and select ALL in both options, this is the sql that the function returns:
Everything just fine up to this point.
Now my question. How can I change this sql to make the sql show only the records that :
DrawingNo='AL18888BC601'
Process1 not Null
process3 not null
Process4 is null
the result must show only the second record from top.
A sample database is attached.
I appreciate any kind of advice.
Important notes:
Once again, thanks for your time.
This is a form based on a cross tab query. Users open this form. Type a drawing number and click the filter button. A function creates a sql and use it as the record source of the table. The results are shown.
If I type AL18888BC601, and select ALL in both options, this is the sql that the function returns:
SQL:
TRANSFORM First(ProcessedOn) AS FirstOfProcessedOn
SELECT ReceiptionPK, DrawingNo, RecMasterFK, Rec_ID, Delivery, Quantity
FROM (tblMaster RIGHT JOIN tblReceiption ON tblMaster.MasterPK = tblReceiption.RecMasterFK)
LEFT JOIN tblRecProcesses ON tblReceiption.ReceiptionPK = tblRecProcesses.ReceiptionFK
WHERE (((tblRecProcesses.ProcessFK) In (1,2,3,14,20))) AND DrawingNo LIKE '*AL18888BC601*'
GROUP BY ReceiptionPK, DrawingNo, RecMasterFK, Rec_ID, Delivery, Quantity
PIVOT 'Process ' & [ProcessFK] In ('Process 1','Process 2','Process 3','Process 14','Process 20');
Everything just fine up to this point.
Now my question. How can I change this sql to make the sql show only the records that :
DrawingNo='AL18888BC601'
Process1 not Null
process3 not null
Process4 is null
the result must show only the second record from top.
A sample database is attached.
I appreciate any kind of advice.
Important notes:
- the actual tables have more than a million records. So speed in receiving the result is important
- The filter I requested above, is just an example of how a user may use this form. Users may select different options. I can manage to write a function to change the sql dynamically. I just need to know how sql should be written.
- The form is just read only. I don't want to edit the records.
Once again, thanks for your time.
Attachments
Last edited: