Creating Reports

[Guest]

Hi everyone!

All my experience with Access has been with setting up tables and input
forms. Now I have to create some reports and I'm having trouble figuring out
how to do it. My 1st report consists of several columns, NCR #, Date Opened,
Part #, Description, Total Qty, Qty Remaining, Unit Price, Total, Rework PO
#, Debit #, Status Code # & Status Code Description.

Most of the fields come from one table, however the Part # and Description
come from the Part# table, and the Status Code and Status Code Description
come from the Status Code table. The problem I'm having is that when I use
the wizard, if I enter the information for each table separately, I get data,
but if I combine all three of the tables there is no data. I even tried to
set up a query based on the three tables, but that didn't work either. The
data has to be grouped together according to the Status Code #. Also, I have
to have some way to set a date range so that only the data within that date
range is displayed. So, obviously I need a lot of help here!!! Someone
please tell me what I'm doing wrong and please walk me through the best way
to set this report up. I'm working in Access 2000-2003.

In addition, the Total column is a calculated field, so I need to put a
formula in there to multiply the Unit Price X Qty Remaining.

Then there has to be a grand total based on the entries for each Status
Code. Hopefully I'm making myself understood, but if not, please let me know
what information I can provide to get the help I desparately need!

Thank you for your help.

Owl Lover

 

[Allen Browne]

Create a query that has all the fields from the different tables.
You will then be able to create the report from that query.

If you get no records in the query, you need to use outer joins and watch
how you specify the criteria on the fields from the outer side of the join.
For details, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Once you have the query showing the correct records, you can use a form to
supply the limiting dates. For details, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

You can also create the calculated field in the query. Your example is
almost the same as this one:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

Once you get the query doing all of that, and create the report based on
that query, you can add a text box to the Report Footer section to show the
total. (If you don't see any Report Footer section in report design view,
it's on the View menu.) The Control Source for this text box will be
something like this:
=Sum([Amount])
substituting your field name for Amount.

There's lots of steps there: be sure to take one at a time. It's certainly
worth learning this stuff if you are going to be developing reports in
Access.