Allen,
Many thanks. That worked just as I wanted!
--
Cheers,
Lyn.
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:4249567a$0$22630$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> 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" <lhancock@ihug.com.au> wrote in message
> news:d2bka4$lic$1@lust.ihug.co.nz...[color=green]
>> 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.[/color]
>
>[/color]
