How to filter a crosstab query? (1 Viewer)

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
This is the structure of three tables:

2020-08-24_9-51-12.jpg


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.

2020-08-24_9-56-07.jpg


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

  • Database2.zip
    4 MB · Views: 453
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
Hi. If the record source stays the same, you can simplify the function by using the form's Filter property. That way, you won't have a chance to mess up the SQL.
 

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
Hi. If the record source stays the same, you can simplify the function by using the form's Filter property. That way, you won't have a chance to mess up the SQL.
I actually thought about it. The actual form has a lot of options to filter or change the record source. But since the source is a cross tab query, in some cases the filter throws up some errors. Maybe I'm doing something wrong.

Tried this, but access asks me for ProcessFK parameter.
Me.Filter = "ProcessFK=20 AND ProcessedOn is not null"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
I actually thought about it. The actual form has a lot of options to filter or change the record source. But since the source is a cross tab query, in some cases the filter throws up some errors. Maybe I'm doing something wrong.

Tried this, but access asks me for ProcessFK parameter.
Me.Filter = "ProcessFK=20 AND ProcessedOn is not null"
Okay, when I get a chance, I'll download your file and take a look.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
Hi Tera. I downloaded your file, but when I opened the form, it was empty.
Hi Tera. See if this is close to what you mean. Hope it helps...
 

Attachments

  • Database2.zip
    4 MB · Views: 555

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
Hi Tera. See if this is close to what you mean. Hope it helps...
Yes it works. I wonder why. I had used this but received a parameter error. I mark this thread as solved because according to my sample file uploaded above your code works perfectly.
I will replicate it in my main database. If I face any problem I will open this thread and will ask again.

Just a question if you don't mind.
What does 1=1 in filter string and form's filter means?


thanks again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
Yes it works. I wonder why. I had used this but received a parameter error. I mark this thread as solved because according to my sample file uploaded above your code works perfectly.
I will replicate it in my main database. If I face any problem I will open this thread and will ask again.

Just a question if you don't mind.
What does 1=1 in filter string and form's filter means?


thanks again.
It's my way to take a shortcut. The long way is to check if a filter needs to be applied or not depending on whether the user chose or entered a criteria. My shortcut is to just apply a filter, no matter what.
Good luck with your project.
 

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
@theDBguy While filtering works fine and I receive what I expect to, it seems that filtering a crosstab query with more than a million records is a little slow.
I will wait to see if anyone can help to rebuild the sql (instead of filtering it) to see if there's any improvement in performance.
Thanks for your time.

To be clear again I need a sql that :
DrawingNo='AL18888BC601'
Process1 not Null
process3 not null
Process4 is null

thanks again for your time.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
@theDBguy While filtering works fine and I receive what I expect to, it seems that filtering a crosstab query with more than a million records is a little slow.
I will wait to see if anyone can help to rebuild the sql (instead of filtering it) to see if there's any improvement in performance.
Thanks for your time.

To be clear again I need a sql that :
DrawingNo='AL18888BC601'
Process1 not Null
process3 not null
Process4 is null

thanks again for your time.
Hi. In that case, I would have two approaches for you. For the first one, this gives you exactly what you asked for. Please let us know if this makes any difference in speed. If not, I'll give you the second approach.
Code:
strSQL = "TRANSFORM First(tblRecProcesses.ProcessedOn) AS FirstOfProcessedOn SELECT tblReceiption.ReceiptionPK, tblMaster.DrawingNo, " _
    & " tblReceiption.RecMasterFK, tblReceiption.Rec_ID, tblReceiption.Delivery, tblReceiption.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))) "

strPivot = " GROUP BY tblReceiption.ReceiptionPK, tblMaster.DrawingNo, tblReceiption.RecMasterFK, tblReceiption.Rec_ID, tblReceiption.Delivery, " _
    & " tblReceiption.Quantity PIVOT 'Process ' & [ProcessFK] In ('Process 1','Process 2','Process 3','Process 14','Process 20'); "

strWhere = " AND DrawingNo='AL18888BC601' AND [Process 1] Is Not Null AND [Process 3] Is Not Null AND [Process 4] Is Null "

Me.RecordSource = strSQL & strWhere & strPivot
 

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
@theDBguy thanks.
I'm away from my PC. I'll check it as soon as I'm back to my desk and let you know the results.

Thanks again for sharing your power.:D
(In Japanese we have a saying knowledge is power)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
@theDBguy thanks.
I'm away from my PC. I'll check it as soon as I'm back to my desk and let you know the results.

Thanks again for sharing your power.:D
(In Japanese we have a saying knowledge is power)
No problem. I have the other approach ready to submit, if the first approach doesn't help. Cheers!
 

deletedT

Guest
Local time
Today, 13:58
Joined
Feb 2, 2019
Messages
1,218
No problem. I have the other approach ready to submit, if the first approach doesn't help. Cheers!
@theDBguy
I'm sorry for not being able to test your solution. I have to leave AWF.
I think I will post the same question on utterAccess and I hope to find you there to continue on this.

Million thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:58
Joined
Oct 29, 2018
Messages
21,358
@theDBguy
I'm sorry for not being able to test your solution. I have to leave AWF.
I think I will post the same question on utterAccess and I hope to find you there to continue on this.

Million thanks.
Hi. Sorry to hear that. I'll look for you over there. Good luck!
 

Users who are viewing this thread

Top Bottom