Union Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a union query that is actually working fine except that it keeps
repeating a field value that I would like to have display only once. For
instance it says:

Jane Doe
Paragraph Formatting
Jane Doe
Styles
I want to get rid of the extra "Jane Doe" but I don't know how to do that in
a union query since I need to have the same # of fields in each select
statement. Thank you so much for any help you can provide.
 
Try adding the 'DistinctRow' reserved word after your "Select" statement in
your query. What this does is generate the result without multiple dupes
 
Dear Joanne:

I'm thinking the data is likely arranged:

Jane Doe Paragraph Formatting
Jane Doe Styles

Now, if you "get rid of the extra Jane Doe" which one would that be? You
must supply a specific basis to do this.

To see the one whose second column is the first one alphabetically:

SELECT Column1, MIN(Column2) AS MinCol2
FROM YourTable
GROUP BY Column1

Replace the column names and table name above with your actual column and
table names.

If you don't want to see only the first value from the second column, what
is it you do want? Or have I guessed entirely wrongly?

Tom Ellison.
 
The way you formatted the display of the data in your posting looks as if you
may be trying to do this in a report. If so, you can get rid of the duplicate
Jane Doe line using one of two methods.

First method, would be to use the reports grouping and sorting property (look in
the View menu) to set up a group on the Person Name field. Then you can put the
Name control in the group and the other items in the detail section.

Another method that might work, would be to click on the Name control and set it
to Hide Duplicates.
 
Thank you very much. I think this will get me started on the right track.
I'm new to union queries though and I thought I would include my select
statements, which are:
SELECT Last, First, ClassName
FROM qryStyles
WHERE TOPIC="Formatting" AND
LEVEL1="ADV" AND
FINAL="CORRECT"

UNION SELECT Last, First, ClassName
FROM qryFootnotes
WHERE QNUM=30 AND
FINAL="INCORRECT"

UNION SELECT Last, First, Classname
FROM qryDocumentFormatting
WHERE TOPIC="Editing" OR "Formatting" AND
LEVEL1="Int" AND
FINAL="Incorrect"

UNION SELECT Last, First, ClassName
FROM qryParagraphFormatting
WHERE TOPIC="Editing" OR "Formatting" AND
LEVEL1="ADV" AND
FINAL="Incorrect"

UNION SELECT Last, First, ClassName
FROM qryPrintFormatting
WHERE QNUM=9 OR 25 OR 37 AND
FINAL="Incorrect";

The idea is that in the report I want the person's name to appear first and
then a list of all the classes that she is required to attend. I'm not sure
how to incorporate your code into this query. In all of the queries except
the first one I only want the classname to appear. Thank you very much for
your help.
 
Thank you. Thank you. Thank you. Since I am very new to Access and I tend
to over think everything, you cut to the chase for me and solved my problem
so easily. You are a genius.
 
Back
Top