Display table and field names in query results

[Guest]

I have a comparison query using two tables with similar, but not exactly the
same field headings. For example, I will display the results of a person's
BirthDate data from Table_1 next to Date_of_Birth from Table_2 in order to
find any mis-matches. My question is, how can I force the query results to
show [Table_1].BirthDate and [Table_2].Date_of_Birth as headings in my query
datasheet view output?

I know I could change the field headings at the table level to be exactly
the same , and then the table name would show in the query, but I have too
many other queries that would fail if I changed the field names now.

Thank you

 

[Brendan Reynolds]

You can give a column an 'alias'. In SQL view it looks like this ...

SELECT SomeColumn AS SomeAlias FROM SomeTable

In query design view, in the 'Field' row, it looks like this ...

SomeAlias: SomeColumn

While I have not tested this, you may not be able to use a "." character in
the alias. That is an illegal character in a field name, so it may well be
illegal also in an alias. You may need to use some other character such as
an under-score to separate the table and field names in the alias. Your
query will look something like this ...

SELECT BirthDate AS Table_1_BirthDate, Date_of_Birth AS
Table_2_Date_of_Birth FROM etc ...

 

[David F Cox]

you can name fields in query design view by entering:

yourname: [nameintable]

e.g
Table1 mydate: [mydate]

 

[Graham Mandeno]

If you display the properties window with the query in design view
(Alt-Enter) and then click on one of your fields, you will see a "Caption"
property. Just type in there the text you want for your column heading.

 

[Graham Mandeno]

PS: I should have said that this is the only way to include dots and square
brackets in column headings.

Using "Select fieldname as aliasname" or aliasname: fieldname (which does
the same thing) will change the name of the *field* in your query, and this
would usually be the preferred method, but dots and square brackets are
illegal characters in field aliases. Using a caption changes only the text
in the column heading, not the field name.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

If you display the properties window with the query in design view
(Alt-Enter) and then click on one of your fields, you will see a "Caption"
property. Just type in there the text you want for your column heading.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a comparison query using two tables with similar, but not exactly
the
same field headings. For example, I will display the results of a
person's
BirthDate data from Table_1 next to Date_of_Birth from Table_2 in order
to
find any mis-matches. My question is, how can I force the query results
to
show [Table_1].BirthDate and [Table_2].Date_of_Birth as headings in my
query
datasheet view output?

I know I could change the field headings at the table level to be exactly
the same , and then the table name would show in the query, but I have
too
many other queries that would fail if I changed the field names now.

Thank you

 

[Guest]

Thanks! This is exactly what I needed to do. The alias would have worked as
well, but this is a little tidier.

If you display the properties window with the query in design view
(Alt-Enter) and then click on one of your fields, you will see a "Caption"
property. Just type in there the text you want for your column heading.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a comparison query using two tables with similar, but not exactly
the
same field headings. For example, I will display the results of a
person's
BirthDate data from Table_1 next to Date_of_Birth from Table_2 in order to
find any mis-matches. My question is, how can I force the query results
to
show [Table_1].BirthDate and [Table_2].Date_of_Birth as headings in my
query
datasheet view output?

I know I could change the field headings at the table level to be exactly
the same , and then the table name would show in the query, but I have too
many other queries that would fail if I changed the field names now.

Thank you

 

[j_beverly]

In your query design grid, enter whatever you want to appear as the
column heading followed by a colon and then the actual field name.
For example:
Table 1 Birth Date:[Table_1].[BirthDate]