union query help!!

V

vicky

I have two tables. One with data from 1995 up to June 2004 and another one
with data from beginning of 2004 up to July 2005. Both tables have exactly
the same field names with exact same number of fields. I need to have all the
data from 1995 up to 2005 in one query. I used the union query to do this,
but it doesnt get rid of all the duplicate records for the first half of 2004
and I cant seem to figure it out why. This is my SQL code. What am I doing
wrong?

SELECT [CACCID], [CALLNUM], [CALLDAYOFWEEK], [CALLHOUR], [CALLPANEL],
[CANCELREASONID], [COMPLAINTCODE], [DESTINATIONINSTITUTIONID],
[DESTINATIONLOWERTIER], [DESTINATIONUPPERTIER], [DESTINATIONUTMZONE],
[DISPATCHID], [DISPATCHEDPRIORITY], [FIRSTNOTIFIEDUNIT], [FIRSTARRIVEDUNIT],
[NUMPATIENTSCARRIED], [ORIGINALSERVICEID], [ORIGINALSTATIONID],
[ORIGINALCALLPRIORITY], [ORIGINALPRIORITYCODE], [PT_LNAME], [PT_FNAME],
[PICKUPINSTITUTIONID], [PICKUPLOWERTIER], [PICKUPUPPERTIER],
[PICKUPLOCATION], [PICKUPTOWN], [UTM], [UTMZONE], [RESPONDINGLOWERTIER],
[RESPONDINGUPPERTIER], [RETURNPRIORITY], [SERVICEID], [STATIONID],
[T0_TIMEZERO], [T1_CALLRECEIVED], [T2_NOTIFIED], [T3_ENROUTE],
[T4_ARRIVEDSCENE], [T5_DEPARTSCENE], [T6_ARRIVEDESTINATION],
[T7_CLEARDESTINATION], [T8_RETURNBASE], [T13_UNITCANCELLED],
[T14_PREEMPTEDTIME], [T15_PICKUP], [T16_APPOINTMENT], [ATTENDANT1],
[ATTENDANT2], [BASEHOSPITAL], [T0_T4_ELAPSED], [T1_T4_ELAPSED], [T3_TMAX],
[T2_T3_ELAPSED], [UNITID], [AR2PROCESSDATE], [DISTPROCESSDATE], [ACTIONTYPE]
FROM [CALL DATA]
UNION SELECT [CACCID], [CALLNUM], [CALLDAYOFWEEK], [CALLHOUR], [CALLPANEL],
[CANCELREASONID], [COMPLAINTCODE], [DESTINATIONINSTITUTIONID],
[DESTINATIONLOWERTIER], [DESTINATIONUPPERTIER], [DESTINATIONUTMZONE],
[DISPATCHID], [DISPATCHEDPRIORITY], [FIRSTNOTIFIEDUNIT], [FIRSTARRIVEDUNIT],
[NUMPATIENTSCARRIED], [ORIGINALSERVICEID], [ORIGINALSTATIONID],
[ORIGINALCALLPRIORITY], [ORIGINALPRIORITYCODE], [PT_LNAME], [PT_FNAME],
[PICKUPINSTITUTIONID], [PICKUPLOWERTIER], [PICKUPUPPERTIER],
[PICKUPLOCATION], [PICKUPTOWN], [UTM], [UTMZONE], [RESPONDINGLOWERTIER],
[RESPONDINGUPPERTIER], [RETURNPRIORITY], [SERVICEID], [STATIONID],
[T0_TIMEZERO], [T1_CALLRECEIVED], [T2_NOTIFIED], [T3_ENROUTE],
[T4_ARRIVEDSCENE], [T5_DEPARTSCENE], [T6_ARRIVEDESTINATION],
[T7_CLEARDESTINATION], [T8_RETURNBASE], [T13_UNITCANCELLED],
[T14_PREEMPTEDTIME], [T15_PICKUP], [T16_APPOINTMENT], [ATTENDANT1],
[ATTENDANT2], [BASEHOSPITAL], [T0_T4_ELAPSED], [T1_T4_ELAPSED], [T3_TMAX],
[T2_T3_ELAPSED], [UNITID], [AR2PROCESSDATE], [DISTPROCESSDATE], [ACTIONTYPE]
FROM [CALL DISPATCH]
ORDER BY [CALLNUM];

THx for your help
 
C

Chaim

Vicky,

Are you sure that the duplicates are 'duplicates'? Every field returned
matches exactly? The UNION will not dedupe just on the sort key. So if any
of the values in those dupes based on CALLNUM are different, the UNION won't
remove them.

BTW, based on the field names, it looks like you've got some calculated
fields in the tables. Usually, you're better off calculating those on the
fly unless the calculations are so complex that you're better off suffering
small doses of delay vs. a large hit when you try to get them all at one
time. I'm referring to the *_ELAPSED fields.

Good Luck!
----
Chaim
vicky said:
I have two tables. One with data from 1995 up to June 2004 and another one
with data from beginning of 2004 up to July 2005. Both tables have exactly
the same field names with exact same number of fields. I need to have all the
data from 1995 up to 2005 in one query. I used the union query to do this,
but it doesnt get rid of all the duplicate records for the first half of 2004
and I cant seem to figure it out why. This is my SQL code. What am I doing
wrong?

SELECT [CACCID], [CALLNUM], [CALLDAYOFWEEK], [CALLHOUR], [CALLPANEL],
[CANCELREASONID], [COMPLAINTCODE], [DESTINATIONINSTITUTIONID],
[DESTINATIONLOWERTIER], [DESTINATIONUPPERTIER], [DESTINATIONUTMZONE],
[DISPATCHID], [DISPATCHEDPRIORITY], [FIRSTNOTIFIEDUNIT], [FIRSTARRIVEDUNIT],
[NUMPATIENTSCARRIED], [ORIGINALSERVICEID], [ORIGINALSTATIONID],
[ORIGINALCALLPRIORITY], [ORIGINALPRIORITYCODE], [PT_LNAME], [PT_FNAME],
[PICKUPINSTITUTIONID], [PICKUPLOWERTIER], [PICKUPUPPERTIER],
[PICKUPLOCATION], [PICKUPTOWN], [UTM], [UTMZONE], [RESPONDINGLOWERTIER],
[RESPONDINGUPPERTIER], [RETURNPRIORITY], [SERVICEID], [STATIONID],
[T0_TIMEZERO], [T1_CALLRECEIVED], [T2_NOTIFIED], [T3_ENROUTE],
[T4_ARRIVEDSCENE], [T5_DEPARTSCENE], [T6_ARRIVEDESTINATION],
[T7_CLEARDESTINATION], [T8_RETURNBASE], [T13_UNITCANCELLED],
[T14_PREEMPTEDTIME], [T15_PICKUP], [T16_APPOINTMENT], [ATTENDANT1],
[ATTENDANT2], [BASEHOSPITAL], [T0_T4_ELAPSED], [T1_T4_ELAPSED], [T3_TMAX],
[T2_T3_ELAPSED], [UNITID], [AR2PROCESSDATE], [DISTPROCESSDATE], [ACTIONTYPE]
FROM [CALL DATA]
UNION SELECT [CACCID], [CALLNUM], [CALLDAYOFWEEK], [CALLHOUR], [CALLPANEL],
[CANCELREASONID], [COMPLAINTCODE], [DESTINATIONINSTITUTIONID],
[DESTINATIONLOWERTIER], [DESTINATIONUPPERTIER], [DESTINATIONUTMZONE],
[DISPATCHID], [DISPATCHEDPRIORITY], [FIRSTNOTIFIEDUNIT], [FIRSTARRIVEDUNIT],
[NUMPATIENTSCARRIED], [ORIGINALSERVICEID], [ORIGINALSTATIONID],
[ORIGINALCALLPRIORITY], [ORIGINALPRIORITYCODE], [PT_LNAME], [PT_FNAME],
[PICKUPINSTITUTIONID], [PICKUPLOWERTIER], [PICKUPUPPERTIER],
[PICKUPLOCATION], [PICKUPTOWN], [UTM], [UTMZONE], [RESPONDINGLOWERTIER],
[RESPONDINGUPPERTIER], [RETURNPRIORITY], [SERVICEID], [STATIONID],
[T0_TIMEZERO], [T1_CALLRECEIVED], [T2_NOTIFIED], [T3_ENROUTE],
[T4_ARRIVEDSCENE], [T5_DEPARTSCENE], [T6_ARRIVEDESTINATION],
[T7_CLEARDESTINATION], [T8_RETURNBASE], [T13_UNITCANCELLED],
[T14_PREEMPTEDTIME], [T15_PICKUP], [T16_APPOINTMENT], [ATTENDANT1],
[ATTENDANT2], [BASEHOSPITAL], [T0_T4_ELAPSED], [T1_T4_ELAPSED], [T3_TMAX],
[T2_T3_ELAPSED], [UNITID], [AR2PROCESSDATE], [DISTPROCESSDATE], [ACTIONTYPE]
FROM [CALL DISPATCH]
ORDER BY [CALLNUM];

THx for your help
Click to expand...
 
V

vicky

Thank you!
IT works now.... you were right. Some of the info didnt match exactly
THanks again :)

Chaim said:
Vicky,

Are you sure that the duplicates are 'duplicates'? Every field returned
matches exactly? The UNION will not dedupe just on the sort key. So if any
of the values in those dupes based on CALLNUM are different, the UNION won't
remove them.

BTW, based on the field names, it looks like you've got some calculated
fields in the tables. Usually, you're better off calculating those on the
fly unless the calculations are so complex that you're better off suffering
small doses of delay vs. a large hit when you try to get them all at one
time. I'm referring to the *_ELAPSED fields.

Good Luck!
----
Chaim
vicky said:
I have two tables. One with data from 1995 up to June 2004 and another one
with data from beginning of 2004 up to July 2005. Both tables have exactly
the same field names with exact same number of fields. I need to have all the
data from 1995 up to 2005 in one query. I used the union query to do this,
but it doesnt get rid of all the duplicate records for the first half of 2004
and I cant seem to figure it out why. This is my SQL code. What am I doing
wrong?

SELECT [CACCID], [CALLNUM], [CALLDAYOFWEEK], [CALLHOUR], [CALLPANEL],
[CANCELREASONID], [COMPLAINTCODE], [DESTINATIONINSTITUTIONID],
[DESTINATIONLOWERTIER], [DESTINATIONUPPERTIER], [DESTINATIONUTMZONE],
[DISPATCHID], [DISPATCHEDPRIORITY], [FIRSTNOTIFIEDUNIT], [FIRSTARRIVEDUNIT],
[NUMPATIENTSCARRIED], [ORIGINALSERVICEID], [ORIGINALSTATIONID],
[ORIGINALCALLPRIORITY], [ORIGINALPRIORITYCODE], [PT_LNAME], [PT_FNAME],
[PICKUPINSTITUTIONID], [PICKUPLOWERTIER], [PICKUPUPPERTIER],
[PICKUPLOCATION], [PICKUPTOWN], [UTM], [UTMZONE], [RESPONDINGLOWERTIER],
[RESPONDINGUPPERTIER], [RETURNPRIORITY], [SERVICEID], [STATIONID],
[T0_TIMEZERO], [T1_CALLRECEIVED], [T2_NOTIFIED], [T3_ENROUTE],
[T4_ARRIVEDSCENE], [T5_DEPARTSCENE], [T6_ARRIVEDESTINATION],
[T7_CLEARDESTINATION], [T8_RETURNBASE], [T13_UNITCANCELLED],
[T14_PREEMPTEDTIME], [T15_PICKUP], [T16_APPOINTMENT], [ATTENDANT1],
[ATTENDANT2], [BASEHOSPITAL], [T0_T4_ELAPSED], [T1_T4_ELAPSED], [T3_TMAX],
[T2_T3_ELAPSED], [UNITID], [AR2PROCESSDATE], [DISTPROCESSDATE], [ACTIONTYPE]
FROM [CALL DATA]
UNION SELECT [CACCID], [CALLNUM], [CALLDAYOFWEEK], [CALLHOUR], [CALLPANEL],
[CANCELREASONID], [COMPLAINTCODE], [DESTINATIONINSTITUTIONID],
[DESTINATIONLOWERTIER], [DESTINATIONUPPERTIER], [DESTINATIONUTMZONE],
[DISPATCHID], [DISPATCHEDPRIORITY], [FIRSTNOTIFIEDUNIT], [FIRSTARRIVEDUNIT],
[NUMPATIENTSCARRIED], [ORIGINALSERVICEID], [ORIGINALSTATIONID],
[ORIGINALCALLPRIORITY], [ORIGINALPRIORITYCODE], [PT_LNAME], [PT_FNAME],
[PICKUPINSTITUTIONID], [PICKUPLOWERTIER], [PICKUPUPPERTIER],
[PICKUPLOCATION], [PICKUPTOWN], [UTM], [UTMZONE], [RESPONDINGLOWERTIER],
[RESPONDINGUPPERTIER], [RETURNPRIORITY], [SERVICEID], [STATIONID],
[T0_TIMEZERO], [T1_CALLRECEIVED], [T2_NOTIFIED], [T3_ENROUTE],
[T4_ARRIVEDSCENE], [T5_DEPARTSCENE], [T6_ARRIVEDESTINATION],
[T7_CLEARDESTINATION], [T8_RETURNBASE], [T13_UNITCANCELLED],
[T14_PREEMPTEDTIME], [T15_PICKUP], [T16_APPOINTMENT], [ATTENDANT1],
[ATTENDANT2], [BASEHOSPITAL], [T0_T4_ELAPSED], [T1_T4_ELAPSED], [T3_TMAX],
[T2_T3_ELAPSED], [UNITID], [AR2PROCESSDATE], [DISTPROCESSDATE], [ACTIONTYPE]
FROM [CALL DISPATCH]
ORDER BY [CALLNUM];

THx for your help
Click to expand...
Click to expand...
 
M

[MVP] S.Clark

I would guess that whatever field make the records unique isn't keyed or
have a unique index, thus the UNION can't determine what is or isn't a dup.

Here's what I would do:
1. Make Table query: Write 1995Jan - 2004Jun data to a new table.
2. Append Query: Write 2004Jul - Present data to the same 'new' table.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting

vicky said:
I have two tables. One with data from 1995 up to June 2004 and another one
with data from beginning of 2004 up to July 2005. Both tables have exactly
the same field names with exact same number of fields. I need to have all
the
data from 1995 up to 2005 in one query. I used the union query to do this,
but it doesnt get rid of all the duplicate records for the first half of
2004
and I cant seem to figure it out why. This is my SQL code. What am I doing
wrong?

SELECT [CACCID], [CALLNUM], [CALLDAYOFWEEK], [CALLHOUR], [CALLPANEL],
[CANCELREASONID], [COMPLAINTCODE], [DESTINATIONINSTITUTIONID],
[DESTINATIONLOWERTIER], [DESTINATIONUPPERTIER], [DESTINATIONUTMZONE],
[DISPATCHID], [DISPATCHEDPRIORITY], [FIRSTNOTIFIEDUNIT],
[FIRSTARRIVEDUNIT],
[NUMPATIENTSCARRIED], [ORIGINALSERVICEID], [ORIGINALSTATIONID],
[ORIGINALCALLPRIORITY], [ORIGINALPRIORITYCODE], [PT_LNAME], [PT_FNAME],
[PICKUPINSTITUTIONID], [PICKUPLOWERTIER], [PICKUPUPPERTIER],
[PICKUPLOCATION], [PICKUPTOWN], [UTM], [UTMZONE], [RESPONDINGLOWERTIER],
[RESPONDINGUPPERTIER], [RETURNPRIORITY], [SERVICEID], [STATIONID],
[T0_TIMEZERO], [T1_CALLRECEIVED], [T2_NOTIFIED], [T3_ENROUTE],
[T4_ARRIVEDSCENE], [T5_DEPARTSCENE], [T6_ARRIVEDESTINATION],
[T7_CLEARDESTINATION], [T8_RETURNBASE], [T13_UNITCANCELLED],
[T14_PREEMPTEDTIME], [T15_PICKUP], [T16_APPOINTMENT], [ATTENDANT1],
[ATTENDANT2], [BASEHOSPITAL], [T0_T4_ELAPSED], [T1_T4_ELAPSED], [T3_TMAX],
[T2_T3_ELAPSED], [UNITID], [AR2PROCESSDATE], [DISTPROCESSDATE],
[ACTIONTYPE]
FROM [CALL DATA]
UNION SELECT [CACCID], [CALLNUM], [CALLDAYOFWEEK], [CALLHOUR],
[CALLPANEL],
[CANCELREASONID], [COMPLAINTCODE], [DESTINATIONINSTITUTIONID],
[DESTINATIONLOWERTIER], [DESTINATIONUPPERTIER], [DESTINATIONUTMZONE],
[DISPATCHID], [DISPATCHEDPRIORITY], [FIRSTNOTIFIEDUNIT],
[FIRSTARRIVEDUNIT],
[NUMPATIENTSCARRIED], [ORIGINALSERVICEID], [ORIGINALSTATIONID],
[ORIGINALCALLPRIORITY], [ORIGINALPRIORITYCODE], [PT_LNAME], [PT_FNAME],
[PICKUPINSTITUTIONID], [PICKUPLOWERTIER], [PICKUPUPPERTIER],
[PICKUPLOCATION], [PICKUPTOWN], [UTM], [UTMZONE], [RESPONDINGLOWERTIER],
[RESPONDINGUPPERTIER], [RETURNPRIORITY], [SERVICEID], [STATIONID],
[T0_TIMEZERO], [T1_CALLRECEIVED], [T2_NOTIFIED], [T3_ENROUTE],
[T4_ARRIVEDSCENE], [T5_DEPARTSCENE], [T6_ARRIVEDESTINATION],
[T7_CLEARDESTINATION], [T8_RETURNBASE], [T13_UNITCANCELLED],
[T14_PREEMPTEDTIME], [T15_PICKUP], [T16_APPOINTMENT], [ATTENDANT1],
[ATTENDANT2], [BASEHOSPITAL], [T0_T4_ELAPSED], [T1_T4_ELAPSED], [T3_TMAX],
[T2_T3_ELAPSED], [UNITID], [AR2PROCESSDATE], [DISTPROCESSDATE],
[ACTIONTYPE]
FROM [CALL DISPATCH]
ORDER BY [CALLNUM];

THx for your help
Click to expand...
 
Top