Field Names in Append Query

H

HORNUNG

I have VB code that prompts the user to browse to another database and
TransferDatabase acImport a table (tblCustomers) into existing DB. The table
name is the same all the time, however a few of the fields are named
differently, such as: LName vs. LastName.
After the import table, I kickoff an append query to merge tblCustomers
into tblCustomersMaster, however I don't know how to write the script to
include LName or LastName.

Can you help?
 
K

KARL DEWEY

Have you tried to use the Graphical User Interface (GUI) available in query
design view?
 
H

HORNUNG

Yes, I created the script that way, I just don't know how to "build" the IF
statement to use the LastName field vs. LName.
 
K

KARL DEWEY

Post the SQL of the query you have now by opening the query in design view,
clicking on menu VIEW - SQL View, highlighting all, copy and paste in a post.
 
H

HORNUNG

The issue that I'm having is the acronym of a document that we used to call
"TRD", but now we call it "SOW". So, when a user accesses an old database,
that utilized "TRD" in a field name, (such as TRD_PARA_NO) I want to be able
to associate that to SOW_PARA_NO.

For ease of understanding my dilemma, I refer to a fictious tables in my
post, however, below is my actual query. When I run this query, it prompts
for input.

INSERT INTO tblBOE ( BOE_IDOrig, TASK_TITLE, PREPARED_BY, wbs_no,
group_number, SOW_PARA_NO, SOW_PARA_TITLE, [BOE HOURS], TASK_INFORMATION,
BASIS_OF_ESTIMATE )
SELECT tblBoe1.BOE_ID, tblBoe1.TASK_TITLE, tblBoe1.PREPARED_BY,
tblBoe1.wbs_no, tblBoe1.group_number, tblBoe1.TRD_PARA_NO Or
tblBOE1!SOW_PARA_NO AS SOW, tblSOW.SOW_PARA_TITLE, tblBoe1.[BOE HOURS],
tblBoe1.TASK_INFORMATION, tblBoe1.BASIS_OF_ESTIMATE
FROM tblBoe1 INNER JOIN tblSOW ON tblBoe1.SOW_PARA_NO = tblSOW.SOW_PARA_NO;
 
K

KARL DEWEY

Try this --
INSERT INTO tblSOW ( BOE_IDOrig, TASK_TITLE, PREPARED_BY, wbs_no,
group_number, SOW_PARA_NO, SOW_PARA_TITLE, [BOE HOURS], TASK_INFORMATION,
BASIS_OF_ESTIMATE )
SELECT tblBoe1.BOE_ID, tblBoe1.TASK_TITLE, tblBoe1.PREPARED_BY,
tblBoe1.wbs_no, tblBoe1.group_number, tblBoe1.TRD_PARA_NO,
tblBoe1.TRD_PARA_TITLE, tblBoe1.[BOE HOURS], tblBoe1.TASK_INFORMATION,
tblBoe1.BASIS_OF_ESTIMATE
FROM tblBoe1;
 
H

HORNUNG

I believe my query confused you. I'll make another example query:

INSERT INTO tblCustomerMaster (FirstName, LastName, Address, City, State,
Zip )
SELECT [tblCustomers].[FName] Or [tblCustomers].[FirstName] AS FirstName,
[tblCustomers].[LName] Or [tblCustomers].[LastName] AS LastName,
tblCustomers.BillingAddress, tblCustomers.City, tblCustomers.StateOrProvince,
tblCustomers.PostalCode
FROM tblCustomers;

I'm having trouble with my select statement to bring in the right fieldname.
When I run this query, I receive a prompt wanting data for FirstName and
LastName.
 
K

KARL DEWEY

You can not do what you are attempting to have this query do.
You can not SELECT like this ---
[tblCustomers].[FName] Or [tblCustomers].[FirstName] AS FirstName
[tblCustomers].[LName] Or [tblCustomers].[LastName] AS LastName
The above can not be done in a straight select query.

Align your INSERT INTO list of fields in the same order as your SELECT list
of fields.
 
H

HORNUNG

I'm going to try this again....

Can you write me a SQL query that would append
data from tblCustomer into tblCustomersMaster that has the following fields:
tblCustomers
FirstName
LastName
Address
City
State
Zip

but sometimes tblCustomers will label these fields differently such as:
tblCustomers
FName
LName
Address
City
State
Zip

tblCustomersMaster
FirstName
LastName
Address
City
State
Zip

Can the query be smart enough to know which fieldname to use?
 
J

John Spencer

IF your fields are always in the same order and have the same number and you
want to import ALL the fields in the source into ALL the fields in the
destination, you could try
INSERT INTO DestinationTable
SELECT * FROM SourceTable

Otherwise, if you want to do something like that you would need a VBA
routine to write the query or just have two (or more) queries to call
depending on the field names in the query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

George Nicholson

Can the query be smart enough to know which fieldname to use?
No

But you could test for the existence of a specific fieldname and then chose
a course of action.
The following example assumes there are only 2 possible sets of field names
and that you have 2 saved append queries (one for each set). If there are
more possibilities, then the logic gets more complex, but the principle is
the same:

Dim iResult as Long

On Error Resume Next
iResult = DCount("[FirstName],"tblCustomer")
If err.number <> 0 Then
' We raised an error so we know FirstName field does not exist in
tblCustomer
' (can we safely assume FName does exist? are there only 2
possibilities?)
'Turn error handler back on
'Run saved qryAppendFName or build a SQL statement to run
Else
'Turn error handler back on
' FirstName field exists in tblCustomer ??
' Run saved qryAppendFirstName or build a SQL statement to run
End If

HTH,
 
H

HORNUNG

Thanks, for your response..... I will adapt this logic into my existing code.


George Nicholson said:
Can the query be smart enough to know which fieldname to use?
Click to expand...
No

But you could test for the existence of a specific fieldname and then chose
a course of action.
The following example assumes there are only 2 possible sets of field names
and that you have 2 saved append queries (one for each set). If there are
more possibilities, then the logic gets more complex, but the principle is
the same:

Dim iResult as Long

On Error Resume Next
iResult = DCount("[FirstName],"tblCustomer")
If err.number <> 0 Then
' We raised an error so we know FirstName field does not exist in
tblCustomer
' (can we safely assume FName does exist? are there only 2
possibilities?)
'Turn error handler back on
'Run saved qryAppendFName or build a SQL statement to run
Else
'Turn error handler back on
' FirstName field exists in tblCustomer ??
' Run saved qryAppendFirstName or build a SQL statement to run
End If

HTH,


HORNUNG said:
I'm going to try this again....

Can you write me a SQL query that would append
data from tblCustomer into tblCustomersMaster that has the following
fields:
tblCustomers
FirstName
LastName
Address
City
State
Zip

but sometimes tblCustomers will label these fields differently such as:
tblCustomers
FName
LName
Address
City
State
Zip

tblCustomersMaster
FirstName
LastName
Address
City
State
Zip

Can the query be smart enough to know which fieldname to use?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query field names changing to expr#: 8
Add Table Name as Field in an Append Query 1
Criteria in append querie 3
Concatenate name fields 2
Preventing duplicate entries with an Append Query 3
AutoNumber Field Not Populating with Append Query 2
Append query - Append to table with Autonumber field 2
FormBased On A Query 2

Top