Query to Generate Flag if Customer has E-Mail on file

G

Guest

Hello,

I'm working on a query to assist my company's shipping department when
shipping packages by UPS. I've integrated the UPS WorldShip program on their
computer with my company's main database, which contains information on our
customers (this is a Pervasive 8.6 database). This way, my shipping
department can simply enter an order number for a given order, and the
database automatically detects the Customer ID from the order in the SO
Master table, then pulls up the appropriate address information, so this
information doesn't have to be entered manually when generating shipping
labels. But now, I have a new problem related to this. I now want our
shipping department to e-mail customers whenever an order ships by UPS
(provided we have an e-mail address for the customer). I got a query from my
supervisor, created in an older version of Access (we currently use Access
2000), that automatically looks at the "SO Master" and "Customer VAT" tables
in our database (linked through their respective CUSTID fields), and displays
any orders that are "status 3", or open (A copy of the SQL text for the query
will appear below). I want to take that query one step further and set it up
so that if it finds a "status 3" order that has an e-mail address listed in
the EMAIL1_81 field of the "Customer VAT" table. Can anyone offer any
suggestions on how to do this? Unfortunately, my Access skills are a bit
rusty, and I keep drawing blanks. I would greatly appreciate any help anyone
can offer.

Thanks in advance,

Michael

P.S. Here is the SQL text for the original query I mentioned earlier:

SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81
FROM [Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27
WHERE (([SO Master].STATUS_27="3"));
 
G

Guest

Since your original query returns all of the customers that fit status 3, you
need to now determine which of those individuals have an e-mail. You can do
this by appending an additional search around the working search that you
have. This will use the results of the original search to feed the "new"
search (e-mail)

So you could do something like this with your current search:

SELECT DISTINCT [qryHelp1].ORDNUM_27, [qryHelp1].STATUS_27,
[qryHelp1].CUSTPO_27, [qryHelp1].SHPVIA_27, [qryHelp1].NAME_27,
[qryHelp1].ADDR1_27, [qryHelp1].ADDR2_27, [qryHelp1].CITY_27,
[qryHelp1].STATE_27, [qryHelp1].ZIPCD_27, [qryHelp1].CNTRY_27,
[qryHelp1].PHONE_27, [qryHelp1].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] LEFT JOIN
(
SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27 WHERE (([SO Master].STATUS_27="3"))
)
AS qryHelp1
ON [Customer VAT].CUSTID_81 = [qryHelp1].CUSTID_81
WHERE (([Customer VAT].EMAIL1_81 is NOT Null));

There may be some field errors above, but if your original query provided a
list of customers that met status 3, then this should provide the list of
customers that meet status 3, and have an e-mail address.



mmbflyr1 said:
Hello,

I'm working on a query to assist my company's shipping department when
shipping packages by UPS. I've integrated the UPS WorldShip program on their
computer with my company's main database, which contains information on our
customers (this is a Pervasive 8.6 database). This way, my shipping
department can simply enter an order number for a given order, and the
database automatically detects the Customer ID from the order in the SO
Master table, then pulls up the appropriate address information, so this
information doesn't have to be entered manually when generating shipping
labels. But now, I have a new problem related to this. I now want our
shipping department to e-mail customers whenever an order ships by UPS
(provided we have an e-mail address for the customer). I got a query from my
supervisor, created in an older version of Access (we currently use Access
2000), that automatically looks at the "SO Master" and "Customer VAT" tables
in our database (linked through their respective CUSTID fields), and displays
any orders that are "status 3", or open (A copy of the SQL text for the query
will appear below). I want to take that query one step further and set it up
so that if it finds a "status 3" order that has an e-mail address listed in
the EMAIL1_81 field of the "Customer VAT" table. Can anyone offer any
suggestions on how to do this? Unfortunately, my Access skills are a bit
rusty, and I keep drawing blanks. I would greatly appreciate any help anyone
can offer.

Thanks in advance,

Michael

P.S. Here is the SQL text for the original query I mentioned earlier:

SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81
FROM [Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27
WHERE (([SO Master].STATUS_27="3"));
Click to expand...
 
G

Guest

Thanks for your help, GB. However, I have a new problem. I misinterpreted
what the shipping department wanted. They want to continue to see ALL status
3 orders, regardless of whether or not the customer has an e-mail address.
They just want the computer running the query to flag whether or not the
customer has an e-mail address. If the customer has an e-mail address, the
query based on the one I originally submitted should return a 'yes' if the
customer has an e-mail address, and a 'no' if the customer doesn't have an
e-mail address. If you have any suggestions, I'd greatly appreciate it.

Thanks again,

Michael

GB said:
Since your original query returns all of the customers that fit status 3, you
need to now determine which of those individuals have an e-mail. You can do
this by appending an additional search around the working search that you
have. This will use the results of the original search to feed the "new"
search (e-mail)

So you could do something like this with your current search:

SELECT DISTINCT [qryHelp1].ORDNUM_27, [qryHelp1].STATUS_27,
[qryHelp1].CUSTPO_27, [qryHelp1].SHPVIA_27, [qryHelp1].NAME_27,
[qryHelp1].ADDR1_27, [qryHelp1].ADDR2_27, [qryHelp1].CITY_27,
[qryHelp1].STATE_27, [qryHelp1].ZIPCD_27, [qryHelp1].CNTRY_27,
[qryHelp1].PHONE_27, [qryHelp1].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] LEFT JOIN
(
SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27 WHERE (([SO Master].STATUS_27="3"))
)
AS qryHelp1
ON [Customer VAT].CUSTID_81 = [qryHelp1].CUSTID_81
WHERE (([Customer VAT].EMAIL1_81 is NOT Null));

There may be some field errors above, but if your original query provided a
list of customers that met status 3, then this should provide the list of
customers that meet status 3, and have an e-mail address.



mmbflyr1 said:
Hello,

I'm working on a query to assist my company's shipping department when
shipping packages by UPS. I've integrated the UPS WorldShip program on their
computer with my company's main database, which contains information on our
customers (this is a Pervasive 8.6 database). This way, my shipping
department can simply enter an order number for a given order, and the
database automatically detects the Customer ID from the order in the SO
Master table, then pulls up the appropriate address information, so this
information doesn't have to be entered manually when generating shipping
labels. But now, I have a new problem related to this. I now want our
shipping department to e-mail customers whenever an order ships by UPS
(provided we have an e-mail address for the customer). I got a query from my
supervisor, created in an older version of Access (we currently use Access
2000), that automatically looks at the "SO Master" and "Customer VAT" tables
in our database (linked through their respective CUSTID fields), and displays
any orders that are "status 3", or open (A copy of the SQL text for the query
will appear below). I want to take that query one step further and set it up
so that if it finds a "status 3" order that has an e-mail address listed in
the EMAIL1_81 field of the "Customer VAT" table. Can anyone offer any
suggestions on how to do this? Unfortunately, my Access skills are a bit
rusty, and I keep drawing blanks. I would greatly appreciate any help anyone
can offer.

Thanks in advance,

Michael

P.S. Here is the SQL text for the original query I mentioned earlier:

SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81
FROM [Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27
WHERE (([SO Master].STATUS_27="3"));
Click to expand...
Click to expand...
 
G

Guest

Then if I understand correctly, they want the query to provide a list of all
the information for a customer that meets the status 3 criteria, plus an
additional field that indicates "Yes" for has e-mail, and "No" for does not
have e-mail.

I believe, this is a point where you could use what is called an IIF()
function. If I'm not mistaken, this function tests for truth then performs
either a true action or a false action. In your case, you could test for the
existence of an e-mail, if it is there, then "Yes" is the response, and if it
is not, then "No" is the response.

So, if you use your original Query and add one additional attribute to the
SELECT portion.

SELECT DISTINCTROW IIF([Customer VAT].EMAIL1_81 is NOT Null, "Yes", "No") as
HasEmail,[SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO Master].CUSTPO_27,
[SO Master].SHPVIA_27, [SO Master].NAME_27, [SO Master].ADDR1_27, [SO
Master].ADDR2_27, [SO Master].CITY_27, [SO Master].STATE_27, [SO
Master].ZIPCD_27, [SO Master].CNTRY_27, [SO Master].PHONE_27, [SO
Master].CNTCT_27, [Customer VAT].EMAIL1_81 FROM [Customer VAT] INNER JOIN [SO
Master] ON [Customer VAT].CUSTID_81 = [SO Master].CUSTID_27 WHERE (([SO
Master].STATUS_27="3"))

If you note the first item after the SELECT statement, is the IIF()
Function, I believe this will give you what you are asked for. Then when
this query is used later, if the answer is yes, then send the e-mail, if not
then continue through whatever is needed for normal processing.

That help? (I've only seen that function used, I've never used it myself,
but it sounds like what you want.)

GB

mmbflyr1 said:
Thanks for your help, GB. However, I have a new problem. I misinterpreted
what the shipping department wanted. They want to continue to see ALL status
3 orders, regardless of whether or not the customer has an e-mail address.
They just want the computer running the query to flag whether or not the
customer has an e-mail address. If the customer has an e-mail address, the
query based on the one I originally submitted should return a 'yes' if the
customer has an e-mail address, and a 'no' if the customer doesn't have an
e-mail address. If you have any suggestions, I'd greatly appreciate it.

Thanks again,

Michael

GB said:
Since your original query returns all of the customers that fit status 3, you
need to now determine which of those individuals have an e-mail. You can do
this by appending an additional search around the working search that you
have. This will use the results of the original search to feed the "new"
search (e-mail)

So you could do something like this with your current search:

SELECT DISTINCT [qryHelp1].ORDNUM_27, [qryHelp1].STATUS_27,
[qryHelp1].CUSTPO_27, [qryHelp1].SHPVIA_27, [qryHelp1].NAME_27,
[qryHelp1].ADDR1_27, [qryHelp1].ADDR2_27, [qryHelp1].CITY_27,
[qryHelp1].STATE_27, [qryHelp1].ZIPCD_27, [qryHelp1].CNTRY_27,
[qryHelp1].PHONE_27, [qryHelp1].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] LEFT JOIN
(
SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27 WHERE (([SO Master].STATUS_27="3"))
)
AS qryHelp1
ON [Customer VAT].CUSTID_81 = [qryHelp1].CUSTID_81
WHERE (([Customer VAT].EMAIL1_81 is NOT Null));

There may be some field errors above, but if your original query provided a
list of customers that met status 3, then this should provide the list of
customers that meet status 3, and have an e-mail address.



mmbflyr1 said:
Hello,

I'm working on a query to assist my company's shipping department when
shipping packages by UPS. I've integrated the UPS WorldShip program on their
computer with my company's main database, which contains information on our
customers (this is a Pervasive 8.6 database). This way, my shipping
department can simply enter an order number for a given order, and the
database automatically detects the Customer ID from the order in the SO
Master table, then pulls up the appropriate address information, so this
information doesn't have to be entered manually when generating shipping
labels. But now, I have a new problem related to this. I now want our
shipping department to e-mail customers whenever an order ships by UPS
(provided we have an e-mail address for the customer). I got a query from my
supervisor, created in an older version of Access (we currently use Access
2000), that automatically looks at the "SO Master" and "Customer VAT" tables
in our database (linked through their respective CUSTID fields), and displays
any orders that are "status 3", or open (A copy of the SQL text for the query
will appear below). I want to take that query one step further and set it up
so that if it finds a "status 3" order that has an e-mail address listed in
the EMAIL1_81 field of the "Customer VAT" table. Can anyone offer any
suggestions on how to do this? Unfortunately, my Access skills are a bit
rusty, and I keep drawing blanks. I would greatly appreciate any help anyone
can offer.

Thanks in advance,

Michael

P.S. Here is the SQL text for the original query I mentioned earlier:

SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81
FROM [Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27
WHERE (([SO Master].STATUS_27="3"));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks again,

I've tried the query you suggested. However, the result of the "HasEmail"
column for all the records the query returns is "Yes", whether there is an
e-mail address for that record or not. I double-checked my typing, but
cannot find any errors there. Is it possible to use an IF...THEN...ELSE
statement? I looked at the original query for comparison purposes, and can't
tell if there's any hidden data in the tables that might cause the query to
think that the records with no visible e-mail address in fact have an e-mail
address. Any input you could provide would be greatly appreciated. Thanks
again for your help.

Michael

GB said:
Then if I understand correctly, they want the query to provide a list of all
the information for a customer that meets the status 3 criteria, plus an
additional field that indicates "Yes" for has e-mail, and "No" for does not
have e-mail.

I believe, this is a point where you could use what is called an IIF()
function. If I'm not mistaken, this function tests for truth then performs
either a true action or a false action. In your case, you could test for the
existence of an e-mail, if it is there, then "Yes" is the response, and if it
is not, then "No" is the response.

So, if you use your original Query and add one additional attribute to the
SELECT portion.

SELECT DISTINCTROW IIF([Customer VAT].EMAIL1_81 is NOT Null, "Yes", "No") as
HasEmail,[SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO Master].CUSTPO_27,
[SO Master].SHPVIA_27, [SO Master].NAME_27, [SO Master].ADDR1_27, [SO
Master].ADDR2_27, [SO Master].CITY_27, [SO Master].STATE_27, [SO
Master].ZIPCD_27, [SO Master].CNTRY_27, [SO Master].PHONE_27, [SO
Master].CNTCT_27, [Customer VAT].EMAIL1_81 FROM [Customer VAT] INNER JOIN [SO
Master] ON [Customer VAT].CUSTID_81 = [SO Master].CUSTID_27 WHERE (([SO
Master].STATUS_27="3"))

If you note the first item after the SELECT statement, is the IIF()
Function, I believe this will give you what you are asked for. Then when
this query is used later, if the answer is yes, then send the e-mail, if not
then continue through whatever is needed for normal processing.

That help? (I've only seen that function used, I've never used it myself,
but it sounds like what you want.)

GB

mmbflyr1 said:
Thanks for your help, GB. However, I have a new problem. I misinterpreted
what the shipping department wanted. They want to continue to see ALL status
3 orders, regardless of whether or not the customer has an e-mail address.
They just want the computer running the query to flag whether or not the
customer has an e-mail address. If the customer has an e-mail address, the
query based on the one I originally submitted should return a 'yes' if the
customer has an e-mail address, and a 'no' if the customer doesn't have an
e-mail address. If you have any suggestions, I'd greatly appreciate it.

Thanks again,

Michael

GB said:
Since your original query returns all of the customers that fit status 3, you
need to now determine which of those individuals have an e-mail. You can do
this by appending an additional search around the working search that you
have. This will use the results of the original search to feed the "new"
search (e-mail)

So you could do something like this with your current search:

SELECT DISTINCT [qryHelp1].ORDNUM_27, [qryHelp1].STATUS_27,
[qryHelp1].CUSTPO_27, [qryHelp1].SHPVIA_27, [qryHelp1].NAME_27,
[qryHelp1].ADDR1_27, [qryHelp1].ADDR2_27, [qryHelp1].CITY_27,
[qryHelp1].STATE_27, [qryHelp1].ZIPCD_27, [qryHelp1].CNTRY_27,
[qryHelp1].PHONE_27, [qryHelp1].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] LEFT JOIN
(
SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27 WHERE (([SO Master].STATUS_27="3"))
)
AS qryHelp1
ON [Customer VAT].CUSTID_81 = [qryHelp1].CUSTID_81
WHERE (([Customer VAT].EMAIL1_81 is NOT Null));

There may be some field errors above, but if your original query provided a
list of customers that met status 3, then this should provide the list of
customers that meet status 3, and have an e-mail address.



:

Hello,

I'm working on a query to assist my company's shipping department when
shipping packages by UPS. I've integrated the UPS WorldShip program on their
computer with my company's main database, which contains information on our
customers (this is a Pervasive 8.6 database). This way, my shipping
department can simply enter an order number for a given order, and the
database automatically detects the Customer ID from the order in the SO
Master table, then pulls up the appropriate address information, so this
information doesn't have to be entered manually when generating shipping
labels. But now, I have a new problem related to this. I now want our
shipping department to e-mail customers whenever an order ships by UPS
(provided we have an e-mail address for the customer). I got a query from my
supervisor, created in an older version of Access (we currently use Access
2000), that automatically looks at the "SO Master" and "Customer VAT" tables
in our database (linked through their respective CUSTID fields), and displays
any orders that are "status 3", or open (A copy of the SQL text for the query
will appear below). I want to take that query one step further and set it up
so that if it finds a "status 3" order that has an e-mail address listed in
the EMAIL1_81 field of the "Customer VAT" table. Can anyone offer any
suggestions on how to do this? Unfortunately, my Access skills are a bit
rusty, and I keep drawing blanks. I would greatly appreciate any help anyone
can offer.

Thanks in advance,

Michael

P.S. Here is the SQL text for the original query I mentioned earlier:

SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81
FROM [Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27
WHERE (([SO Master].STATUS_27="3"));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hmmm... I would also need to re-review the queries, if you are still
interested in this... Sorry if it is too late.


mmbflyr1 said:
Thanks again,

I've tried the query you suggested. However, the result of the "HasEmail"
column for all the records the query returns is "Yes", whether there is an
e-mail address for that record or not. I double-checked my typing, but
cannot find any errors there. Is it possible to use an IF...THEN...ELSE
statement? I looked at the original query for comparison purposes, and can't
tell if there's any hidden data in the tables that might cause the query to
think that the records with no visible e-mail address in fact have an e-mail
address. Any input you could provide would be greatly appreciated. Thanks
again for your help.

Michael

GB said:
Then if I understand correctly, they want the query to provide a list of all
the information for a customer that meets the status 3 criteria, plus an
additional field that indicates "Yes" for has e-mail, and "No" for does not
have e-mail.

I believe, this is a point where you could use what is called an IIF()
function. If I'm not mistaken, this function tests for truth then performs
either a true action or a false action. In your case, you could test for the
existence of an e-mail, if it is there, then "Yes" is the response, and if it
is not, then "No" is the response.

So, if you use your original Query and add one additional attribute to the
SELECT portion.

SELECT DISTINCTROW IIF([Customer VAT].EMAIL1_81 is NOT Null, "Yes", "No") as
HasEmail,[SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO Master].CUSTPO_27,
[SO Master].SHPVIA_27, [SO Master].NAME_27, [SO Master].ADDR1_27, [SO
Master].ADDR2_27, [SO Master].CITY_27, [SO Master].STATE_27, [SO
Master].ZIPCD_27, [SO Master].CNTRY_27, [SO Master].PHONE_27, [SO
Master].CNTCT_27, [Customer VAT].EMAIL1_81 FROM [Customer VAT] INNER JOIN [SO
Master] ON [Customer VAT].CUSTID_81 = [SO Master].CUSTID_27 WHERE (([SO
Master].STATUS_27="3"))

If you note the first item after the SELECT statement, is the IIF()
Function, I believe this will give you what you are asked for. Then when
this query is used later, if the answer is yes, then send the e-mail, if not
then continue through whatever is needed for normal processing.

That help? (I've only seen that function used, I've never used it myself,
but it sounds like what you want.)

GB

mmbflyr1 said:
Thanks for your help, GB. However, I have a new problem. I misinterpreted
what the shipping department wanted. They want to continue to see ALL status
3 orders, regardless of whether or not the customer has an e-mail address.
They just want the computer running the query to flag whether or not the
customer has an e-mail address. If the customer has an e-mail address, the
query based on the one I originally submitted should return a 'yes' if the
customer has an e-mail address, and a 'no' if the customer doesn't have an
e-mail address. If you have any suggestions, I'd greatly appreciate it.

Thanks again,

Michael

:

Since your original query returns all of the customers that fit status 3, you
need to now determine which of those individuals have an e-mail. You can do
this by appending an additional search around the working search that you
have. This will use the results of the original search to feed the "new"
search (e-mail)

So you could do something like this with your current search:

SELECT DISTINCT [qryHelp1].ORDNUM_27, [qryHelp1].STATUS_27,
[qryHelp1].CUSTPO_27, [qryHelp1].SHPVIA_27, [qryHelp1].NAME_27,
[qryHelp1].ADDR1_27, [qryHelp1].ADDR2_27, [qryHelp1].CITY_27,
[qryHelp1].STATE_27, [qryHelp1].ZIPCD_27, [qryHelp1].CNTRY_27,
[qryHelp1].PHONE_27, [qryHelp1].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] LEFT JOIN
(
SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81 FROM
[Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27 WHERE (([SO Master].STATUS_27="3"))
)
AS qryHelp1
ON [Customer VAT].CUSTID_81 = [qryHelp1].CUSTID_81
WHERE (([Customer VAT].EMAIL1_81 is NOT Null));

There may be some field errors above, but if your original query provided a
list of customers that met status 3, then this should provide the list of
customers that meet status 3, and have an e-mail address.



:

Hello,

I'm working on a query to assist my company's shipping department when
shipping packages by UPS. I've integrated the UPS WorldShip program on their
computer with my company's main database, which contains information on our
customers (this is a Pervasive 8.6 database). This way, my shipping
department can simply enter an order number for a given order, and the
database automatically detects the Customer ID from the order in the SO
Master table, then pulls up the appropriate address information, so this
information doesn't have to be entered manually when generating shipping
labels. But now, I have a new problem related to this. I now want our
shipping department to e-mail customers whenever an order ships by UPS
(provided we have an e-mail address for the customer). I got a query from my
supervisor, created in an older version of Access (we currently use Access
2000), that automatically looks at the "SO Master" and "Customer VAT" tables
in our database (linked through their respective CUSTID fields), and displays
any orders that are "status 3", or open (A copy of the SQL text for the query
will appear below). I want to take that query one step further and set it up
so that if it finds a "status 3" order that has an e-mail address listed in
the EMAIL1_81 field of the "Customer VAT" table. Can anyone offer any
suggestions on how to do this? Unfortunately, my Access skills are a bit
rusty, and I keep drawing blanks. I would greatly appreciate any help anyone
can offer.

Thanks in advance,

Michael

P.S. Here is the SQL text for the original query I mentioned earlier:

SELECT DISTINCTROW [SO Master].ORDNUM_27, [SO Master].STATUS_27, [SO
Master].CUSTPO_27, [SO Master].SHPVIA_27, [SO Master].NAME_27, [SO
Master].ADDR1_27, [SO Master].ADDR2_27, [SO Master].CITY_27, [SO
Master].STATE_27, [SO Master].ZIPCD_27, [SO Master].CNTRY_27, [SO
Master].PHONE_27, [SO Master].CNTCT_27, [Customer VAT].EMAIL1_81
FROM [Customer VAT] INNER JOIN [SO Master] ON [Customer VAT].CUSTID_81 = [SO
Master].CUSTID_27
WHERE (([SO Master].STATUS_27="3"));
Click to expand...
Click to expand...
Click to expand...
 
Top