Crosstab query in form?

[azhar2006]

Hello guys
How to display a crosstab query on the form?
If there is an example, I would be grateful to you. Thanks

I have three tables, each table contains sections. There is stationery such as pens, papers, and other materials distributed among these departments. What I want is to create a tabular query that shows me the names of the departments horizontally and vertically the stationery materials distributed among those departments in numbers (quantities).

 

[Gasman]

Have you looked here?
If you need to.make it dynamic, I seem to recall a little more work.

 

[DHookom]

You can use a form with a junk subform. Use a little to change the source object.
Me.MySubform.SourceObject = ”Query.[your query name]”

 

[azhar2006]

Hello everyone
What I want to do is convert this table from Excel to Access. And make a cross-tab query. Is there any help?
Thank you very much

 

[DHookom]

The Excel data can be imported directly into an Access table but needs to be normalized into tables. The should be tables for rooms, items, and inventory (RoomID, ItemID, Quantity,...).
tblRooms
RoomID
RoomName

tblItems
ItemID
ItemName

tblInventory
invID
invRoomID
invItemID
invQuantity


Then you can create a crosstab of all three tables with Room Name (or ID) as the Row Heading, Item Name as the Column Heading, and Sum of inventory Quantity.

Let us know if you have any other questions.

 

[LarryE]

Here you go. Open QryClassroomSupplyTotals. By the way, the total for Classroom A1 is 27, not 28.
You can display crosstab information on a form, but cannot edit it in crosstab.

 

[DHookom]

Nice job LarryE. I have attached the updated file with the ability to display a query in a form.

 

[azhar2006]

Thank you very much my dears. I will edit the examples, and if you need help, you can certainly help me. Isn't it?

 

[azhar2006]

Thank you DHookom
Is it possible to add a combo box that creates a filter to the classroom rooms?

 

[arnelgp]

see the changes.

 

[Gasman]

How many classrooms do you have, that you need to filter?

 

[azhar2006]

How many classrooms do you have, that you need to filter?

Approximately 17 classrooms, for example, the A classroom contains 4 halls

 

[DHookom]

It isn’t clear whether you want to display results for a single room or use multiple rooms or a single or multiple halls.

 

[azhar2006]

It isn’t clear whether you want to display results for a single room or use multiple rooms or a single or multiple halls.

I have attached an example database for you. When I choose the crosstab query from the first drop-down list. Through the second combo box, can I create a filter for the sfrmQuery subform?

 

[Gasman]

Arnelgp has already done it for you?

 

[LarryE]

Approximately 17 classrooms, for example, the A classroom contains 4 halls

What is a "hall" and does each classroom have multiple "halls"? And does each "hall" have multiple supplies (paper, pins, pens etc.).
So A1 is actually classroom A Hall 1?

 

[azhar2006]

What is a "hall" and does each classroom have multiple "halls"? And does each "hall" have multiple supplies (paper, pins, pens etc.).
So A1 is actually classroom A Hall 1?

Thank you dear:
Yes, each class has multiple halls, but each hall has a responsible teacher who is in charge of these supplies. Meaning, we say the class (ِA) Teacher George is in charge of the hall (A1) Teacher Miller is in charge of the hall (A2) But they all return to the class (A) basically
I intended to add the teacher's name to a new field in the class table. Isn't this true?

 

[LarryE]

Thank you dear:
Yes, each class has multiple halls, but each hall has a responsible teacher who is in charge of these supplies. Meaning, we say the class (ِA) Teacher George is in charge of the hall (A1) Teacher Miller is in charge of the hall (A2) But they all return to the class (A) basically
I intended to add the teacher's name to a new field in the class table. Isn't this true?

Yes, you can add the teachers name field to the Class table.

 

[DHookom]

I'm not sure you have answered my question about select one or multiple of anything. You have been given an example of selecting one. I would have done this different but it works as expected. You should be able to apply the same logic to any filtering of a single hall or room.

If you want to be able to cherry-pick multiple halls or rooms, this gets significantly more complex and usually involves a multi-select list box. There are lots of resources on the web regarding using multi-select list boxes to filter forms and reports. If you continue to have questions, please come back and be thorough with your specifications.

 

[azhar2006]

I'm not sure you have answered my question about select one or multiple of anything. You have been given an example of selecting one. I would have done this different but it works as expected. You should be able to apply the same logic to any filtering of a single hall or room.

If you want to be able to cherry-pick multiple halls or rooms, this gets significantly more complex and usually involves a multi-select list box. There are lots of resources on the web regarding using multi-select list boxes to filter forms and reports. If you continue to have questions, please come back and be thorough with your specifications.

Hello dear DHookom
no . I don't want to choose multiple halls. What I want is to select one hall so that only this hall appears for me in the form and the other halls disappear. To avoid confusion in auditing.