How do I insert a line of text, if a certain number shows up in the re

[billquga]

Hello all,

I have 2 tables (Shipments_Late_Reasons) & (Shipment Tracking). I have 12 Query's (JANUARY)(FEBRUARY)etc.
I then have 12 reports, one for each month. Data is entered into the (Shipments_Tracking) table which include data like, the month an order shipped, specific details about the order, and if it was late or not. if it was late, a specific code # (1-100) is assigned to it. The table (Shipments_Late_Reasons) contains all the codes, followed by a description of what the code# represents:
CODE REASON
90 Held for Credit
The 12 querys pull data for the respective months, so a monthy report can be printed, created off the query for the specific month. Each of the 12 reports are exactly the same layout, containing the # on time, the number and other specific details. the only difference is the month the shipments occured in.
So far, this has all worked fine. But now I would like to create a new report that breaks down the data into seperate codes and give me specifics about that. so far i have the report pull the data from a new querey that only contains the late code and the Sales order # for a specific month. This data all comes from the table (Shipments_Tracking) where all the data is entered. The report does seperate all the different codes, give me a count of each code, and then a percentage of the whole:

SALES ORDER MO PRIMARY REASON LATE

AUGUST 10

AUGUST 10

AUGUST 10
Summary for 'Sales Order Mo' = August (6 detail records)
COUNT 3
PERCENT 33%

SALES ORDER MO PRIMARY REASON LATE

AUGUST 15

AUGUST 15

Summary for 'Sales Order Mo' = August (6 detail records)
COUNT
PERCENT 25%

Now the big question after the long explanation. Can I insert either a text line or the text line that coresponds to the late code, in the grouping with each summary on the report? Any help would be appreciated. Thanks!

 

[VogonPoet]

You just need to create a query that joins to the Shipments_Late_Reasons table. If you include the description field in your query results, you can include it in your report output.

Also, you may want to consider creating one table with all twelve months in it and adding a field that indicates the month/date of the record. You then insert a criteria/paramater into your query to print a report for a specific month. This will avoid having 12 different tables/reports that each must be maintained separately.

Michael Hodes
michaelhodes@managedcare.com