Crosstab query in form?

[LarryE]

I wouldn't create multiple tables for items. If you want to group certain items together, add a Category field (with a small lookup table). You can then filter any report or form by Item Category.

Also, someone stated "ACCESS will not filter a crosstab query based upon criteria in a form." which is false. You must add parameter data types in the query. Once you do this, the query will work as expected. The SQL view of a crosstab will look something like:

PARAMETERS [Forms]![frmDisplayQuery]![combx] Text ( 255 );
TRANSFORM Sum(SuppyQuantity.Quantity) AS SumOfQuantity
SELECT Classroom.TeacherName, Classroom.ClassroomName, Sum(SuppyQuantity.Quantity) AS [Total Of Quantity]
...

Yeah, I learned something. I never use a crosstab anyway in a form. When the number of columns change, it changes the way the crosstab looks, and you need to manually change the column widths. I much prefer to use totals queries based upon normal form criteria. It's much cleaner and you can use continuous subforms to display the data anyway which automatically adjust to accommodate data. Just my opinion. You can use DSums to sum totals as needed without messy code to filter a crosstab. Again, just my own opinion.

 

[DHookom]

I have only used dynamic crosstabs in dynamic forms like the answer to this question. You don't need to enter Parameters if you enter the column headings in design. This works best for Column Headings like days of the week, months, years (this, previous, etc), or other "stable" values. I worked for a large food manufacturing company and typically sent data to Excel for Pivot tables. Prior to retiring (2021) most of our KPI data was reported using PowerBI. These are both much more flexible and powerful for users.

 

[LarryE]

I wouldn't create multiple tables for items. If you want to group certain items together, add a Category field (with a small lookup table). You can then filter any report or form by Item Category.

Also, someone stated "ACCESS will not filter a crosstab query based upon criteria in a form." which is false. You must add parameter data types in the query. Once you do this, the query will work as expected. The SQL view of a crosstab will look something like:

PARAMETERS [Forms]![frmDisplayQuery]![combx] Text ( 255 );
TRANSFORM Sum(SuppyQuantity.Quantity) AS SumOfQuantity
SELECT Classroom.TeacherName, Classroom.ClassroomName, Sum(SuppyQuantity.Quantity) AS [Total Of Quantity]
...

I got this error when I tried to use PARAMETERS

I don't know what it means, but I couldn't figure it out.
Here is my SQL using my form name:

PARAMETERS [Forms]![FrmClassroom]![ClassroomName] Text ( 255 );
TRANSFORM Sum(QryInput.Quantity) AS SumOfQuantity
SELECT QryInput.ClassroomName, QryInput.HallNum, QryInput.CategoryName, Sum(QryInput.Quantity) AS [Total Of Quantity]
FROM QryInput
GROUP BY QryInput.ClassroomName, QryInput.HallNum, QryInput.CategoryName
ORDER BY QryInput.ClassroomName
PIVOT QryInput.SupplyName;

 

[LarryE]

@azhar2006:
Here is a new demo file for your use including a new Category table. I was also able to include your crosstab query and it will filter when you select a classroom to Go To. This form is not actually filtered when you select a classroom to go to, but since sub-forms are linked, it is filtered automatically. You can then click the Show All Classrooms button to unfilter the crosstab.

Please review the Database Relationships window first, so you can see how the design is made.
The form allows you to enter and edit Classrooms, Halls, Categories, Supplies and Supply amounts all on one form.

 

[azhar2006]

@azhar2006:
Here is a new demo file for your use including a new Category table. I was also able to include your crosstab query and it will filter when you select a classroom to Go To. This form is not actually filtered when you select a classroom to go to, but since sub-forms are linked, it is filtered automatically. You can then click the Show All Classrooms button to unfilter the crosstab.

Please review the Database Relationships window first, so you can see how the design is made.
The form allows you to enter and edit Classrooms, Halls, Categories, Supplies and Supply amounts all on one form.

Thank you very muchLarryE , great work.

But the crosstab query displayed in the form remained the same, I have to use horizontal scroll bars. And with the addition of new material it will be longer and so on. So I was thinking that I can isolate the categories with three crosstab queries and through a main form or an opening form with buttons that call each query specific to a specific category separately.

 

[LarryE]

Thank you very muchLarryE , great work.

But the crosstab query displayed in the form remained the same, I have to use horizontal scroll bars. And with the addition of new material it will be longer and so on. So I was thinking that I can isolate the categories with three crosstab queries and through a main form or an opening form with buttons that call each query specific to a specific category separately.

That is the problem with crosstab queries. I never use them because they cannot be easily filtered, they cannot be formatted to look professional and they if you add new columns, they require using the horizontal scrollbar. You cannot get around these limitations. I am working on an alternative way. I will attach the file when it is done. Let me see what can be done.

 

[LarryE]

@azhar2006:
This version does not use a crosstab query but gives you the same totals viewed 2 ways. You can scroll the supplies and see which classrooms has what amounts. You scroll or select a classroom and see what supplies that classroom has and in what amounts.

 

[azhar2006]

Haha you are really great thank you very much.LarryE
I will make some adjustments to the destination
It is very nice to find friends like you in this wonderful forum. Thank you to those in charge of this really enjoyable forum.

 

[LarryE]

Haha you are really great thank you very much.LarryE
I will make some adjustments to the destination
It is very nice to find friends like you in this wonderful forum. Thank you to those in charge of this really enjoyable forum.

@azhar2006:
You may get a Parameter error requesting a SupplyID number when opening the FrmClassroom.
The form performs much better if you:

1. Open the FrmViewSupply in design mode
2. Open the Record Source
3. Remove the SupplyID criteria ([Forms]![FrmClassroom]![FrmHall]![FrmCategory]![FrmSupply]![SupplyID])
4. Save the Record Source and close the form
5. Open the FrmClassroom in design mode
6. Insert the following code in the form Current Event:

Private Sub Form_Current()
Me.FrmViewSupply.Form.Filter = "[SupplyID]=[Forms]![FrmClassroom]![FrmHall]![FrmCategory]![FrmSupply]![SupplyID]"
Me.FrmViewSupply.Form.FilterOn = True
Me.FrmViewSupply.Form.Requery
Exit Sub
End Sub

This will filter the FrmViewSupply AFTER the FrmClassroom opens and should eliminate any Parameter errors when opening the form.

 

[LarryE]

@azhar2006:
This updated file has a Test Classroom and several new Category and Supply amounts for testing purposes.

The form also works much better.

Look at the Test Classroom and its Category and Supply amounts to see how each one is displayed. I also deleted the totals at the bottom of the Classroom sub-form as it was a meaningless value.

 

[azhar2006]

@azhar2006:
This updated file has a Test Classroom and several new Category and Supply amounts for testing purposes.

The form also works much better.

Look at the Test Classroom and its Category and Supply amounts to see how each one is displayed. I also deleted the totals at the bottom of the Classroom sub-form as it was a meaningless value.

Thank you very much dear LarryE
Yes I have seen that already. But there is one problem is when I make the form full screen some navigation buttons will disappear. And I am trying to adjust the subforms inside the main form

 

[LarryE]

@azhar2006:
I apologize for the confusing error messages on the last file I sent you. Here is a re-constructed file with added views for each supply item. Filtering a sub-form when the main form opens is very confusing, but I finally got the correct code. Here is a new file that should not have any error messages. If you get any messages, please let me know.

 

[LarryE]

@azhar2006:
Having the summary sub-forms in the FrmClassroom Footer Section will not work. I have put them in their proper respective form footer sections so any filtering code or criteria is no longer needed. Here is the file. You are welcome to do whatever you wish.

I would be happy to help with any reports if you want.

 

[LarryE]

@azhar2006:
Do you require reports for this project?

 

[azhar2006]

@azhar2006:
Do you require reports for this project?

Oh thank LarryE dear. Yes I need a report.

 

[LarryE]

Oh thank LarryE dear. Yes I need a report.

Here is a new file that includes various reports. Select the filtering option and click the Print Report button. For example, if you want to know which Classrooms and Halls have Colored Paper:

1. Move to the Colored Paper record under Supply Name
2. Select the By Active Supply filtering option
3. Click Print Report

 

[azhar2006]

Here is a new file that includes various reports. Select the filtering option and click the Print Report button. For example, if you want to know which Classrooms and Halls have Colored Paper:

1. Move to the Colored Paper record under Supply Name
2. Select the By Active Supply filtering option
3. Click Print Report

 

[LarryE]

View attachment 115877View attachment 115878

You should use the last file I sent to you. If you want the class supplies form to look like this one with buttons aligned, I might be able to do that, but do not use this older file version. There is a Test classroom that you can delete if you wish. Attach the file you are using and I will try to adjust it for you.

 

[azhar2006]

You should use the last file I sent to you. If you want the class supplies form to look like this one with buttons aligned, I might be able to do that, but do not use this older file version. There is a Test classroom that you can delete if you wish. Attach the file you are using and I will try to adjust it for you.

Yes I will use the latest program because it is integrated in terms of reports as well but I will modify the fields and navigation buttons. Thank you very much you are really great. dear friend

LarryE​

 

[Gasman]

And you have learnt very little if anything?