Reports with multiple tables

[cmarsh]

The layout---
5 Fields to be searched pre table
FieldA, FieldB, FieldC, FieldD, FieldE

I have a db with 25 tables. In each of the tables there are multiple
fields, but I only want to search through 5 of them (listed above). I want a
user to select on what field to search and by what criteria (i.e. Where
FieldA=123), and then display this is the report. In the report I want to
display the results, but also other information that attached to the record
or records(date, time, etc.). How would I go about doing this?

 

[Jerry Whittle]

To have even a chance of searching 5 fields in 25 different tables, you would
need to write 25 select queries each of which would have 5 OR statements in
the Where clause then you would need to Union these 25 queries.

Your best bet is to put all this data into one table.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.

 

[cmarsh]

Well I have a question then. I have 25 different checklist for documents.
So each checklist's questions are different. Now when they are all in 1
table, and a field is selected to pull up a product, I get any information
regarding that product, even though the information wont match the checklist.
This is why I thought of using seperate tables. What would you recommend on
doing here?

 

[John W. Vinson]

The layout---
5 Fields to be searched pre table
FieldA, FieldB, FieldC, FieldD, FieldE

I have a db with 25 tables. In each of the tables there are multiple
fields, but I only want to search through 5 of them (listed above). I want a
user to select on what field to search and by what criteria (i.e. Where
FieldA=123), and then display this is the report. In the report I want to
display the results, but also other information that attached to the record
or records(date, time, etc.). How would I go about doing this?

If these tables all have basically the same structure, and just refer to
different subsets of the "same kind of data", then I'd very strongly suggest
that you reconsider your table design. Rather than 25 identical tables, use
*ONE* table, with an additional field to categorize the subset of data. It
will be very easy to create 25 queries - or, better, one parameter query - to
extract just the desired subset.

With your current design you will need to use a UNION query to string all 25
of these together. See UNION in the online help, it's pretty good; if that
doesn't help, or if you get the dreaded Query Too Complex error, post back
with some more details about the nature of your tables.

John W. Vinson [MVP]

 

[Pat Hartman]

Sounds like you have serious normalization issues if you are trying to
search 25 tables at once. Let's start with "Why do you have 25 tables with
the same columns?"

 

[John W. Vinson]

Well I have a question then. I have 25 different checklist for documents.
So each checklist's questions are different. Now when they are all in 1
table, and a field is selected to pull up a product, I get any information
regarding that product, even though the information wont match the checklist.
This is why I thought of using seperate tables. What would you recommend on
doing here?

You have a very standard many to many relationship here, it seems: each
Document must have many Questions answered; each Checklist has many Questions.
A four table structure should work:

Checklists
ChecklistID <Primary Key>
ChecklistName <Text>
<other information about the checklist as an entity in its own right>

Documents
DocID <Primary Key>
<information about the document as an entity>

Questions
QuestionNo <Number, part of primary key>
ChecklistID <foreign key to Checklists and part of primary key>
Question <text>

Answers
QuestionNo <link to Questions>
ChecklistID <link to Questions>
DocID <link to Documents>
Answer <yes/no or other datatype as appropriate>

John W. Vinson [MVP]