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.

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

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

.

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

..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