Basing a report on mulptiple queries

[Guest]

I set up a report based on multiple Access queries but when I run the report
it is using the "And" criteria instead of the "Or". The report lists classes
that secretaries are required to take if they have gotten below a certain %
on a test we give them. For instance if they miss questions 30 and 25 they
are required to take a footnoting class. If they miss question 32 they are
required to take a paragraph formatting class, etc. etc. I tried to do one
query with multiple IIF statements but it just didn't work so I created
several queries and then based the report on all of these queries. But now
it is only showing the people who have to take both the footnoting class AND
the paragraph formatting class. The way I set it up was to link all of the
queries on the same field (index#, which is the student ID#, and then added
all of the queries to the "master" query, i.e. the query on which the report
will be based. I then added the individual field which has the word
"footnotes" or the words "paragraph formatting" in them. I hope this is
clear. Thanks in advance for your help.

 

[Guest]

I just thought I'd also post the SQL statement for these queries.

Thank you.

SELECT Scores.LAST, Scores.FIRST, qryFootnotes.Footnotes,
qryDocumentFormatting.DocumentForm
FROM (((((Scores INNER JOIN qryParagraphFormatting ON Scores.INDEX_NUM =
qryParagraphFormatting.INDEX_NUM) INNER JOIN qryPrintFormatting ON
Scores.INDEX_NUM = qryPrintFormatting.INDEX_NUM) INNER JOIN qryStyles ON
Scores.INDEX_NUM = qryStyles.INDEX_NUM) INNER JOIN qryWordFormatting ON
Scores.INDEX_NUM = qryWordFormatting.INDEX_NUM) INNER JOIN
qryDocumentFormatting ON Scores.INDEX_NUM = qryDocumentFormatting.INDEX_NUM)
INNER JOIN qryFootnotes ON Scores.INDEX_NUM = qryFootnotes.INDEX_NUM;

 

[Guest]

Is there someone out there who could help me with this?

Thank you very much.

 

[Duane Hookom]

I would question why you need multiple queries like
"..paragraphformatting...", "PrintFormatting.."...

If I understand correctly, you might need to use a union query rather than a
select query. I expect your various queries have similar fields. If this is
so, you want to use a union rather than joining in a select query.

 

[Guest]

Thank you so much for your help. The union query did exactly what I needed.
I'm afraid I have one more question though. How do I get the fields from the
union query into the report. I went to the properties of the report and
chose the query but all it displays are the first 3 fields. I have every
field in the query set to "show". Here is the query I wrote:
SELECT Index_Num, Last, First, Styles
FROM qryStyles
WHERE TOPIC="Formatting" AND
LEVEL1="ADV" AND
FINAL="CORRECT";

UNION
SELECT Index_Num, Last, First, Footnotes
FROM qryFootnotes
WHERE QNUM=30 AND
FINAL="INCORRECT";

UNION
Select Index_Num,Last,First, DocumentForm
FROM qryDocumentFormatting
WHERE TOPIC="Editing" OR "Formatting" AND
LEVEL1="Int" AND
FINAL="Incorrect"

UNION
Select Index_Num,Last,First, ParagraphForm
FROM qryParagraphFormatting
WHERE TOPIC="Editing" OR "Formatting" AND
LEVEL1="ADV" AND
FINAL="Incorrect"

UNION Select Index_Num,Last,First,"Print Formatting"
FROM qryPrintFormatting
WHERE QNUM=9 OR 25 OR 37 AND
FINAL="Incorrect";

Thank you again for any help you can provide.

 

[Lynn Trapp]

Joanne,
A UNION query uses only the field names from the first query. If you need to
identify which records come from which query you can add a literal text
field to each query.

SELECT Index_Num, Last, First, Styles, "Styles"
FROM qryStyles
WHERE TOPIC="Formatting" AND
LEVEL1="ADV" AND
FINAL="CORRECT";

UNION
SELECT Index_Num, Last, First, Footnotes, "Footnotes"
FROM qryFootnotes
WHERE QNUM=30 AND
FINAL="INCORRECT";

UNION
Select Index_Num,Last,First, DocumentForm, "DocumentForm"
FROM qryDocumentFormatting
WHERE TOPIC="Editing" OR "Formatting" AND
LEVEL1="Int" AND
FINAL="Incorrect"

UNION
Select Index_Num,Last,First, ParagraphForm, "ParagraphForm"
FROM qryParagraphFormatting
WHERE TOPIC="Editing" OR "Formatting" AND
LEVEL1="ADV" AND
FINAL="Incorrect"

UNION Select Index_Num,Last,First,"Print Formatting","Formatting"
FROM qryPrintFormatting
WHERE QNUM=9 OR 25 OR 37 AND
FINAL="Incorrect";


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

I figured it out! I didn't realize that the field names had to be the same,
I thought that I only had to have the same # of fields. Thank you!

 

[Guest]

Thank you for your help. But since I have to use the same fields in each
query, specifically last (which is last name) and first (first name), the
result in the report is coming out with the first and last name listed
multiple times. E.G.
Jane Smith
Footnotes
Jane Smith
paragraph Numbering

I just want it to say:
Jane Smith
Footnotes
Paragraph Numbering

What I thought would be a simple database is turning into a nightmare.
Please, if you could provide some help, I would appreciate it.