Combine Queries?

[Dale]

I've got the following query and it works fine.

SELECT tblMasterClients.ClientName, [tblMasterClients].[AlphaPrefix] &
"-" & [tblMasterClients].[SiteId] & "-" & [ClientType] & "-" &
Mid([DID],InStr(1,[DID],"-")+1) AS [Client Number],
tblMasterClients.InfinityNo, tblMasterClients.AuthCode,
tblBillingAddr.CreditCardPmt, tblBillingAddr.CreditCardNo,
tblBillingAddr.CreditCardExp, tblBillingAddr.CreditCardType,
tblBillingAddr.WhitePages

FROM (tblMasterClients INNER JOIN tblBillingAddr ON
tblMasterClients.ClientName = tblBillingAddr.[Client Name])
INNER JOIN tblClients ON
tblMasterClients.ClientName = tblClients.ClientName


WHERE tblBillingAddr.WhitePages = -1


ORDER BY tblMasterClients.ClientName;


However, the query below has elements in it that I also need:

SELECT tblLeaseInfo.LeaseId, tblLeaseInfo.ClientName,
tblLeaseInfo.LeaseDate, tblLeaseInfo.LeaseStatus,
tblLeaseInfo.LeaseTermLength, tblLeaseInfo.LeaseStartDate,
tblLeaseInfo.LeaseEndDate
FROM tblLeaseInfo
WHERE leasestatus = 'A'
ORDER BY tblLeaseInfo.LeaseEndDate DESC;


Is there a way to combine the queries? I've tried adding more joins the
first query but I keep getting syntax errors.

Suggestions?

Thanks

 

[Cheval]

Have you created logical relationships between the tables
involved? If no, then nope.

-----Original Message-----


I've got the following query and it works fine.

SELECT tblMasterClients.ClientName, [tblMasterClients]. [AlphaPrefix] &
"-" & [tblMasterClients].[SiteId] & "-" & [ClientType] & "-" &
Mid([DID],InStr(1,[DID],"-")+1) AS [Client Number],
tblMasterClients.InfinityNo, tblMasterClients.AuthCode,
tblBillingAddr.CreditCardPmt, tblBillingAddr.CreditCardNo,

tblBillingAddr.CreditCardExp,

tblBillingAddr.CreditCardType,
tblBillingAddr.WhitePages

FROM (tblMasterClients INNER JOIN tblBillingAddr ON
tblMasterClients.ClientName = tblBillingAddr.[Client Name])
INNER JOIN tblClients ON
tblMasterClients.ClientName = tblClients.ClientName


WHERE tblBillingAddr.WhitePages = -1


ORDER BY tblMasterClients.ClientName;


However, the query below has elements in it that I also need:

SELECT tblLeaseInfo.LeaseId, tblLeaseInfo.ClientName,
tblLeaseInfo.LeaseDate, tblLeaseInfo.LeaseStatus,
tblLeaseInfo.LeaseTermLength, tblLeaseInfo.LeaseStartDate,
tblLeaseInfo.LeaseEndDate
FROM tblLeaseInfo
WHERE leasestatus = 'A'
ORDER BY tblLeaseInfo.LeaseEndDate DESC;


Is there a way to combine the queries? I've tried adding more joins the
first query but I keep getting syntax errors.

Suggestions?

Thanks
.

 

[MacDermott]

In theory, there's no reason you can't include 4 tables in your query.

You might want to start over -
just add the 4 tables to the query grid and connect them, then add your
fields and criteria.

There are a number of things you can do to make the query perform better -
assuring that you're using appropriate indexes and relationships is one.
but this shouldn't cause a syntax problem.
I'd also mention that ClientName is rarely an appropriate Primary Key -
there are just too many John Smiths in the world for that to be a reliable
unique identifier.

HTH
- Turtle

 

[Dale]

There are a number of things you can do to make the query perform better -
assuring that you're using appropriate indexes and relationships is one.
but this shouldn't cause a syntax problem.

thanks!