Greetings:
I am trying to mimic a preexisting form in a report. The preexisting form has some Y/N check boxes. Data must be pulled from 3 primary tables to complete this section of the report. (Intuitively obvious in a hand completed form: not so in programming.) Witnesses to an incident may be either patients, staff, or nonstaff. They cannot be more than one at a time.
To accomplish this, I created a 3-way, full-outer-join, UNION query; the code for which is below, and the results attached. In this query, some fields are merged, while some retain the identity of their table of origin. This is the way it is intended.
The purpose, other than a user self-check, of having a confirm status (Y/N) field in the tables and the data entry forms was to support this report.
The trouble is this: when I put a Y/N field in a any union query, the Y/N box is lost and the field data is rendered as 0 or -1. The Y/N box is retained fine with any other type of query, and appears as a box, with or without a check mark, in reports. Only in the UNION query is the box lost. I need the box for the report.
The question is this: can I tweak the code to retain the Y/N boxes?; can I recreate the boxes in the report?; is this a limitation of UNION queries?; or is this indicative of file corruption?
(Note: I have misspellings in field names, even table names. But programming does not care about spelling--only consistency. Names are totally arbitrary. I will repair spellings when it all works, so as not to confuse later users. Alas, I am better at math and logic than spelling.)
SQL code for final 3-way, full-outer-Join, UNION query (Note:UNIONLRConsEmp is a saved UNION, full-outer-join, query between the Left and Right joins of the employee and patient data. The query below joins it to the nonstaff data to create a 3-way, full-outer-join, UNION query):
SELECT
UNIONLRConsEmpl.IncidentNum,
UNIONLRConsEmpl.Witness,
UNIONLRConsEmpl.Fullname,
UNIONLRConsEmpl.Phone,
UNIONLRConsEmpl.ConsumerID,
UNIONLRConsEmpl.EmployeeID,
WitnessUnionNonStaff.ID,
UNIONLRConsEmpl.ConfirmConsStatus, UNIONLRConsEmpl.ConfirmEmpStautus, WitnessUnionNonStaff.CofirmOtherStatus
FROM UNIONLRConsEmpl
LEFT JOIN WitnessUnionNonStaff
ON UNIONLRConsEmpl.ConsumerID = WitnessUnionNonStaff.IDTEXT
ORDER BY UNIONLRConsEmpl.IncidentNum;
UNION ALL
SELECT
WitnessUnionNonStaff.IncidentID,
WitnessUnionNonStaff.Witness,
WitnessUnionNonStaff.FullName,
WitnessUnionNonStaff.Phone,
UNIONLRConsEmpl.ConsumerID,
UNIONLRConsEmpl.EmployeeID,
WitnessUnionNonStaff.ID,
UNIONLRConsEmpl.ConfirmConsStatus, UNIONLRConsEmpl.ConfirmEmpStautus, WitnessUnionNonStaff.CofirmOtherStatus
FROM UNIONLRConsEmpl
RIGHT JOIN WitnessUnionNonStaff
ON UNIONLRConsEmpl.ConsumerID = WitnessUnionNonStaff.IDTEXT
ORDER BY UNIONLRConsEmpl.IncidentNum;
Creating this query is a story in itself. First, the joins have to be on the person ID, not on the incident ID. (The incident ID is crucial, even though it does not appear in the report, in order to form the correct master/child relationship for the sub-report.) The incident ID is simply merged from each tale; it goes along for the ride. But you need to pull the different person data from the various tables. In this instance, each of the L/R joins in the full-outer-join has no data at all for the other table, because a person cannot be a patient and an employee, or nonstaff at the same time. The field is there as a space holder. But when both queries are put together in a UNION query you have all the data from both tables. How cool is that!
The ID number of nonstaff involved in an incident is an autonumber, because the organization has no other data about nonstaff. Of course, that will not join with the text fields for staff and patient IDs, which may have leading zeros and are, themselves, drawn from other tables. So I had to create a calculated field changing the nonstaff autonumber ID to a text field in order to perform the last join. Finally, the IDTEXT field did not return correct in the final query so I used the original autonumber ID for the output, even though the join had to be on the calculated IDTEXT field. Very intricate; very cool!
I hope this post helps some people better understand full-outer-joins and UNION queries, and I hope someone knows how to preserve Y/N boxes in UNION queries.
David
I am trying to mimic a preexisting form in a report. The preexisting form has some Y/N check boxes. Data must be pulled from 3 primary tables to complete this section of the report. (Intuitively obvious in a hand completed form: not so in programming.) Witnesses to an incident may be either patients, staff, or nonstaff. They cannot be more than one at a time.
To accomplish this, I created a 3-way, full-outer-join, UNION query; the code for which is below, and the results attached. In this query, some fields are merged, while some retain the identity of their table of origin. This is the way it is intended.
The purpose, other than a user self-check, of having a confirm status (Y/N) field in the tables and the data entry forms was to support this report.
The trouble is this: when I put a Y/N field in a any union query, the Y/N box is lost and the field data is rendered as 0 or -1. The Y/N box is retained fine with any other type of query, and appears as a box, with or without a check mark, in reports. Only in the UNION query is the box lost. I need the box for the report.
The question is this: can I tweak the code to retain the Y/N boxes?; can I recreate the boxes in the report?; is this a limitation of UNION queries?; or is this indicative of file corruption?
(Note: I have misspellings in field names, even table names. But programming does not care about spelling--only consistency. Names are totally arbitrary. I will repair spellings when it all works, so as not to confuse later users. Alas, I am better at math and logic than spelling.)
SQL code for final 3-way, full-outer-Join, UNION query (Note:UNIONLRConsEmp is a saved UNION, full-outer-join, query between the Left and Right joins of the employee and patient data. The query below joins it to the nonstaff data to create a 3-way, full-outer-join, UNION query):
SELECT
UNIONLRConsEmpl.IncidentNum,
UNIONLRConsEmpl.Witness,
UNIONLRConsEmpl.Fullname,
UNIONLRConsEmpl.Phone,
UNIONLRConsEmpl.ConsumerID,
UNIONLRConsEmpl.EmployeeID,
WitnessUnionNonStaff.ID,
UNIONLRConsEmpl.ConfirmConsStatus, UNIONLRConsEmpl.ConfirmEmpStautus, WitnessUnionNonStaff.CofirmOtherStatus
FROM UNIONLRConsEmpl
LEFT JOIN WitnessUnionNonStaff
ON UNIONLRConsEmpl.ConsumerID = WitnessUnionNonStaff.IDTEXT
ORDER BY UNIONLRConsEmpl.IncidentNum;
UNION ALL
SELECT
WitnessUnionNonStaff.IncidentID,
WitnessUnionNonStaff.Witness,
WitnessUnionNonStaff.FullName,
WitnessUnionNonStaff.Phone,
UNIONLRConsEmpl.ConsumerID,
UNIONLRConsEmpl.EmployeeID,
WitnessUnionNonStaff.ID,
UNIONLRConsEmpl.ConfirmConsStatus, UNIONLRConsEmpl.ConfirmEmpStautus, WitnessUnionNonStaff.CofirmOtherStatus
FROM UNIONLRConsEmpl
RIGHT JOIN WitnessUnionNonStaff
ON UNIONLRConsEmpl.ConsumerID = WitnessUnionNonStaff.IDTEXT
ORDER BY UNIONLRConsEmpl.IncidentNum;
Creating this query is a story in itself. First, the joins have to be on the person ID, not on the incident ID. (The incident ID is crucial, even though it does not appear in the report, in order to form the correct master/child relationship for the sub-report.) The incident ID is simply merged from each tale; it goes along for the ride. But you need to pull the different person data from the various tables. In this instance, each of the L/R joins in the full-outer-join has no data at all for the other table, because a person cannot be a patient and an employee, or nonstaff at the same time. The field is there as a space holder. But when both queries are put together in a UNION query you have all the data from both tables. How cool is that!
The ID number of nonstaff involved in an incident is an autonumber, because the organization has no other data about nonstaff. Of course, that will not join with the text fields for staff and patient IDs, which may have leading zeros and are, themselves, drawn from other tables. So I had to create a calculated field changing the nonstaff autonumber ID to a text field in order to perform the last join. Finally, the IDTEXT field did not return correct in the final query so I used the original autonumber ID for the output, even though the join had to be on the calculated IDTEXT field. Very intricate; very cool!
I hope this post helps some people better understand full-outer-joins and UNION queries, and I hope someone knows how to preserve Y/N boxes in UNION queries.
David
