Just exactly what i wanted to to my Taxes for my Employees. I was moving data from Access to Excel to do the Calculation. Your method worked. Thank you.
Hi Lisa..Did you do a quick vid before this one combining 2 queries into one and then grouped to produce the one used in this vid? It was fairly complicated with many headers, footers and grouping to handle the one to many relation and implemented totals and subtotals If yes I cant find it anywhere or is this the only one? All the sub form framework titles etc had been deleted and it appeared as one report with considerable grouping. It was a real keeper. Thks.
Yes, this report is based on the ProductsByCompany query that pulls fields from the Customers, Orders, Order Details, and Products tables, and that query is created in the previous screencast in the Microsoft Access 101 UA-cam playlist, "Building Reports using Sections, Labels, and Text Boxes" ua-cam.com/video/X0OrOfHB2rM/v-deo.html
Here's the link to the entire Microsoft Access A to Z playlist. It starts by explaining why we'd want to use Access in the first place. :) ua-cam.com/video/FJ00JkV0D3M/v-deo.html
Hi Lisa Do you have a short screen cast on adding a sub report to a report and the editing required to remove the visual frame work surrounding the sub report.? Maybe that is not doable or only available when doing it as a form object. I have ordered your 2019 access text but the ETA is a least 2 weeks ( UK to Canada). If my question is answered in your text then I can wait and get my answer from your text. Thks
OK, subreports are not nearly as common or useful as subforms and that's because of the powerful grouping sections and sorting features of a single report. The only 2 reasons you need subreports is ...1. you want to present subtotals in an order that you can't achieve with a single report. For example, you want subtotals to print on the first page, and then you want all details to print on pages 2-to the end. You can get all of those subtotals to print before or after their detail records in one report, but you can't force them to print together on the first page without using two reports, a report within a report aka a subreport.
And 2. You want two reports that are based on different recordsets to print one after another. For example, you want to print 2 reports that are based on completely different information each Monday morning so you chain those two reports together using a subreport control in the report footer section of the first report and then all you have to do is print the main report, and the second report automatically prints too. In summary, I've not had much need for subreports.
That doesn't mean I shouldn't do a screencast on subreports, but wanted to put this in perspective to subforms. I use subforms 100 to 1 (or more!) than subreports.
@@lfriedrichsen Thks Lisa. I am still waiting for your book. My problem is probably based on my approach. One table (primary keyed) has a Budget amt entered under a division/cost code structure. A second table (foreign keyed) has the invoice cost entered under the same coding structure but can have many invoices with the same coding structure but from different suppliers (the many). The tables are linked. I want to calculate the net over/under (budget minus expense at the division/cost code level ie "Project Construction Costing Report .. Net". Is the better approach to build a query and take it to a report for printing as I am struggling with the forms route conceptually? I have put considerable time into a Reort/SubReport route thus far but do not seem to be able to achieve the final objective at this point (learning curve). The book will help I am sure. I am making this more complicated than it should be for sure.
@@TimSmith-df2gz If I understand this correctly, the Departmental Budget table is linked to the Invoices table in a one-to-many relationship using a common Code field. So one Departmental Budget record can have many invoices associated with it. You're trying to compare the budget dollar value to the combined invoice amounts for a particular department to see where you stand from a "here's what we actually spent" compared to what was budgeted standpoint. So yes, you'll want to build a query with fields from both tables. If you only want to see the invoice information summarized, you could use a summary (aka aggregate) query to group the records by department, and SUM the amount field from the Invoices table. Given the same budget number will appear for each department, you do NOT want to sum that field, but rather, choose MIN or MAX, doesn't matter given the budget value for each department is the same value. My UA-cams on aggregate queries should help on that, and then you could "pretty it up" by running the Report Wizard against that summary query. If you want to see the information including each invoice detail on the report, then you're NOT going to want to use a summary query, but rather, feed all of the individual records to the report from a mere select query that selects all of the records from both tables that you want to see on the report. You'll want to have a group header (and/or group footer) section for each department, and in that section you'll include all fields from the departments table such as department name and budget value. In the detail section of the report you'll include all of the fields from the invoices table. To compare the budget value of the department to the sum of the invoice amounts you'll need to build a calculated field on the report that looks something like this: =Sum([Amount]) in the department group header and/or group footer sections. Once you have both the Budget field and the summarized invoice amount in the same department group header (or group footer) sections, you can build yet another calculated field that calculates the difference. Something like: =[Budget]-Sum([Amount]) So in summary, and if I'm understanding this correctly, you do NOT want to use a subreport for this. You first just need to put all of the fields you want in one query. Then run the Report Wizard against that query and if you want to see all of the details for each invoice, choose the option to group the records by department. That will get you pretty far and set up the Department Header and/or Department Footer sections that you'll use for the calculations you need for each department to compare their budget value to the sum of the invoices for that department. Good luck!
Two thoughts -- using the subform control you can place one report within another. Or, consider the "D" functions (D for domain). Dfunctions such a DSUM allow you to add a calculation to a report from a domain (a set of records) that is different from the Record Source of the report.
@@lfriedrichsen Thnx for your prompt response. My problem is slightly different which I was unable to explain. I have a report where there are subtotals based on results of different teams. Subtotal 1 is for team A which is 100. Subtotal 2 for team B ( say 80) and so on. Now I want to make a fresh report where all these different subtotals ( Table being the same) will be included and be sorted highest to lowest. For example, 100 will be on the top followed by other teams secured less one by one. Think it should be possible but I don't have the knowledge about it's execution. That's why solicited your kind advice. A video tutorial would be of great help for us. Regards.
Base the new report on a new query that already subtotals the data by company and sorts the subtotals in descending order. If you need help with this see my UA-cams on summary queries. ua-cam.com/video/pMCc_cwJaMk/v-deo.html If you want to print the two reports together, use a subreport control to connect them.
Just exactly what i wanted to to my Taxes for my Employees. I was moving data from Access to Excel to do the Calculation. Your method worked. Thank you.
You're welcome! You can find all of my UA-cams organized at learncs.w3spaces.com/
Excellent tutorial. Learn a lot from your video. all thumbs up & definately subscribe! thank you for teaching
Thanks for taking the time to comment! All of my UA-cams are organized at learncs.w3spaces.com. Take care and best wishes.
Thank you so much Lisa for your valuable link. i am checking it out now. Will thumbs up for all your videos
Thank you Madam. it was useful for me.
You're welcome! You can find all of my UA-cams organized at learncs.w3spaces.com/
Hi Lisa..Did you do a quick vid before this one combining 2 queries into one and then grouped to produce the one used in this vid? It was fairly complicated with many headers, footers and
grouping to handle the one to many relation and implemented totals and subtotals If yes I cant find it anywhere or is this the only one? All the sub form framework titles etc had been deleted and it appeared as one report with considerable grouping. It was a real keeper. Thks.
Yes, this report is based on the ProductsByCompany query that pulls fields from the Customers, Orders, Order Details, and Products tables, and that query is created in the previous screencast in the Microsoft Access 101 UA-cam playlist, "Building Reports using Sections, Labels, and Text Boxes" ua-cam.com/video/X0OrOfHB2rM/v-deo.html
Here's the link to the entire Microsoft Access A to Z playlist. It starts by explaining why we'd want to use Access in the first place. :) ua-cam.com/video/FJ00JkV0D3M/v-deo.html
@@lfriedrichsen Most helpful Thks Lisa.. Shopping for your 2019 text.
Hi Lisa
Do you have a short screen cast on adding a sub report to a report and the editing required
to remove the visual frame work surrounding the sub report.? Maybe that is not doable or only available when doing it as a form object. I have ordered your 2019 access text but the
ETA is a least 2 weeks ( UK to Canada). If my question is answered in your text then I can wait and get my answer from your text. Thks
OK, subreports are not nearly as common or useful as subforms and that's because of the powerful grouping sections and sorting features of a single report. The only 2 reasons you need subreports is ...1. you want to present subtotals in an order that you can't achieve with a single report. For example, you want subtotals to print on the first page, and then you want all details to print on pages 2-to the end. You can get all of those subtotals to print before or after their detail records in one report, but you can't force them to print together on the first page without using two reports, a report within a report aka a subreport.
And 2. You want two reports that are based on different recordsets to print one after another. For example, you want to print 2 reports that are based on completely different information each Monday morning so you chain those two reports together using a subreport control in the report footer section of the first report and then all you have to do is print the main report, and the second report automatically prints too. In summary, I've not had much need for subreports.
That doesn't mean I shouldn't do a screencast on subreports, but wanted to put this in perspective to subforms. I use subforms 100 to 1 (or more!) than subreports.
@@lfriedrichsen Thks Lisa. I am still waiting for your book. My problem is probably based on my approach. One table (primary keyed) has a Budget amt entered under a division/cost code structure. A second table (foreign keyed) has the invoice cost entered under the same coding structure but can have many invoices
with the same coding structure but from different suppliers (the many). The tables are linked. I want to calculate the net over/under (budget minus expense at the
division/cost code level ie "Project Construction Costing Report .. Net". Is the better approach to build a query and take it to a report for printing as I am struggling with the forms route conceptually? I have put considerable time into a Reort/SubReport route thus far but do not seem to be able to achieve the final objective at this point (learning curve). The book will help I am sure. I am making this more complicated than it should be for sure.
@@TimSmith-df2gz If I understand this correctly, the Departmental Budget table is linked to the Invoices table in a one-to-many relationship using a common Code field. So one Departmental Budget record can have many invoices associated with it.
You're trying to compare the budget dollar value to the combined invoice amounts for a particular department to see where you stand from a "here's what we actually spent" compared to what was budgeted standpoint.
So yes, you'll want to build a query with fields from both tables.
If you only want to see the invoice information summarized, you could use a summary (aka aggregate) query to group the records by department, and SUM the amount field from the Invoices table. Given the same budget number will appear for each department, you do NOT want to sum that field, but rather, choose MIN or MAX, doesn't matter given the budget value for each department is the same value.
My UA-cams on aggregate queries should help on that, and then you could "pretty it up" by running the Report Wizard against that summary query.
If you want to see the information including each invoice detail on the report, then you're NOT going to want to use a summary query, but rather, feed all of the individual records to the report from a mere select query that selects all of the records from both tables that you want to see on the report. You'll want to have a group header (and/or group footer) section for each department, and in that section you'll include all fields from the departments table such as department name and budget value. In the detail section of the report you'll include all of the fields from the invoices table.
To compare the budget value of the department to the sum of the invoice amounts you'll need to build a calculated field on the report that looks something like this: =Sum([Amount]) in the department group header and/or group footer sections.
Once you have both the Budget field and the summarized invoice amount in the same department group header (or group footer) sections, you can build yet another calculated field that calculates the difference. Something like: =[Budget]-Sum([Amount])
So in summary, and if I'm understanding this correctly, you do NOT want to use a subreport for this. You first just need to put all of the fields you want in one query. Then run the Report Wizard against that query and if you want to see all of the details for each invoice, choose the option to group the records by department. That will get you pretty far and set up the Department Header and/or Department Footer sections that you'll use for the calculations you need for each department to compare their budget value to the sum of the invoices for that department. Good luck!
Can I make a fresh reports taking subtotals from another report. Kindly help.
Two thoughts -- using the subform control you can place one report within another. Or, consider the "D" functions (D for domain). Dfunctions such a DSUM allow you to add a calculation to a report from a domain (a set of records) that is different from the Record Source of the report.
@@lfriedrichsen Thnx for your prompt response. My problem is slightly different which I was unable to explain. I have a report where there are subtotals based on results of different teams. Subtotal 1 is for team A which is 100. Subtotal 2 for team B ( say 80) and so on. Now I want to make a fresh report where all these different subtotals ( Table being the same) will be included and be sorted highest to lowest. For example, 100 will be on the top followed by other teams secured less one by one. Think it should be possible but I don't have the knowledge about it's execution. That's why solicited your kind advice. A video tutorial would be of great help for us. Regards.
Base the new report on a new query that already subtotals the data by company and sorts the subtotals in descending order. If you need help with this see my UA-cams on summary queries. ua-cam.com/video/pMCc_cwJaMk/v-deo.html If you want to print the two reports together, use a subreport control to connect them.