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. 2 2599
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.
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.
This discussion thread is closed Replies have been disabled for this discussion. Similar topics
6 posts
views
Thread by Alan |
last post: by
|
3 posts
views
Thread by Matt O'Donnell |
last post: by
|
1 post
views
Thread by JD Kronicz |
last post: by
|
3 posts
views
Thread by axlchris |
last post: by
|
reply
views
Thread by Ryan Ternier |
last post: by
|
7 posts
views
Thread by vivekian |
last post: by
|
reply
views
Thread by |
last post: by
|
1 post
views
Thread by bgreenspan |
last post: by
|
1 post
views
Thread by Ken Fine |
last post: by
| | | | | | | | | | |