Crosstab query in form?

Can you provide your database with the Hall field (and possibly teacher) added?
 
You don't have a Hall field or table but you apparently want to select an individual hall for the filter. I need enlightening.
 
Please send a new file. I don't know which table you added the field to. I would have expected a hall to include multiple classrooms but it seems your situation is the opposite.
 
DHookom said:
Please send a new file. I don't know which table you added the field to. I would have expected a hall to include multiple classrooms but it seems your situation is the opposite.
Click to expand...
66.PNG
 

Attachments

If you are going to use halls, then they need to be linked to the class table and the supply quantities table. And if you wish to view the data in crosstab format, then you can just add it to the forms footer section. See attached file as an example of how input and viewing crosstab can be done.
 

Attachments

LarryE said:
If you are going to use halls, then they need to be linked to the class table and the supply quantities table. And if you wish to view the data in crosstab format, then you can just add it to the forms footer section. See attached file as an example of how input and viewing crosstab can be done.
Click to expand...
Thank you, dear,LarryE
for your help. But this is not what I want.
What I want is when I select the class the combo box does a filter for the crosstab query. See my comment in
For example, during the year I will provide the class (A1) with stationery every week or two. This class will repeat that it received stationery and other things. At the end of the year or during a certain period, I will go to the combo box and choose the name of the classroom or the name of the teacher, for example. The combo box will filter the table or crosstab query and tell me that this class or teacher received materials and pens at certain intervals, perhaps more than other classes, or that this teacher received and consumed more materials than his fellow teachers.
 
Last edited:
azhar2006 said:
Thank you, dear,LarryE
for your help. But this is not what I want.
What I want is when I select the class the combo box does a filter for the crosstab query. See my comment in
For example, during the year I will provide the class (A1) with stationery every week or two. This class will repeat that it received stationery and other things. At the end of the year or during a certain period, I will go to the combo box and choose the name of the classroom or the name of the teacher, for example. The combo box will filter the table or crosstab query and tell me that this class or teacher received materials and pens at certain intervals, perhaps more than other classes, or that this teacher received and consumed more materials than his fellow teachers.
Click to expand...
ACCESS will not filter a crosstab query based upon criteria in a form. You must filter a crosstab query using its own filtering dropdown criteria for each field.
 
LarryE said:
ACCESS will not filter a crosstab query based upon criteria in a form. You must filter a crosstab query using its own filtering dropdown criteria for each field.
Click to expand...
May I ask why? You can change the sql of the query and reload it.
Check the attached file. Is it what you think Access is not able to do?
Open frmDisplayQuery. From the first combo box select "QryInput_Crosstab"
Then select a classroom from the next combo box.
 

Attachments

Last edited:
@LarryE
I have a non-English version of Access and most of the time, others can not open my files.
If the above file shows error, Download OP's file in #22, and add the following to frmDisplayQuery.
Then check if form's on-close event and combx's after-update event are set to :"[Event Procedure]"

SQL: 
Private Property Get sql() As String
    sql = "SELECT DISTINCTROW q.ClassroomID, c.ClassroomName, q.SupplyID, s.SupplyName, q.Quantity "
    sql = sql & "FROM Supply AS s INNER JOIN (Classroom AS c INNER JOIN SuppyQuantity AS q ON c.ClassroomID = q.ClassroomID) "
    sql = sql & "ON s.SupplyID = q.SupplyID"
End Property

Private Property Get sort() As String
    sort = "ORDER BY q.ClassroomID, q.SupplyID;"
End Property

Private Sub combx_AfterUpdate()
    Dim Filtr As String
    If Not combx & "" = "" And cboQuery = "QryInput_Crosstab" Then
        Filtr = "WHERE ClassRoomname='" & combx & "'"
    End If
    CurrentDb.QueryDefs("Qryinput").sql = sql & " " & Filtr & " " & sort
    Me.sfrmQuery.Form.Requery
End Sub

Private Sub Form_Close()
    CurrentDb.QueryDefs("Qryinput").sql = sql & " " & sort
End Sub
 
Last edited:
KitaYama said:
@LarryE
I have a non-English version of Access and most of the time, others can not open my files.
If the above file shows error, Download OP's file in #22, and add the following to frmDisplayQuery.
Then check if form's on-close event and combx's after-update event are set to :"[Event Procedure]"

SQL: 
Private Property Get sql() As String
    Dim sq As String
    sql = "SELECT DISTINCTROW q.ClassroomID, c.ClassroomName, q.SupplyID, s.SupplyName, q.Quantity "
    sql = sql & "FROM Supply AS s INNER JOIN (Classroom AS c INNER JOIN SuppyQuantity AS q ON c.ClassroomID = q.ClassroomID) "
    sql = sql & "ON s.SupplyID = q.SupplyID"
End Property

Private Property Get sort() As String
    sort = "ORDER BY q.ClassroomID, q.SupplyID;"
End Property

Private Sub combx_AfterUpdate()
    Dim Filtr As String
    If Not combx & "" = "" And cboQuery = "QryInput_Crosstab" Then
        Filtr = "WHERE ClassRoomname='" & combx & "'"
    End If
    CurrentDb.QueryDefs("Qryinput").sql = sql & " " & Filtr & " " & sort
    Me.sfrmQuery.Form.Requery
'     Me.sfrmQuery.SourceObject = "Query." & Me.cboQuery & ""
End Sub

Private Sub Form_Close()
    CurrentDb.QueryDefs("Qryinput").sql = sql & " " & sort
End Sub
Click to expand...
This is amazing . Thank you KitaYama
This is what I really aspire to. Thank you again. Nice work. When you clear the contents of the filter's combo box and click the Enter button, all the chapters reappear. There is no need to close and open the form again. Thank you dear.
 
azhar2006 said:
This is amazing . Thank you KitaYama
This is what I really aspire to. Thank you again. Nice work. When you clear the contents of the filter's combo box and click the Enter button, all the chapters reappear. There is no need to close and open the form again. Thank you dear.
Click to expand...
You can add a select statement and do the same concept for other queries too.
 
KitaYama said:
You can add a select statement and do the same concept for other queries too.
Click to expand...
Thank you, my friend.KitaYama
I will add a teachers name field in the query and apply the same concept to the filter combo box. Thanks for helping me. If I need help, will you help me? I will be grateful to you.
Suppose I want to copy the query QryInput_Crosstab to create another list similar to the Supply table, but for printing devices, not stationery. Meaning that we have two tables, the first is for stationery, paper, and pens, and the other table is for devices such as a printer, a photo viewer, and an electronic whiteboard. Since I want this not in the Supply table, but in another table. The purpose of this is, firstly, so that the query display in the form is not long and does not have to use the horizontal scroll bar, and secondly, that these materials are not included in the stationery. The cboQuery combo box shows me the stationery query. How can I add another query that displays the devices query?

DHookom​

LarryE​

KitaYama​

Gasman​

Of course, I will not forget, thank you all. If it were not for you, the work would have continued for several weeks
 
Last edited:
It makes no real sense to create another table for materials that are not stationery. Simply add a field that allows the materials in the Supply table to be categorised as Stationery, Presentation Equipment, Other etc. (Maintain it as a SupplyCategory table). This is a standard practise in database design referred to as normalisation. The query(ies) can use the Category to filter the list limiting the number of records to display for when interested in Stationery or Equipment as needed.
 
GaP42 said:
It makes no real sense to create another table for materials that are not stationery. Simply add a field that allows the materials in the Supply table to be categorised as Stationery, Presentation Equipment, Other etc. (Maintain it as a SupplyCategory table). This is a standard practise in database design referred to as normalisation. The query(ies) can use the Category to filter the list limiting the number of records to display for when interested in Stationery or Equipment as needed.
Click to expand...
Thanks for the reply dearGaP42
Even if I add this field in the display table, what will the combo box depend on? The query will remain displayed in the form for a long time because the other materials, I mean stationery materials, will not disappear. I will have to use the horizontal scroll bar to get to the end of the query
 
azhar2006 said:
The purpose of this is, firstly, so that the query display in the form is not long and does not have to use the horizontal scroll bar, and secondly, that these materials are not included in the stationery.
Click to expand...
I don't understand your database and how it should work, so I can't advise on your table structure. Others, are much better than me in this area. You may want to listen to them. I only can say that your above points, aren't reasonable for adding a new table. You can always have several queries to limit fields and rows, and use them in different situation. Instead of trying to figure out how you need a form to be shown or work, you should first work on a correct table structure and relation.
When your schema is correct, anything else is just a piece of cake.
 
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:

Code: 
PARAMETERS [Forms]![frmDisplayQuery]![combx] Text ( 255 );
TRANSFORM Sum(SuppyQuantity.Quantity) AS SumOfQuantity
SELECT Classroom.TeacherName, Classroom.ClassroomName, Sum(SuppyQuantity.Quantity) AS [Total Of Quantity]
...
 
You must log in or register to reply here.

Similar threads

M
Filter of list field
Replies
1
Views
130
Pat Hartman
P
azhar2006
Import variables from the first database to the main version
Replies
9
Views
202
Pat Hartman
P
M
Form based on crosstab - hiding columns
Replies
9
Views
812
ebs17
E
W
Calculated field in form from another table
Replies
7
Views
242
Pat Hartman
P
A
Solved Form based on query + subform
Replies
6
Views
256
Andy74
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom