Printing reports associated with certain Criteria

[Erin]

I have a database with work order information. It has a
work order number and a series of yes/no fields for
services required. I need to be able to enter the work
order number and print only the fields checked 'yes' in
the table. The reports are already made. What kind of
query or macro would this require and how would I
implement it?

 

[Steve Schapel]

Erin,

This relates to the query that the report is based on. Presumably you
mean you will enter the required work order number into an unbound
textbox on a form. In this case, just refer to the textbox in the
criteria of the work order number field in your query, using syntax
equivalent to...
[Forms]![NameOfYourForm]![NameOfCriteriaTextbox]

As regards your "series of yes/no fields for services required", my
best advice is to ask a question... Is the structure of your database
set in concrete, or are you able to consider a change? On the basis
of what you have told us so far, I would be 99.9% sure that there is
an error in your table design. If you would care to post back with
some examples of what these yes/no fields relate to, someone will be
able to advise further on this aspect.

- Steve Schapel, Microsoft Access MVP

 

[Erin]

The database will track work orders and inventory for a
sign manufacturing co. There are about 10 areas signs go
through when made however not all signs need all areas.
The table I made lists the work order#, the 10 areas as
yes/no fields, customer info, etc. There is a report for
each area (already created)along with a work order,
customer service eval and permit tracking. Not all signs
need all areas and those needed are checked. Two reports,
Work Order and Customer Eval always need to print but the
others are variable. I need to be able to create something
when printing the reports to enter the work order# and
print only the reports pertaining to that #. The database
if adaptable and I haven't made any forms. Is there an
easy way to do this? I am willing to do difficult as well.
Any suggestions are terrific. Database will also be
adapted to track all inventory as well which required
connecting inventory info with work order info

-----Original Message-----
Erin,

This relates to the query that the report is based on. Presumably you
mean you will enter the required work order number into an unbound
textbox on a form. In this case, just refer to the textbox in the
criteria of the work order number field in your query, using syntax
equivalent to...
[Forms]![NameOfYourForm]![NameOfCriteriaTextbox]

As regards your "series of yes/no fields for services required", my
best advice is to ask a question... Is the structure of your database
set in concrete, or are you able to consider a change? On the basis
of what you have told us so far, I would be 99.9% sure that there is
an error in your table design. If you would care to post back with
some examples of what these yes/no fields relate to, someone will be
able to advise further on this aspect.

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

Erin,

Thanks for the clarification.

In a nutshell, you should not have the 10 yes/no fields in your table
for the 10 areas. Databases do not work well like this. This is
known as the "fields as data trap". The future usage and
effectiveness of your database will be affected by your data
structure. Correct design of your tables would be something along
these lines...

Table: Customers
CustomerID
CustomerName
CustomerAddress
etc

Table: Areas
AreaID
NameOfArea
any other infor relating to each area

Table: WorkOrders
WorkOrderID
DescriptionOfSign
CustomerID

Table: SignAreas
SignAreaID
WorkOrderID
AreaID

Obviously I don't know how your business works, so this is just a
rough outline of the kind of thing required. I have made the
assumption that each work order relates to only one sign. If this is
not correct, and more than one sign can be included in any given work
order, then the model above is of course immediately wrong. At first
glance it may appear that this approach is more complicated/difficult
than what you've got now, but in fact it is simpler, and as mentioned
before, in keeping with database principles.

Please feel free to post back if you would like to discuss further.

- Steve Schapel, Microsoft Access MVP