Duplicate rows from union query

[Guest]

I have a simple union query in Access 2002 between two tables that outputs
repeating rows of identical data and I don't want the repeating rows.

Table1: Camera Assy
Columns: Serial number, Part number, GHVPS Serial Number

Table2: Optics Assy
Columns: GHVPS Serial number, TB/ALC Serial, LVPS CCA

I created a link between the two GHVPS Serial number columns. The repeating
rows are in various group sizes (i.e. sometimes there are four, sometimes 3
sometimes 1). Let me know if you need more info.

 

[Danny J. Lesandrini]

It's my understanding that if you use the UNION operator without
the ALL operator, you will get the equivalent of DISTINCT rows.

Can you paste the SQL you are using to get the output?

 

[Guest]

Here's the SQL. I just use the design aid where you drag and drop a columns
to link so the SQL is generated automatically. Let me know what you mean by
using the UNION and ALL operators. Do I have to tweak the SQL after using
the design aid? Thanks for your immediate reply.

SELECT tblCameraAssemblybuild.[Serial Number], tblCameraAssemblybuild.[GHVPS
Serial Number], tblCameraAssemblybuild.[LVPS CCA Serial Number],
tblCameraAssemblybuild.[Timebase CCA Serial Number]
FROM tblCameraAssemblybuild INNER JOIN tblOpticsAssybuild ON
tblCameraAssemblybuild.[GHVPS Serial Number] = tblOpticsAssybuild.[GHVPS
Serial Number]
ORDER BY tblCameraAssemblybuild.[Serial Number];

 

[Danny J. Lesandrini]

You used the word "union" and I though you had created a UNION query.
Perhaps this isn't the correct answer to your question, but here's the
syntax for a UNION query ...


SELECT [Serial Number], [GHVPS Serial Number],
[LVPS CCA Serial Number], [Timebase CCA Serial Number]
FROM tblCameraAssemblybuild
UNION
SELECT [Serial Number], [GHVPS Serial Number],
[LVPS CCA Serial Number], [Timebase CCA Serial Number]
FROM tblOpticsAssybuild
ORDER BY [Serial Number]

 

[John Vinson]

On Mon, 10 Oct 2005 14:01:06 -0700, "Parts Maker" <Parts

I have a simple union query in Access 2002 between two tables that outputs
repeating rows of identical data and I don't want the repeating rows.

Table1: Camera Assy
Columns: Serial number, Part number, GHVPS Serial Number

Table2: Optics Assy
Columns: GHVPS Serial number, TB/ALC Serial, LVPS CCA

I created a link between the two GHVPS Serial number columns. The repeating
rows are in various group sizes (i.e. sometimes there are four, sometimes 3
sometimes 1). Let me know if you need more info.

This isn't a UNION query: a UNION is a special query type which
strings together two identically-structured recordsets "end to end".
You're doing a simple side-by-side Join, it appears; and you're
getting exactly and specifically the results that such a query should
give - repeating the "one" side data for each matching record on the
"many".

If you want to see [Camera Assy] data only once on the screen (or on
paper) associated with multiple rows of [Optics Assy] data, I'd
suggest using a Form with a Subform - or, for a report, using the
Sorting and Grouping feature of the Report to display the Camera Assy
data on a group header, and Optics Assy data in the detail section.

John W. Vinson[MVP]

 

[Danny J. Lesandrini]

We spoke off-line and concluded that what was missing was DISTINCT.
That fixed it.