Report

[Guest]

Hi all,

Again, I've come to the experts to figure out how to do something! I'm
creating a report in Access 2000-2003. I have two tables that I'm pulling
information from. The first problem is that I have to sort it according to
each Status Code #. I have it set up with headings at the top, then below
that is where the information is supposed to come in, and the below that is a
line where it registers the status code for the above information with the
Status Code description. Even though I'm taking it from the same table, I
can get the status code to show up but I can't get the description to show.
Does anyone have any ideas or maybe you need information that I'm not giving
you.

Problem 2 - There are two fields that are not on any tables that they want
in the report, both calculated fields. One is a Quantity Remaining field and
one is a Total field. The total field is a calculation of the Unit price X
Quantity remaining. But again, I can't seem to get the calculation right in
order for it to appear in the same line as the status code. What am I doing
wrong?

Problem 3 - I need to have a way for the user to identify which month they
want the report to run for. So, I guess the best way would be to have a box
pop up when they open the report asking for the month. Again, I'm not sure
how to do this. By putting in, say, April, the report is supposed to go to
the table and pull all the NCR's for the month of April and sort them
according to Status Code.

I'm not sure if I've given you enough information to help me, but just email
me back and let me know what you need and I will try to give it to you.
Thank you in advance for the help.

Owl Lover

 

[Guest]

1. To show the status code description on the report include the referenced
table (StatusCodes say) containing the description in the report's underlying
query, joining it on the status code # columns. Return the description as a
column in the query in addition to the column with the status code #. Group
the report by the latter but include a text box bound to the former in the
group header.

2. For the computed values you don't say where these values are being
computed, but you can either include a query which returns the quantity
remaining values in the report's underlying query, joining it on the relevant
columns and bind a text box to the quantity remaining column in the query's
result table. For the Total you can then use a computed control in the
report (=[Quantity Remaining]*[Unit Price]. Alternatively for the quantity
remaining you might be able to call a the DSum function in an expression as
the ControlSource property of an unbound text box, passing an appropriate
criterion to it. In this case you'd need to repeat this expression as part
of the ControlSource for the Total control rather than referencing the Total
Quantity control directly. Without more information I can't be more
categorical than that, however.

3. You'll need parameters for both year and month to distinguish between
the same months in different years, the months should be entered as numbers 1
to 12, so the WHERE clause for the report's query would be something like
this:

WHERE YEAR([TransactionDate]) = [Enter year:]
AND MONTH([TransactionDate]) = [Enter month as a number 1 to 12:]

When the report is opened the user would be prompted for the year and month.
A more developed solution would be to make the parameters references to
controls on a dialogue form from which the report is opened. That way you
can use combo boxes for the user to select a year and month from, and in the
case of the latter, show the month names but include the numbers in a hidden
column of the combo box's RowSource as its bound column.

Ken Sheridan
Stafford, England