Adding result tables together

[david.goodyear]

Hi,

I have a query that gives me almost the results I want, which is

SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampai gn,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNu mber,
ai.street_addre ss as Cust_AddressStr eet,
ai.city as Cust_AddressCit y,
ai.state as Cust_AddressSta te,
ai.zip as Cust_AddressZIP ,
rec.file_name as AgreementRecord ingFile
FROM agent_input ai, leads l, recordings rec
WHERE ai.whole_phone_ number = l.whole_phone_n umber AND
l.call_status = 1110 AND
rec.whole_phone _number = l.whole_phone_n umber AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign AND
last_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#
ORDER BY ai.agent_login, ai.entry_date

but i want to make the rec.file_name as AgreementRecord ingFile
optional. Does any one know of a way I can modify the above to either
put '' in the results table or the rec.file_name if one is found? Im
sure this is some form of INTERSECT command, but i just keep getting
errors :(

FYI: This is to be run on a program which uses an access datafile as
its database, until we move to a mssql server in the near future. I
think the program is using odbc to access the access data file at the
moment.

Thanks for any help.

David

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What errors are you getting?

What do you mean by "optional"? The value of the file_name column is
either there or not there (NOT NULL or NULL), unless you've allowed zero
length strings in the column, then the possibilities are NULL, NOT NULL,
or empty string. If you want to exclude any rows that have NULL in the
column just use a criteria in the WHERE clause:

WHERE....
AND rec.file_name IS NOT NULL

You can't include/exclude the column from the SELECT clause based on the
existence/non-existence of a value in the column - you'd have to write 2
different queries, one w/ the column and one w/o the column.

If this query is the source of an INSERT INTO command and the
destination column of the rec.file_name is a required column (NOT NULL)
then use the WHERE clause option. If the destination column accepts
zero-length strings (a horrible option, IMO) then use the IIf() function
to substitute a zero-length string:

IIf(rec.file_na me IS NULL,'',rec.fil e_name) AS AgreementRecord ingFile

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRE6dDoechKq OuFEgEQIinACgog/ySzRLNzirNEo9SB zZpnMx0pMAnRoW
JVt82hQSgbhVBZH aEfOb+ioh
=Y7vE
-----END PGP SIGNATURE-----
da************@ gmail.com wrote:

Hi,

I have a query that gives me almost the results I want, which is

SELECT ai.entry_date as CallTime,
ai.agent_login as AgentsLogin,
ai.campaign as MarketingCampai gn,
ai.agent_input2 as ProductsSold,
ai.first_name as Cust_FirstName,
ai.last_name as Cust_LastName,
ai.agent_input1 as Cust_PersonalNu mber,
ai.street_addre ss as Cust_AddressStr eet,
ai.city as Cust_AddressCit y,
ai.state as Cust_AddressSta te,
ai.zip as Cust_AddressZIP ,
rec.file_name as AgreementRecord ingFile
FROM agent_input ai, leads l, recordings rec
WHERE ai.whole_phone_ number = l.whole_phone_n umber AND
l.call_status = 1110 AND
rec.whole_phone _number = l.whole_phone_n umber AND
rec.last_name = l.last_name AND
rec.agent = ai.agent_login AND
rec.campaign = l.campaign AND
last_call_date between #04/24/2006 12:00 AM# and #04/25/2006 11:59 PM#
ORDER BY ai.agent_login, ai.entry_date

but i want to make the rec.file_name as AgreementRecord ingFile
optional. Does any one know of a way I can modify the above to either
put '' in the results table or the rec.file_name if one is found? Im
sure this is some form of INTERSECT command, but i just keep getting
errors :(

FYI: This is to be run on a program which uses an access datafile as
its database, until we move to a mssql server in the near future. I
think the program is using odbc to access the access data file at the
moment.