Query Options

S

Sondra

I have a database that requires approximately 50 different
reports. The only difference in the reports is a yes/no
field in the table:

Table:

Doc#
DocID
BinderA
BinderB
BinderC

Report for BinderA = yes
Report for BinderB = yes
Report for BinderC = yes

I know that I can write query for each separate
BinderReport; and I know I can write one query that can be
used for all binders by asking the user to identify the
binder they want.

But since not all the users are going to call the binder
by the same name, is there a way to use a dropdown option
for the user to choose the binder they want their report
for?

Thanks in advance.
 
D

Duane Hookom

You should not use field names that are binder names. Consider normalizing
your table structure so that if Doc# "123" is located in BinderB, it creates
a record in a related table like
tblDocBinders
===============
DocNumber
BinderNumber
This allows for easy querying. Plus if you need to add a new Binder, you
don't have to add a field and modify forms and reports and queries.

If you are unable to normalize your tables, consider creating a union query
that performs the same basic function.
 
S

Sondra

This was my first thought. Separate table with but I was
unable to make it work. My problem is that the document
might be in multiple binders.

Here is what I originally had:

TblDocument
===========
DocNumber>-----------------\
DocName |
|
tblDocBinders |
=============== |
DocNumber<-----------------/
BinderNumber<--------------\
|
tblBinder |
=============== |
BinderNumber>--------------/
BinderTitle

But I am brain dead on how to write the query so that the
user doesn't have to enter the information separately in
the "tblDocBinders" and in the "tblDocument". I know its
possible, but its not clicking. I want to be able to
create a form where the user would enter the Document
Information and have toggle buttons on the same form for
the user to choose which binders the Document is in.

Any help would be great.
 
D

Duane Hookom

Use a subform bound to tblDocBinders on a main form bound to tblDocument.
This is very much like Orders and OrderDetails.
 
Top