my SQL view query

  • Thread starter Thread starter Joshua Henry
  • Start date Start date
J

Joshua Henry

My query looks like the following:

Select Vendors.VName AS [Payor/Payee]
FROM Vendors;

UNION Select Tenants.OccFNAme & Tenants.OccLName
From Tenants;

I would however like a space between the tenants first
name & last. How would I go about doing this? thanks for
any help you have.

Josh
 
SELECT Vendors.VName AS [Payor/Payee]
FROM Vendors
UNION
SELECT Tenants.OccFNAme & " " & Tenants.OccLName
FROM Tenants

In answer to your other question (about how can you sort by Tenant last
name), you can't. The UNION query returns everything as a single recordset.
Since VName doesn't have the names split into first and last names, there's
no way you can sort it that way.

If you're willing to have all of the VNames sorted differently from the
Tenant names, you could use:

SELECT Vendors.VName AS [Payor/Payee], "" AS SortField
FROM Vendors
UNION
SELECT Tenants.OccFNAme & " " & Tenants.OccLName, Tenants.OccLName
FROM Tenants
ORDER BY SortField

(or ORDER BY 2)
 
-----Original Message-----
SELECT Vendors.VName AS [Payor/Payee]
FROM Vendors
UNION
SELECT Tenants.OccFNAme & " " & Tenants.OccLName
FROM Tenants

In answer to your other question (about how can you sort by Tenant last
name), you can't. The UNION query returns everything as a single recordset.
Since VName doesn't have the names split into first and last names, there's
no way you can sort it that way.

If you're willing to have all of the VNames sorted differently from the
Tenant names, you could use:

SELECT Vendors.VName AS [Payor/Payee], "" AS SortField
FROM Vendors
UNION
SELECT Tenants.OccFNAme & " " & Tenants.OccLName, Tenants.OccLName
FROM Tenants
ORDER BY SortField

(or ORDER BY 2)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



My query looks like the following:

Select Vendors.VName AS [Payor/Payee]
FROM Vendors;

UNION Select Tenants.OccFNAme & Tenants.OccLName
From Tenants;

I would however like a space between the tenants first
name & last. How would I go about doing this? thanks for
any help you have.

Josh
Click to expand...


.
Click to expand...
 
Back
Top