Controlling field names in a Make Table query

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

Gary Schuldt

I am creating a table tExp to export to a different application. It draws
its data from 3 different source tables in my database.

I am using a Make Table query to construct it, and it works fine, except . .
..

Each field in tExp has the same name as it does in whichever source table it
came from. That's not how the destination app wants them.

Question: In the Make Table query, is there any way to set the field names
the way I want them?

Gary
 
Use calculated Fields in the Queries you use for exporting. In the Column,
use something like:

ExportName: OriginalFieldName
 
I am creating a table tExp to export to a different application. It draws
its data from 3 different source tables in my database.

I am using a Make Table query to construct it, and it works fine, except . .
.
Click to expand...

Ummm... You do NOT need to use a Make Table query in order to do an
export. You can export directly from a Query; you will save a lot of
bloat and get improved performance by doing so, since you won't have
the overhead of creating the new table and registering it in the
Access systems tables.
Each field in tExp has the same name as it does in whichever source table it
came from. That's not how the destination app wants them.

Question: In the Make Table query, is there any way to set the field names
the way I want them?
Click to expand...

The same way you do in a simple Select query: by aliasing them. You
can put

Exportname: [tablename].[Fieldname]

in a query to alias Fieldname as "Exportname"; that's what you'll see
when you do the export (or the make-table).
 
John,

OK, I now understand that there is a way via Exportname to control the field
name on an exported object.

I just don't know how to code it! I searched Help and got no hits on
"exportname", so no help there.

I built the query in Design View, so in the bottom of the pane I have a set
of columns, each of which represents one of the fields in the query. In
terms of that view, where do I enter Exportname: DesiredFieldName?

Gary

John Vinson said:
I am creating a table tExp to export to a different application. It draws
its data from 3 different source tables in my database.

I am using a Make Table query to construct it, and it works fine, except .. .
.
Click to expand...

Ummm... You do NOT need to use a Make Table query in order to do an
export. You can export directly from a Query; you will save a lot of
bloat and get improved performance by doing so, since you won't have
the overhead of creating the new table and registering it in the
Access systems tables.
Each field in tExp has the same name as it does in whichever source table it
came from. That's not how the destination app wants them.

Question: In the Make Table query, is there any way to set the field names
the way I want them?
Click to expand...

The same way you do in a simple Select query: by aliasing them. You
can put

Exportname: [tablename].[Fieldname]

in a query to alias Fieldname as "Exportname"; that's what you'll see
when you do the export (or the make-table).
Click to expand...
 
Van,

I need a really simple example to illustrate how to code this! Suppose I'm
in Design View for a query, and I want to export one field F1 from table T1,
but I want it named Field1 instead of F1.

I bring up query design view, add table T1, double-click F1, which puts
T1.F1 in the QBE pane.

Now where do I specify the Exportname?

Gary
 
I built the query in Design View, so in the bottom of the pane I have a set
of columns, each of which represents one of the fields in the query. In
terms of that view, where do I enter Exportname: DesiredFieldName?
Click to expand...

You have it backwards.

If you have a field in your table named Surname, and you want the
export name to be LastName, simply edit the Field cell in the query
grid from

Surname

to

LastName: [tablename].[Surname]

The SQL equivalent is

SELECT [tablename].[Surname] AS [LastName]
FROM ....
 
In an empty Column, just type:

Field1: F1

You can also select F1 so that it appears in a Column and then type
"Field1:" in front of F1.

You should read up the chapters on Queries in any Access book you have as
you need to know these things before you can do more complex queries.
 
John,

thanks; that makes sense! I was looking at the word Exportname as some kind
of keyword followed by a colon followed by the new field name.

Gary

John Vinson said:
I built the query in Design View, so in the bottom of the pane I have a set
of columns, each of which represents one of the fields in the query. In
terms of that view, where do I enter Exportname: DesiredFieldName?
Click to expand...

You have it backwards.

If you have a field in your table named Surname, and you want the
export name to be LastName, simply edit the Field cell in the query
grid from

Surname

to

LastName: [tablename].[Surname]

The SQL equivalent is

SELECT [tablename].[Surname] AS [LastName]
FROM ....
Click to expand...
 
Van,

thanks, that's clear.

I've done some reading and made all kinds of queries . . . just never dealt
with this "renaming situation" before and don't remember reading about it.

Thanks for being there.

Gary
 
John,

OK, so next clarification: You wrote, "You can export directly from a
Query; you will save a lot of bloat and get improved performance by doing
so."

If I export a query qData (instead of a table tData) to another Access
database Target.mdb, does Target see qData as a table or as a query?

Gary

John Vinson said:
I am creating a table tExp to export to a different application. It draws
its data from 3 different source tables in my database.

I am using a Make Table query to construct it, and it works fine, except .. .
.
Click to expand...

Ummm... You do NOT need to use a Make Table query in order to do an
export. You can export directly from a Query; you will save a lot of
bloat and get improved performance by doing so, since you won't have
the overhead of creating the new table and registering it in the
Access systems tables.
Each field in tExp has the same name as it does in whichever source table it
came from. That's not how the destination app wants them.

Question: In the Make Table query, is there any way to set the field names
the way I want them?
Click to expand...

The same way you do in a simple Select query: by aliasing them. You
can put

Exportname: [tablename].[Fieldname]

in a query to alias Fieldname as "Exportname"; that's what you'll see
when you do the export (or the make-table).
Click to expand...
 
If I export a query qData (instead of a table tData) to another Access
database Target.mdb, does Target see qData as a table or as a query?
Click to expand...

As a Query. If you want to export *the data in* qData into another
..mdb file, then ideally you should have a table already in existance
in the other database, and run an Append query to append your data
into it. If you do not have a pre-existing table, then you can use a
MakeTable query with the IN clause to specify the name of the target
database.

I was assuming that you wanted to export to a file, or to a
spreadsheet - it strikes me as rather unusual to export to another
database! If it's on the same network, would it not be simpler to have
all the users' databases sharing one backend with *all* the table
data?
 
John,

yes, I want to export the *actual data* to a database that deals with a
subset of the subject matter in my database. The target is not on the same
network as mine. I just have a copy of the Target DB, which I export to,
and then email the updated result via the internet to its users.

The target database has queries and reports already defined which run off a
flat file. I am providing an update to the contents of that single file
with higher quality and more current information from my normalized
database.

So I was using a Make Table query to do the data extraction from my
normalized database into a single-table format that Target's queries and
reports could read without alteration.

But while I was testing the system, I found myself having to manually
*rename* the different fields each time I modified and reran the MT query !
That's what prompted my original question.

Thanks to your help, now I can incorporate the Target's field names right in
my MT query and save A LOT of work, since I expect to be providing periodic
updates to the Target in the future.

I imagine to you it seems like a lot of ado about something so minor I
should have already known (as Van said--READ A BOOK!), but we did make
progress.

FYI, my goal is to get my database on the web so that the Target's users can
then abandon their flat file system altogether. But I have never deployed
any Access DB on the web, so another frontier of learning looms (darkly)
ahead!

Gary
 
Back
Top