Union question

[martinmike2]

Hello,

I have a Union Query:

SELECT DISTINCTROW EDVR.A_RATE_ABR, EDVR.DNEC1, EDVR.DNEC2, EDVR.UIC,
qryAMDsubqry.*
FROM EDVR RIGHT JOIN qryAMDsubqry ON EDVR.SSN = qryAMDsubqry.SSN

UNION SELECT DISTINCTROW tblOCDR.RANK, tblOCDR.DESIG,
tblOCDR.PAYGRADE, tblOCDR.UIC, qryOAMDsubqry.*
FROM tblOCDR RIGHT JOIN qryOAMDsubqry ON tblOCDR.SSN =
qryOAMDsubqry.SSN;

qryAMDsubqry:

SELECT tblDIV.WC, tblDIV.TITLE, PERS.[NAME LAST], PERS.[NAME FIRST],
tblAMD.auic, tblAMD.bin, tblAMD.bsc, tblAMD.r_rtabbr, tblAMD.r_pnec,
tblAMD.r_snec, tblAMD.title, PERS.SSN, tblAMD.a_rtabbr, PERS.WCNTR
FROM tblDIV INNER JOIN (tblAMD LEFT JOIN PERS ON tblAMD.SSN =
PERS.SSN) ON tblDIV.WCCODE = tblAMD.WCCODE
WHERE (((tblAMD.auic) Like [Which UIC?]) AND ((tblAMD.WCCODE) Is Not
Null));

qryOAMDsubqry:

SELECT tblDIV.WC, tblDIV.TITLE, O_PERS.[NAME LAST], O_PERS.[NAME
FIRST], tblAMD.auic, tblAMD.bin, tblAMD.bsc, tblAMD.r_rtabbr,
tblAMD.r_pnec, tblAMD.r_snec, tblAMD.title, O_PERS.SSN,
tblAMD.a_rtabbr, O_PERS.WC
FROM tblDIV INNER JOIN (tblAMD INNER JOIN O_PERS ON tblAMD.SSN =
O_PERS.SSN) ON tblDIV.WCCODE = tblAMD.WCCODE
WHERE (((O_PERS.[NAME LAST]) Is Not Null) AND ((tblAMD.auic) Like
[Which UIC?]) AND ((tblAMD.WCCODE) Is Not Null));


The problem I am having is that for every record in tblAMD where the
SSN field is in O_PERS and not in PERS I am getting a duplicate record
shown on a report. While I confess I am not really SQL savvy I know
enough to get by, but this is killing me, lol. I can't have
duplicated records shown on the report as this is our master manning
document that shows where everybody is at and the job title they hold.

 

[Ken Sheridan]

A UNION operation suppresses duplicate rows, so if you are getting duplicates
in the report then it sounds like the query is returning non-duplicated
values in a column or columns not used by the report. These probably come
from the qryOAMDsubqry as you are using the asterisk to return all columns
from that. By opening the query independently of the report and pinning down
the duplicated rows you should be able to identify which column(s) are the
culprits and remove them from the query.

An alternative would be to base the report on a query which returns distinct
rows with only the required columns from the UNION query:

SELECT DISTINCT <list of required columns>
FROM <your union query's name>;

You'd have to put the names of the relevant columns from the qryOAMDsubqry
query, not an asterisk, if I'm right about that being the likely culprit.

You could suppress the duplicates in the report itself by using code in the
detail section's Format event procedure to see if every bound control's value
matches that of the previous row, whose values you'd assign to variables, and
if so set the return value of the Cancel argument to True. Or you could add
a group level, grouping by all columns, and put the controls currently in the
detail section in a group header or footer. It shouldn't be necessary to
resort to such lengths, however.

Ken Sheridan
Stafford, England

Hello,

I have a Union Query:

SELECT DISTINCTROW EDVR.A_RATE_ABR, EDVR.DNEC1, EDVR.DNEC2, EDVR.UIC,
qryAMDsubqry.*
FROM EDVR RIGHT JOIN qryAMDsubqry ON EDVR.SSN = qryAMDsubqry.SSN

UNION SELECT DISTINCTROW tblOCDR.RANK, tblOCDR.DESIG,
tblOCDR.PAYGRADE, tblOCDR.UIC, qryOAMDsubqry.*
FROM tblOCDR RIGHT JOIN qryOAMDsubqry ON tblOCDR.SSN =
qryOAMDsubqry.SSN;

qryAMDsubqry:

SELECT tblDIV.WC, tblDIV.TITLE, PERS.[NAME LAST], PERS.[NAME FIRST],
tblAMD.auic, tblAMD.bin, tblAMD.bsc, tblAMD.r_rtabbr, tblAMD.r_pnec,
tblAMD.r_snec, tblAMD.title, PERS.SSN, tblAMD.a_rtabbr, PERS.WCNTR
FROM tblDIV INNER JOIN (tblAMD LEFT JOIN PERS ON tblAMD.SSN =
PERS.SSN) ON tblDIV.WCCODE = tblAMD.WCCODE
WHERE (((tblAMD.auic) Like [Which UIC?]) AND ((tblAMD.WCCODE) Is Not
Null));

qryOAMDsubqry:

SELECT tblDIV.WC, tblDIV.TITLE, O_PERS.[NAME LAST], O_PERS.[NAME
FIRST], tblAMD.auic, tblAMD.bin, tblAMD.bsc, tblAMD.r_rtabbr,
tblAMD.r_pnec, tblAMD.r_snec, tblAMD.title, O_PERS.SSN,
tblAMD.a_rtabbr, O_PERS.WC
FROM tblDIV INNER JOIN (tblAMD INNER JOIN O_PERS ON tblAMD.SSN =
O_PERS.SSN) ON tblDIV.WCCODE = tblAMD.WCCODE
WHERE (((O_PERS.[NAME LAST]) Is Not Null) AND ((tblAMD.auic) Like
[Which UIC?]) AND ((tblAMD.WCCODE) Is Not Null));


The problem I am having is that for every record in tblAMD where the
SSN field is in O_PERS and not in PERS I am getting a duplicate record
shown on a report. While I confess I am not really SQL savvy I know
enough to get by, but this is killing me, lol. I can't have
duplicated records shown on the report as this is our master manning
document that shows where everybody is at and the job title they hold.

 

[martinmike2]

the problem is, i need to show the records that do not have a value in
the SSN field so we can easily see the holes we need to fill, if that
makes any sense. To accomplish that I made qryAMDsubqry LEFT JOIN
tblAMD (where the manning data is kept) and PERS (where the personell
data is kept) so that I can see all the "unfilled" records.

O_PERS and tblOCDR store Officer data. Since Officer's also hold
positions they need to show on the manning document as well. What is
happening is, only those positions held by officers are being
duplicated, like qryAMDsubqry is showing those records as having no
SSN in them.

qryOAMDsubqry is a copy of qryAMDsubqry with table names changed. All
columns in both the subqueries are required for both halves of the
union query.

Both subqueries are working because the report is showing both
enlisted and officer personell.

by the way, if I make qryOAMDsubqry the LEFT JOIN then I get every
record being duplicated. Ill try unioning the PERS and O_PERS table
then using that union query in another query to pull the data from
tblAMD. maybe i'm just backwards.

 

[martinmike2]

ok, that seemed to work. I combined PERS, EDVR, O_PERS, tblOCDR
together in a union qry, then used that union query as a subquery in a
select query to pull in tblAMD and tblDIV. The report shows no
duplicated records and also shows the empty positions as well.

Ken, thanks for nudging in the right direction.