An alternate to pivot table form

[uncle-lai]

In my attached file I use a report with sub-report to display the stock of garments in different colors and sizes. I have tried using pivot tables and crosstab queries, at last I choose this way because it is formatable. Originally I wished to use a form to do so but could not succeed. I post it here first to share this idea with other newbie like me and secondly to seek advice or comments from Access experts. Any response is very much welcomed.

Peter

 

[Rose412]

A very advanced report format!

Could you tell us real newbies how you can make the records in the report run from left to right across the page?

A step by step description of how the report with the subreports is produced would help us understand the techniques that you have applied.

Rose

 

[uncle-lai]

Sorry,

This is the 4th time I compose a reply. Everytime I click on the Submit Reply button my browser brought me back to the login page and everthing I wrote was gone and I have to compose the whole message again. Please be patient to view my next post.

Peter

 

[uncle-lai]

Basically my final report is composed of a main report with 2 sub-reports, all based on simple queries. One sub-report displays the Sizes as Heading for Columns, the other sub-report is placed at the end of the main report to show the Size Sub-Totals. If you open the reports in design mode you will find that they all depend on simple Select Queries.

All records in the main and sub reports are displayed in small columns. To see the format settings of the report go to File - Page Setup - Columns. The options are plain and easy to understand. What is essential is to place the sub-reports at positions so that the Size Headings and Size Sub-Totals align with the columns that contain the corresponding data.

Pay attention to the Property of the Header Sections and Detail Section, the options in the Format page - [Force New Page]; [New Row Or Col]; [Repeat Section] - can change the format of your report too.

Open the query [tblStockCtb], the form [PivotTable Form] too. They all display the same data as my stock report.


* This time I copied all the contents to NotePad before I clicked the Submit button, my browser brought me back to the log in page again. I have to re-log in and paste back the contents.

Peter

 

[Rose412]

I have tried very hard to imitate your report, but I just couldn't put the Quantities in the correct columns.

Could you take a look at my report and sub-reports and tell me what I have done wrong? My reports are prefixed with X. They uses your select queries.

Thanks in advance.

 

[Rose412]

The attached image shows your report along with mine.

 

[uncle-lai]

Rose

Open your report in design mode, on the main menu select View - Sorting & Grouping, under [Color] group select [Size]. The default sorting is Ascending. This will sort the Sizes in "Alphabetical" order. Then your report will look the same as mine.

Glad you can make use of it.

Wish us luck in Access Progaming.

Peter

 

[Rose412]

Thank you so much Peter! That fixed it.

As the number of columns would dynamically change, in the past it was very difficult to build a report off a crosstab query. Even the report wizard refuses to build such a report (it won't allow us to select any fields from a crosstab query.) You have found a way to do it rather easily.

Thanks again.

Rose

 

[uncle-lai]

You are welcome. I'm so happy that there's at least one person appreciates my invention!

Peter