UNION JOIN - NEEDS HELP

[GaryWilsonCPA]

THIS UNION JOIN WORKS UNTIL I ADD


"appointment_scheduling.client_name as NAME" on the fifth line of the SELECT in section one.
I do not add this to the 5th line of the SELECT on the second section of the union because NAME is not in the table to choose.

Any suggestions?


SELECT
appointment_scheduling.PATID as PATID,
appointment_scheduling.appointment_date as DATE,
appointment_scheduling.service_code as SERVICECODE,
appointment_scheduling.client_name as NAME


FROM SYSTEM.appointment_scheduling

UNION ALL

SELECT
staff_tx_history_all.PATID as PATID,
staff_tx_history_all.date_of_service as DATE,
staff_tx_history_all.SERVICE_CODE as SERVICECODE


FROM
SYSTEM.staff_tx_history_all

 

[vidru]

Just add in a "fake" column:

SELECT
appointment_scheduling.PATID as PATID,
appointment_scheduling.appointment_date as DATE,
appointment_scheduling.service_code as SERVICECODE,
appointment_scheduling.client_name as NAME
FROM SYSTEM.appointment_scheduling

UNION ALL

SELECT
staff_tx_history_all.PATID as PATID,
staff_tx_history_all.date_of_service as DATE,
staff_tx_history_all.SERVICE_CODE as SERVICECODE
'No Name' as NAME
FROM
SYSTEM.staff_tx_history_all

-dave

 

[synapsevampire]

You can't add anything to the first select line (read the text blurb while in the Database->Show SQL Query).

Dave misunderstood your post.

If the second table has more fields, use it as the original data source, and then follow Dave's advice to create a derived field for use with the unioned data.

-k

 

[GaryWilsonCPA]

THANKS THAT WORKED.

ONE MORE QUESTION, HOW DO I PLACE THESE FIELDS ON A REPORT.

I HAVE BEEN SUCCESFUL WITH THE SQL DESIGNER - I CAN SEE THE DATA, HOWEVER I NEED LIVE DATA ON A HOURLY BASIS, AND THE SQL DESIGNER NEEDS REFRESHED BEFORE A REPORT CAN BE RUN.

IS THERE A WAY TO PUT THIS SQL IN A REPORT WHERE THE REPORT CAN BE REFRESHED BY USERS OTHER THAN ME.

 

[synapsevampire]

You post doesn't make sense.

I see that you're starting a new thread, so I guess I'll react to that instead...

-k