Matrix Crosstab query based on checkboxes with multiple values (1 Viewer)

heathxp · Jul 2, 2019

I searched online but I'm not sure if I need to create 3 separate cosstab queries and then somehow join them.

I'm trying to achieve a report that will show a percentage of 3 criteria (checkboxes) based on month.

I'm following some tutorials but I'm unable to add 3 values in one crosstab query.
I'm also not sure how to sum checkboxes as percentages.
Can someone point me to a few good examples?

I apologize for my lack of points but here's my attempt to include some data/pictures:
Here's an example of what I'm trying to acheive:
i.imgur.com/oy5cMbU.png

and here is an example database:
gofile.io/?c=hkr5Op

plog · Jul 2, 2019

Cross-tab queries aren't an intermediate step, they should be the final query. So you shouldn't JOIN 3 of them together. Instead you should either UNION or JOIN the underlying data that makes them up and then cross-tab that.

I can't decrypt your urls, so I can't see your examples.

arnelgp · Jul 2, 2019

maybe an Aggregate (Total) query will do it for you.

heathxp · Jul 2, 2019

plog said:
Cross-tab queries aren't an intermediate step, they should be the final query. So you shouldn't JOIN 3 of them together. Instead you should either UNION or JOIN the underlying data that makes them up and then cross-tab that.

I can't decrypt your urls, so I can't see your examples.

Thanks plog. I edited the URL's.

I'm not sure what is there to UNION. It's literally 4 columns in the same table and 4 columns for the output.

heathxp · Jul 2, 2019

arnelgp said:
maybe an Aggregate (Total) query will do it for you.

Thanks arnelgp. This pretty much does what I need but I'd like to have the months as columns and the results as rows.

Basically, transpose the current output and show it as percentages.

arnelgp · Jul 2, 2019

I hope you can follow this.
firstly I created another table zzTempTable.
we will add records to this table from Orders table.
then we will use qry_Crosstab to crosstab the temp table.

see Form1 sample.

the subform, child0, the sourceobject is initially blank.
on the load event of Form1, we call the public sub, PrepareXTab to initialized the temp table and add records to it.
next, we set the sourceobject of our subform to qry_Crosstab.

see all the codes in the module and in the load event of Form1.

I added line number on each activity, so it will be sorted in order.

heathxp · Jul 2, 2019

arnelgp said:
I hope you can follow this.

Thank you. This is more complicated than I expected. In Excel I can simply do a "Transpose" paste but this works so Thank you.

Just need to figure out the percentage part and ability for user to pick the time period but I think I can figure this out.

heathxp · Jul 2, 2019

arnelgp said:
I hope you can follow this.
firstly I created another table zzTempTable.
we will add records to this table from Orders table.
then we will use qry_Crosstab to crosstab the temp table.

see Form1 sample.

the subform, child0, the sourceobject is initially blank.
on the load event of Form1, we call the public sub, PrepareXTab to initialized the temp table and add records to it.
next, we set the sourceobject of our subform to qry_Crosstab.

see all the codes in the module and in the load event of Form1.

I added line number on each activity, so it will be sorted in order.

arnelgp, it looks like the underlying data is not including rows where neither of these conditions are met and therefore I cannot count the total number of rows to see the percentages of Orders were the condition occurred. I can't just count the total now from this crosstab query either because the same order can have multiple true conditions which will inflate the total. All we have now is the total occurrences instead of percentage of occurence for each month for each category.

I believe I would have to do the percentage calculations in VBA directly.
Maybe just include the ID field for each line and include the rest of the results under a forth category and then count distinct ID per month?

arnelgp · Jul 3, 2019

here, please review the calculation on modUtility if I am doing the right calculation for the percentage. modify as you wish.

see form1.

heathxp · Jul 3, 2019

arnelgp said:
here, please review the calculation on modUtility if I am doing the right calculation for the percentage. modify as you wish.

see form1.

arnelgp! Thank you, you are a lifesaver. I spent most of the day yesterday trying to find a workaround but indeed, calculating that directly in VBA was the way to go.

Cheers!

Matrix Crosstab query based on checkboxes with multiple values (1 Viewer)

heathxp

Registered User.

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

Attachments

heathxp

Registered User.

heathxp

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

heathxp

Registered User.

heathxp

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

heathxp

Registered User.

Similar threads

Users who are viewing this thread