Report design and Converting a paper form to a Access Report

J

John O'Malley

I need to design a report that matches a form we currently
use. The report has 25 categories of expenses that are
totaled from anywhere from one entry to a hundred entries
per category while some categories may not be used. The
report is filled out and filed monthly.

Presently the database has three tables.
Table One: Daily Records - The fields are:
([date], [description], [account], [transaction type])
Table Two: Accounts - The fields are: ([account], [type])
Table Three: Income - Expense - the fields are:
([transaction type])

Table two and three are lists to autofill table one's
[account] and [type] fields.

Presently, my report, based on a query with totals, totals
the month's activity and lists the totals for each category
from table one. This is fine; but I need to match my
existing form created in Excel that shows categories on the
left and amounts on the right.

I know how to use text boxes to reflect the form's
appearance. It is the matter of having only the total of a
specific category hit an exact spot on the form and to have
nothing show on the categories that have no activity.

I can redesign the tables if needed. I just can not change
the form.
Any ideas?
John
 
L

Larry Linson

If you create a query that returns Records having two fields, Category and
Amount, as it appears to me you describe, it should be trivial to create a
Report with the categories on the left and the amounts on the right -- the
Report Wizard for tabular format should give you such a report.

Thus, I think I must be missing some key point of your description. Can you
clarify?

Larry Linson
Microsoft Access MVP
 
G

Guest

Thank you for your help. In essence this is what I want to
do the problem manifests itself when each category may have
multiple records and some are null. The report needs to
replicate a form -- similar to a spreadsheet -- that some
records will be used and others will not. Category ONE must
hit a certain spot on the form with just a total. Category
TWO whether there is activity or not must hit a certain
spot on the form and so forth.

Herein is my problem. I have a report that only produces
the totals of categories with activity. What I need is for
it to replicate this form with only totals. Someone
previously suggested an intermediate query as the base of
the report....

I would be glad to email you a copy of the .mdb for you to
see it.

Thanks,
John
-----Original Message-----
If you create a query that returns Records having two fields, Category and
Amount, as it appears to me you describe, it should be trivial to create a
Report with the categories on the left and the amounts on the right -- the
Report Wizard for tabular format should give you such a report.

Thus, I think I must be missing some key point of your description. Can you
clarify?

Larry Linson
Microsoft Access MVP


I need to design a report that matches a form we currently
use. The report has 25 categories of expenses that are
totaled from anywhere from one entry to a hundred entries
per category while some categories may not be used. The
report is filled out and filed monthly.

Presently the database has three tables.
Table One: Daily Records - The fields are:
([date], [description], [account], [transaction type])
Table Two: Accounts - The fields are: ([account], [type] )
Table Three: Income - Expense - the fields are:
([transaction type])

Table two and three are lists to autofill table one's
[account] and [type] fields.

Presently, my report, based on a query with totals, totals
the month's activity and lists the totals for each category
from table one. This is fine; but I need to match my
existing form created in Excel that shows categories on the
left and amounts on the right.

I know how to use text boxes to reflect the form's
appearance. It is the matter of having only the total of a
specific category hit an exact spot on the form and to have
nothing show on the categories that have no activity.

I can redesign the tables if needed. I just can not change
the form.
Any ideas?
John


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top