Union

[Renetta]

Good morning,

I am new to "UNION" and am attempting to combine severa same detail/info
tables into one for reporting purposes. I have a 2 part question.

1: my table detail is being linked via Excel to Access - the Excel field -
breaks down the borrowers first name and last name as "borrower name" for
both fields, but Access when linking and review reflect the last name as
"borrower name1" - should I recognize in the select statement as that (I
would say yes, but not quite sure)

Also, I keep getting a syntax errror based on what I've entered - can you
review and provide suggestions - again I am very new to this how process.
Thanks.

SELECT Fieldname, [Univ], [Borrower Name], [Borrower Name], [Fund], [Loan],
[SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip Code],
[Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full], [University
Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin Balance], [Collection
CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign Address], [Past Due
Interest], [Email Address], [Payment Source], [Home Phone]
FROM [759]
UNION SELECT Fieldname [Univ], [Borrower Name], [Borrower Name], [Fund],
[Loan], [SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip
Code], [Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full],
[University Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin Balance],
[Collection CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign Address],
[Past Due Interest], [Email Address], [Payment Source], [Home Phone]
FROM [763]
UNION SELECT [Univ], [Borrower Name], [Borrower Name], [Fund], [Loan],
[SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip Code],
[Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full], [University
Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin Balance], [Collection
CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign Address], [Past Due
Interest], [Email Address], [Payment Source], [Home Phone]
FROM [767];

 

[Duane Hookom]

Is "Fieldname" an actual field name? You can't have two fields with the same
name.

I would try change the first Select to:
SELECT Fieldname, [Univ], [Borrower Name], [Borrower Name] As LastName,
[Fund], [Loan],
[SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip Code],
[Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full],
[University
Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin Balance],
[Collection
CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign Address], [Past Due
Interest], [Email Address], [Payment Source], [Home Phone]
FROM [759]

Or just switch out the second [Borrower Name] with the name that Access has
provided.

 

[tina]

remove the word "Fieldname"; that was simply the word i used in replying to
your previous post of yesterday morning, to show you the correct syntax for
the SQL statement. also, add the number 1 to the end of one of the "borrower
name" fields, because that is the name of the field in the linked Access
table.

SELECT [Univ], [Borrower Name], [Borrower Name1], [Fund], [Loan], [SSN],
[Address Line 1], [Address Line 2], [City], [State], [Zip Code], [Interest
Rate], [Total Loan], [Lst Tran Date], [To Pay in Full], [University Name],
[Status], [Lst Pmy Source], [Pastdue Age], [Prin Balance], [Collection CD],
[# of Accts], [Agency CD], [Repay Plan], [Foreign Address], [Past Due
Interest], [Email Address], [Payment Source], [Home Phone]
FROM [759]
UNION SELECT [Univ], [Borrower Name], [Borrower Name1], [Fund], [Loan],
[SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip Code],
[Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full],
[University Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin
Balance], [Collection CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign
Address], [Past Due Interest], [Email Address], [Payment Source], [Home
Phone]
FROM [763]
UNION SELECT [Univ], [Borrower Name], [Borrower Name1], [Fund], [Loan],
[SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip Code],
[Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full],
[University Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin
Balance], [Collection CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign
Address], [Past Due Interest], [Email Address], [Payment Source], [Home
Phone]
FROM [767];

hth

Good morning,

I am new to "UNION" and am attempting to combine severa same detail/info
tables into one for reporting purposes. I have a 2 part question.

1: my table detail is being linked via Excel to Access - the Excel field -
breaks down the borrowers first name and last name as "borrower name" for
both fields, but Access when linking and review reflect the last name as
"borrower name1" - should I recognize in the select statement as that (I
would say yes, but not quite sure)

Also, I keep getting a syntax errror based on what I've entered - can you
review and provide suggestions - again I am very new to this how process.
Thanks.

SELECT Fieldname, [Univ], [Borrower Name], [Borrower Name], [Fund], [Loan],
[SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip Code],
[Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full], [University
Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin Balance], [Collection
CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign Address], [Past Due
Interest], [Email Address], [Payment Source], [Home Phone]
FROM [759]
UNION SELECT Fieldname [Univ], [Borrower Name], [Borrower Name], [Fund],
[Loan], [SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip
Code], [Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full],
[University Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin Balance],
[Collection CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign Address],
[Past Due Interest], [Email Address], [Payment Source], [Home Phone]
FROM [763]
UNION SELECT [Univ], [Borrower Name], [Borrower Name], [Fund], [Loan],
[SSN], [Address Line 1], [Address Line 2], [City], [State], [Zip Code],
[Interest Rate], [Total Loan], [Lst Tran Date], [To Pay in Full], [University
Name], [Status], [Lst Pmy Source], [Pastdue Age], [Prin Balance], [Collection
CD], [# of Accts], [Agency CD], [Repay Plan], [Foreign Address], [Past Due
Interest], [Email Address], [Payment Source], [Home Phone]
FROM [767];