If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access 2003 - Report Problems
I hope someone can help, I am at my wits end on this. I am doing my taxes and
made a database to make retrieving and totaling my information easier. I finally figured out how to retrieve a report from a table but when I did, a field I had hidden on the table portion came up on the report as well. My question: How do I hide the field on the report that is hidden on the table? Question 2: How in the world do you get a field to add and have the total come up at the end? It seems so easy but I cannot make it happen. Ideally I'd like to get it to calculate it at the bottom of the table portion and the report as well. Please help. |
#2
|
|||
|
|||
Access 2003 - Report Problems
To delete the field from the report first open the report in design view.
Then select the control bound to field in question and delete it. Do the same with any label associated with the field if necessary. You may then have to adjust the layout by moving the controls around to fill in the gaps. Once you are happy with the layout save the report, though you can always open it again in design view to make further changes if necessary. To sum the values in a field at the bottom of the report add a text box to the report footer and set its ControlSource property so that it sums the field in question. So if you have a field called Amount say in the detail section, the ControlSource property of the text box in the footer would be: =Sum([Amount]) If report is grouped on a certain field you can also give the group a group footer and add a text box to the group footer to give you a subtotal in exactly the same way. You can't do this in the table itself. A table is just a bucket of data, and there is no real reason why it should ever need to be viewed in its raw datasheet view at all. Data should always be entered via forms, never directly. You can sum a field in a form in the same way as in a report by putting a text box in the form's footer or header. The form can be in single form view to show one record at a time, or in continuous forms view to show multiple rows, so there is no advantage in working directly with the table. Ken Sheridan Stafford, England marinegrl wrote: I hope someone can help, I am at my wits end on this. I am doing my taxes and made a database to make retrieving and totaling my information easier. I finally figured out how to retrieve a report from a table but when I did, a field I had hidden on the table portion came up on the report as well. My question: How do I hide the field on the report that is hidden on the table? Question 2: How in the world do you get a field to add and have the total come up at the end? It seems so easy but I cannot make it happen. Ideally I'd like to get it to calculate it at the bottom of the table portion and the report as well. Please help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
#3
|
|||
|
|||
Access 2003 - Report Problems
Mr. Sheridan - Thank you for answering my question. I did try your suggestion
and it comes up as: #error I'm not sure what I am doing wrong. Ideally I would like to retrieve data from the table, go into Report and print the info I need, with a total at the end. If I group it will I be able to accomplish it or am I stuck computing each report as they are generated? I was asking about the ability to total a Table for almost the same reason; pull the info I need and at the same time see the totals on that field - without having to go to a Report. Again, I appreciate you taking the time to answer my question. "KenSheridan via AccessMonster.com" wrote: To delete the field from the report first open the report in design view. Then select the control bound to field in question and delete it. Do the same with any label associated with the field if necessary. You may then have to adjust the layout by moving the controls around to fill in the gaps. Once you are happy with the layout save the report, though you can always open it again in design view to make further changes if necessary. To sum the values in a field at the bottom of the report add a text box to the report footer and set its ControlSource property so that it sums the field in question. So if you have a field called Amount say in the detail section, the ControlSource property of the text box in the footer would be: =Sum([Amount]) If report is grouped on a certain field you can also give the group a group footer and add a text box to the group footer to give you a subtotal in exactly the same way. You can't do this in the table itself. A table is just a bucket of data, and there is no real reason why it should ever need to be viewed in its raw datasheet view at all. Data should always be entered via forms, never directly. You can sum a field in a form in the same way as in a report by putting a text box in the form's footer or header. The form can be in single form view to show one record at a time, or in continuous forms view to show multiple rows, so there is no advantage in working directly with the table. Ken Sheridan Stafford, England marinegrl wrote: I hope someone can help, I am at my wits end on this. I am doing my taxes and made a database to make retrieving and totaling my information easier. I finally figured out how to retrieve a report from a table but when I did, a field I had hidden on the table portion came up on the report as well. My question: How do I hide the field on the report that is hidden on the table? Question 2: How in the world do you get a field to add and have the total come up at the end? It seems so easy but I cannot make it happen. Ideally I'd like to get it to calculate it at the bottom of the table portion and the report as well. Please help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 . |
#4
|
|||
|
|||
Access 2003 - Report Problems
Its important to understand that a table stores data, a report presents it
along with information derived from the data. Data are values of a particular attribute, e.g. Widget might be a value of an attribute Product; 200 grams a value of attribute Weight. Attributes are represented by columns in a table, so these could be columns in a table Products. In another related table OrderDetails, a column might represent an attribute Quantity. If someone orders 10 Widgets the total weight is 2000 grams, but this is an aggregated value computed by the product of Weight and Quantity. This would not be stored in a table but computed when required. This can be in a query or in a computed column in a report. So a report of orders might have the following in its detail section Product Quantity Total Weight Widget 20 2000 The product and quantity would be bound text box controls in the report, i.e. their ControlSource properties would in each case be the name of the product and quantity columns (aka fields) in the tables, but the Total Weight text box would be an unbound computed control, with a ControlSource property of = [Quantity]*[Weight]. In this case the computation is across the row of data. Taking things a step further, an order will probably have a number of order lines, and we might want to get the total quantity of all items ordered and the total weight of all those items. This is done by adding unbound text boxes to the report footer. To get the total quantity the CiontrolSource is: =Sum([Quantity]) To get the total weight of all items we don't sum the computed TotalWeight of each item, but the original expression, like so: =Sum([Quantity]*[Weight]) If the report is for just one order these text boxes could go in the report footer and give the totals for that one order. If the report covers more than one order then this would give the totals for all the orders, which we might well want, but we'd also want the totals for each order as separate sub- totals. To do this we'd group the report by a column such as OrderNumber, which is done in report design view via the Sorting and Grouping dialogue, and give the group a group footer. This would contain two text boxes identical to those in the report footer, with exactly the same ControlSource properties. One thing to be aware of is that you can only aggregate values like this in a group or report footer (or header), not in a page footer (or header). It is possible to show page totals in a page footer, but it has to be done differently. When you build a report like this it will reflect the current data in the underlying tables whenever the report is opened, whether on screen in print preview or sent to a printer. The data shown in the report can be restricted each time, however, by basing the report on a query which includes parameters which prompt the user for one or more values when the report is opened. You'll find information about parameters in the Help system. In the above case for instance the report might be restricted to a particular customer, or it might be restricted to orders between two dates, or a combination of both of these. I hope that the above, while obviously not directly analogous to your situation, will give you a general idea of how reports work. I'd be happy to provide more specific guidance related to your specific needs, but would need a more detailed description of what data is held in the columns of your table or tables, and just how you want it presented in a report, not only in terms of the layout, but also in how you might want to restrict at runtime what data is printed, e.g. by entering a start and end date. Unfortunately I shall be away for the rest of this week, so would not be able to get back to you before Sunday at the earliest. It may well be that others will be able to step in and help you in the meantime however. Ken Sheridan Stafford, England marinegrl wrote: Mr. Sheridan - Thank you for answering my question. I did try your suggestion and it comes up as: #error I'm not sure what I am doing wrong. Ideally I would like to retrieve data from the table, go into Report and print the info I need, with a total at the end. If I group it will I be able to accomplish it or am I stuck computing each report as they are generated? I was asking about the ability to total a Table for almost the same reason; pull the info I need and at the same time see the totals on that field - without having to go to a Report. Again, I appreciate you taking the time to answer my question. To delete the field from the report first open the report in design view. Then select the control bound to field in question and delete it. Do the [quoted text clipped - 34 lines] like to get it to calculate it at the bottom of the table portion and the report as well. Please help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
#5
|
|||
|
|||
Access 2003 - Report Problems
..Mr. Sheridan -
Thank you again for taking the time to answer my question. I think I have found a solution to my problem, which was obtaining a total for an amount on a report. My knowledge of Access is minimal, to say the least. Most people would probably use Excel but I think Access is much more flexible and versatile. I put all my information in and then I would retrieve what I needed by filtering everything else out. You asked about the information I was working with. It was rather simple - date-name of person/business the money went to-account it came from-check number/ATM/credit card-amount-tax deductable-catagory it was used for-misc. Once eveything was done I wanted to be able to go in and pull up any and all information of a specific nature. Once I had filtered everything and I had just the information I needed I would go to the top to the -NEW OBJECT - AUTO FORM button. Then I would scroll down to REPORT. Then I would go to AUTOREPORT-TABULAR. I hit OK. And there it was my report. It seemed so easy, but no total. I would go into design and dress it up, but for the most part that was it. I tried everything to get the sum in that design window, I just couldn't get get. What I ended up doing was going to NEW OBJECT - AUTO FORM, and then would group what I needed which allowed me the oportunity for a calculation button. In the end I was able to group the things that I needed and get my total amount that was paid for each catagory. As I use it more I will become more familiar with it. I can honestly say there are not too many people that know Access. You are an exception to the rule. So thank you again for the time you took to answer my question. I greatly appreciate your help - Melanie N. - North Carolina, USA "KenSheridan via AccessMonster.com" wrote: Its important to understand that a table stores data, a report presents it along with information derived from the data. Data are values of a particular attribute, e.g. Widget might be a value of an attribute Product; 200 grams a value of attribute Weight. Attributes are represented by columns in a table, so these could be columns in a table Products. In another related table OrderDetails, a column might represent an attribute Quantity. If someone orders 10 Widgets the total weight is 2000 grams, but this is an aggregated value computed by the product of Weight and Quantity. This would not be stored in a table but computed when required. This can be in a query or in a computed column in a report. So a report of orders might have the following in its detail section Product Quantity Total Weight Widget 20 2000 The product and quantity would be bound text box controls in the report, i.e. their ControlSource properties would in each case be the name of the product and quantity columns (aka fields) in the tables, but the Total Weight text box would be an unbound computed control, with a ControlSource property of = [Quantity]*[Weight]. In this case the computation is across the row of data. Taking things a step further, an order will probably have a number of order lines, and we might want to get the total quantity of all items ordered and the total weight of all those items. This is done by adding unbound text boxes to the report footer. To get the total quantity the CiontrolSource is: =Sum([Quantity]) To get the total weight of all items we don't sum the computed TotalWeight of each item, but the original expression, like so: =Sum([Quantity]*[Weight]) If the report is for just one order these text boxes could go in the report footer and give the totals for that one order. If the report covers more than one order then this would give the totals for all the orders, which we might well want, but we'd also want the totals for each order as separate sub- totals. To do this we'd group the report by a column such as OrderNumber, which is done in report design view via the Sorting and Grouping dialogue, and give the group a group footer. This would contain two text boxes identical to those in the report footer, with exactly the same ControlSource properties. One thing to be aware of is that you can only aggregate values like this in a group or report footer (or header), not in a page footer (or header). It is possible to show page totals in a page footer, but it has to be done differently. When you build a report like this it will reflect the current data in the underlying tables whenever the report is opened, whether on screen in print preview or sent to a printer. The data shown in the report can be restricted each time, however, by basing the report on a query which includes parameters which prompt the user for one or more values when the report is opened. You'll find information about parameters in the Help system. In the above case for instance the report might be restricted to a particular customer, or it might be restricted to orders between two dates, or a combination of both of these. I hope that the above, while obviously not directly analogous to your situation, will give you a general idea of how reports work. I'd be happy to provide more specific guidance related to your specific needs, but would need a more detailed description of what data is held in the columns of your table or tables, and just how you want it presented in a report, not only in terms of the layout, but also in how you might want to restrict at runtime what data is printed, e.g. by entering a start and end date. Unfortunately I shall be away for the rest of this week, so would not be able to get back to you before Sunday at the earliest. It may well be that others will be able to step in and help you in the meantime however. Ken Sheridan Stafford, England marinegrl wrote: Mr. Sheridan - Thank you for answering my question. I did try your suggestion and it comes up as: #error I'm not sure what I am doing wrong. Ideally I would like to retrieve data from the table, go into Report and print the info I need, with a total at the end. If I group it will I be able to accomplish it or am I stuck computing each report as they are generated? I was asking about the ability to total a Table for almost the same reason; pull the info I need and at the same time see the totals on that field - without having to go to a Report. Again, I appreciate you taking the time to answer my question. To delete the field from the report first open the report in design view. Then select the control bound to field in question and delete it. Do the [quoted text clipped - 34 lines] like to get it to calculate it at the bottom of the table portion and the report as well. Please help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201003/1 |
Thread Tools | |
Display Modes | |
|
|