Lost data in Union Query

D

DZ

The total number of records in 3 tables used in a union query is about
40,000. The Union Query is omly displaying about 13, 000.

there are no filters. The data types match up.

CAn anyne tell me why there is missing data in the Union Query?

Here is the SQL

SELECT [Data Provider], [Hospital_Name], [Insured Name], [Policy Year],
[TOTAL IND RES], [TOTAL IND PAID], [TOTAL ALAE RES], [TOTAL ALAE PAID],
[REG RES], [REG PAID], [MLMIC xs ALAE RES], [MLMIC xs ALAE PAID], [MLMIC
xs IND RES], [MLMIC xs IND PAID], [Zurich IND RES], [Zurich IND PAID],
[Zurich ALAE RES], [Zurich ALAE PAID], [Policy No], [Claim No], [Policy
Period], [Policy Form], [Policy Type], [CLAIM Type], [Claim Status],
[Claimant Name], [Report Date], [Loss Date], [Close Date], [MLMIC ALAE
RES], [MLMIC ALAE PAID], [MLMIC IND RES], [MLMIC IND PAID], [Claim
Description]

FROM [Loss Data PRI-MLMIC-FOJP @ 09-30-07]



UNION SELECT [Data Provider], Null as RR2, [InsuredName], Null as RR4, Null
as RR5, Null as RR6, Null as RR7, Null as RR8, Null as RR9, Null as RR10,
Null as RR11, Null as RR12, Null as RR13, Null as RR14, Null as RR15, Null as
RR16, Null as RR17, Null as RR18, [PolicyNo], [ClaimNo], [PolicyYear],
[PolicyForm], [PolicyType], [ClaimType], [ClaimStatus], [ClaimantName],
[ReportDate], [LossDate], [CloseDate], [AlaeReserve], [AlaePayment],
[IndemnityReserve], [IndemnityPayment], [ClaimDescription]

FROM [PRI_9_30_07]

UNION SELECT [Data Provider], Null as RR2, Null as RR3, Null as RR4, Null as
RR5, Null as RR6, Null as RR7, Null as RR8, Null as RR9, Null as RR10, Null
as RR11, Null as RR12, Null as RR13, Null as RR14, Null as RR15, Null as
RR16, Null as RR17, Null as RR18, [POLICY_NO], [CLAIM_NO], [Policy Period],
[FORM], [POLICY_TYPE], [CLAIM_TYPE], [STATUS], [PATIENT_NAME],
[REPORTED_DATE], [ACCIDENT_DATE], [CLOSE_DATE], [EXPENSE_RESERVE],
[EXPENSE_PAID], [LOSS_RESERVE], [LOSS_PAID], [ALLEGATIONS]

FROM [MLMIC_9_30_07]

UNION SELECT [Data Provider], Null as RR2, [Insured Name], Null as RR4, Null
as RR5, Null as RR6, Null as RR7, Null as RR8, Null as RR9, Null as RR10,
Null as RR11, Null as RR12, Null as RR13, Null as RR14, Null as RR15, Null as
RR16, Null as RR17, Null as RR18, Null as RR19, [FOJP Case Number], [Policy
Period], [Policy Form], [Policy Type], Null as RR24, [Claim Status], Null as
RR26, Null as RR27, Null as RR28, Null as RR29, [MLMIC ALAE RES], [MLMIC ALAE
PAID], [MLMIC IND RES], [MLMIC IND PAID], Null as RR34

FROM [FOJP_9_30_07];


Thank you for any help
 
D

DZ

PS

The fields named RR1, RR2 etc were generated because there were no
cooresponding fields in the underlying tables so I added those Null as RR1 so
the query would open. The Query referred to in the SQL select statement has
no records but has correct field names for the client.

dz

thanks
 
J

John Spencer

UNION combines duplicate rows into one row

UNION ALL does not combine the duplicate rows. So try replacing UNION with
UNION ALL

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

DZ said:
The total number of records in 3 tables used in a union query is about
40,000. The Union Query is omly displaying about 13, 000.

there are no filters. The data types match up.

CAn anyne tell me why there is missing data in the Union Query?

Here is the SQL

SELECT [Data Provider], [Hospital_Name], [Insured Name], [Policy
Year],
[TOTAL IND RES], [TOTAL IND PAID], [TOTAL ALAE RES], [TOTAL ALAE PAID],
[REG RES], [REG PAID], [MLMIC xs ALAE RES], [MLMIC xs ALAE PAID],
[MLMIC
xs IND RES], [MLMIC xs IND PAID], [Zurich IND RES], [Zurich IND PAID],
[Zurich ALAE RES], [Zurich ALAE PAID], [Policy No], [Claim No],
[Policy
Period], [Policy Form], [Policy Type], [CLAIM Type], [Claim Status],
[Claimant Name], [Report Date], [Loss Date], [Close Date], [MLMIC ALAE
RES], [MLMIC ALAE PAID], [MLMIC IND RES], [MLMIC IND PAID], [Claim
Description]

FROM [Loss Data PRI-MLMIC-FOJP @ 09-30-07]



UNION SELECT [Data Provider], Null as RR2, [InsuredName], Null as RR4,
Null
as RR5, Null as RR6, Null as RR7, Null as RR8, Null as RR9, Null as RR10,
Null as RR11, Null as RR12, Null as RR13, Null as RR14, Null as RR15, Null
as
RR16, Null as RR17, Null as RR18, [PolicyNo], [ClaimNo], [PolicyYear],
[PolicyForm], [PolicyType], [ClaimType], [ClaimStatus], [ClaimantName],
[ReportDate], [LossDate], [CloseDate], [AlaeReserve], [AlaePayment],
[IndemnityReserve], [IndemnityPayment], [ClaimDescription]

FROM [PRI_9_30_07]

UNION SELECT [Data Provider], Null as RR2, Null as RR3, Null as RR4, Null
as
RR5, Null as RR6, Null as RR7, Null as RR8, Null as RR9, Null as RR10,
Null
as RR11, Null as RR12, Null as RR13, Null as RR14, Null as RR15, Null as
RR16, Null as RR17, Null as RR18, [POLICY_NO], [CLAIM_NO], [Policy
Period],
[FORM], [POLICY_TYPE], [CLAIM_TYPE], [STATUS], [PATIENT_NAME],
[REPORTED_DATE], [ACCIDENT_DATE], [CLOSE_DATE], [EXPENSE_RESERVE],
[EXPENSE_PAID], [LOSS_RESERVE], [LOSS_PAID], [ALLEGATIONS]

FROM [MLMIC_9_30_07]

UNION SELECT [Data Provider], Null as RR2, [Insured Name], Null as RR4,
Null
as RR5, Null as RR6, Null as RR7, Null as RR8, Null as RR9, Null as RR10,
Null as RR11, Null as RR12, Null as RR13, Null as RR14, Null as RR15, Null
as
RR16, Null as RR17, Null as RR18, Null as RR19, [FOJP Case Number],
[Policy
Period], [Policy Form], [Policy Type], Null as RR24, [Claim Status], Null
as
RR26, Null as RR27, Null as RR28, Null as RR29, [MLMIC ALAE RES], [MLMIC
ALAE
PAID], [MLMIC IND RES], [MLMIC IND PAID], Null as RR34

FROM [FOJP_9_30_07];


Thank you for any help
Click to expand...
 
Top