Binding Results from UNION Query to Controls

Lyn

Hi,
How do you bind the output columns from a UNION query when the fields from
the two tables have different names? Consider this query (WHERE clauses
omitted)...

SELECT SurnameBirth, FNameBirth, DOB, 'Birth' from tblBirth UNION
SELECT SurnameAlias, FNameAlias, #1/1/100#, 'Alias' from tblAlias;

What I am doing here is searching two tables for a person where the name
given may be the person's original name (at birth) or subsequent changed
name (marriage, etc). There are four fields to be returned from each table
with corresponding data types. The tblAlias does not have DOB so I am
returning a dummy literal value that will be recognised by code. In
addition, the fourth field is another literal that identifies the source of
the data row ("Birth" or "Alias"). The search criteria have been omitted
for simplicity.

I am trying to display the columns returned in text boxes in a listing in
Continuous Mode. The form displaying this data is called by another form
which does the query to produce a public recordset which is then used to set
the Recordset property of the listing form via the Load event.

Because the field names are different, depending on the source of the row, I
have tried to bind the columns to the controls using the generic syntax
"rst.Fields(n)" in an expression. While this syntax is accepted, it results
in a display of "#Name" in each text box.

I have found by trial and error that if I set "tblBirth" as the
RecordSource, I can then bind the controls using the field names from this
table, and it works even with rows returned from tblAlias including the
dummy DOB value. The only problem now is how to display the literal values
"Birth" and "Alias" as neither corresponds to a column in tblAlias.

Sorry for the lengthy post, but my questions really are:
1) Can you bind data to controls using "rst.Fields(n)" syntax somehow?
2) If not, how can you bind a dummy column which doesn't have a field name?

One solution to 2) might be to use VBA as in (eg):

if rst.Fields(4) = "Birth" then
'set text box to "Birth"
else
'set text box to "Alias"
end if

This would have to be done for every row returned.

As a final question, is it possible in SQL to return the table name itself
as a "column"? If so, I wouldn't need to use the literal to identify the
source table.

TIA.

--
Cheers,
Lyn.

Nov 13 '05 #1

Allen Browne

Use the AS keyword to alias the field names so they are consistent between
the two tables, e.g.:

SELECT SurnameBirth, FNameBirth, DOB, 'Birth' AS Source
FROM tblBirth
UNION ALL
SELECT SurnameAlias, FNameAlias, Null AS DOB, 'Alias' AS Source
FROM tblAlias;
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Lyn" <lh******@ihug.com.au> wrote in message
news:d2**********@lust.ihug.co.nz...

Hi,
How do you bind the output columns from a UNION query when the fields from
the two tables have different names? Consider this query (WHERE clauses
omitted)...

SELECT SurnameBirth, FNameBirth, DOB, 'Birth' from tblBirth UNION
SELECT SurnameAlias, FNameAlias, #1/1/100#, 'Alias' from tblAlias;

What I am doing here is searching two tables for a person where the name
given may be the person's original name (at birth) or subsequent changed
name (marriage, etc). There are four fields to be returned from each
table with corresponding data types. The tblAlias does not have DOB so I
am returning a dummy literal value that will be recognised by code. In
addition, the fourth field is another literal that identifies the source
of the data row ("Birth" or "Alias"). The search criteria have been
omitted for simplicity.

I am trying to display the columns returned in text boxes in a listing in
Continuous Mode. The form displaying this data is called by another form
which does the query to produce a public recordset which is then used to
set the Recordset property of the listing form via the Load event.

Because the field names are different, depending on the source of the row,
I have tried to bind the columns to the controls using the generic syntax
"rst.Fields(n)" in an expression. While this syntax is accepted, it
results in a display of "#Name" in each text box.

I have found by trial and error that if I set "tblBirth" as the
RecordSource, I can then bind the controls using the field names from this
table, and it works even with rows returned from tblAlias including the
dummy DOB value. The only problem now is how to display the literal
values "Birth" and "Alias" as neither corresponds to a column in tblAlias.

Sorry for the lengthy post, but my questions really are:
1) Can you bind data to controls using "rst.Fields(n)" syntax somehow?
2) If not, how can you bind a dummy column which doesn't have a field
name?

One solution to 2) might be to use VBA as in (eg):

if rst.Fields(4) = "Birth" then
'set text box to "Birth"
else
'set text box to "Alias"
end if

This would have to be done for every row returned.

As a final question, is it possible in SQL to return the table name itself
as a "column"? If so, I wouldn't need to use the literal to identify the
source table.

TIA.

--
Cheers,
Lyn.

Nov 13 '05 #2

Lyn

Allen,
Many thanks. That worked just as I wanted!

--
Cheers,
Lyn.

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:42***********************@per-qv1-newsreader-01.iinet.net.au...

Use the AS keyword to alias the field names so they are consistent between
the two tables, e.g.:

SELECT SurnameBirth, FNameBirth, DOB, 'Birth' AS Source
FROM tblBirth
UNION ALL
SELECT SurnameAlias, FNameAlias, Null AS DOB, 'Alias' AS Source
FROM tblAlias;
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Lyn" <lh******@ihug.com.au> wrote in message
news:d2**********@lust.ihug.co.nz...

Hi,
How do you bind the output columns from a UNION query when the fields
from the two tables have different names? Consider this query (WHERE
clauses omitted)...

SELECT SurnameBirth, FNameBirth, DOB, 'Birth' from tblBirth UNION
SELECT SurnameAlias, FNameAlias, #1/1/100#, 'Alias' from tblAlias;

What I am doing here is searching two tables for a person where the name
given may be the person's original name (at birth) or subsequent changed
name (marriage, etc). There are four fields to be returned from each
table with corresponding data types. The tblAlias does not have DOB so I
am returning a dummy literal value that will be recognised by code. In
addition, the fourth field is another literal that identifies the source
of the data row ("Birth" or "Alias"). The search criteria have been
omitted for simplicity.

I am trying to display the columns returned in text boxes in a listing in
Continuous Mode. The form displaying this data is called by another form
which does the query to produce a public recordset which is then used to
set the Recordset property of the listing form via the Load event.

Because the field names are different, depending on the source of the
row, I have tried to bind the columns to the controls using the generic
syntax "rst.Fields(n)" in an expression. While this syntax is accepted,
it results in a display of "#Name" in each text box.

I have found by trial and error that if I set "tblBirth" as the
RecordSource, I can then bind the controls using the field names from
this table, and it works even with rows returned from tblAlias including
the dummy DOB value. The only problem now is how to display the literal
values "Birth" and "Alias" as neither corresponds to a column in
tblAlias.

Sorry for the lengthy post, but my questions really are:
1) Can you bind data to controls using "rst.Fields(n)" syntax somehow?
2) If not, how can you bind a dummy column which doesn't have a field
name?

One solution to 2) might be to use VBA as in (eg):

if rst.Fields(4) = "Birth" then
'set text box to "Birth"
else
'set text box to "Alias"
end if

This would have to be done for every row returned.

As a final question, is it possible in SQL to return the table name
itself as a "column"? If so, I wouldn't need to use the literal to
identify the source table.

TIA.

--
Cheers,
Lyn.

Nov 13 '05 #3