I am trying to modify the simple account.rpt file that comes with Emerald to
display only those users which get their billing by Email, with a seperate
version of the report for those with postal billing. I am using the
following SQL query qhich is all rights shoudl work, btu it tends to report
back all users with send bill checked.
SELECT
MasterAccounts."CustomerID", MasterAccounts."ReferredBy",
MasterAccounts."FirstName", MasterAccounts."LastName",
MasterAccounts."Company", MasterAccounts."Address1",
MasterAccounts."Address2", MasterAccounts."City", MasterAccounts."State",
MasterAccounts."Zip", MasterAccounts."PhoneHome",
MasterAccounts."PhoneWork", MasterAccounts."PhoneFax",
MasterAccounts."SalesPerson", MasterAccounts."CreateDate",
MasterAccounts."StartDate", MasterAccounts."maExpireDate",
MasterAccounts."Extension", MasterAccounts."PayPeriod",
MasterAccounts."PayMethod", MasterAccounts."PayInfo",
MasterAccounts."PaidThru", MasterAccounts."LastReceived",
MasterAccounts."PONumber", MasterAccounts."CreditCardNumber",
MasterAccounts."CreditCardExpire", MasterAccounts."LastModifyDate",
MasterAccounts."LastModifyUser", MasterAccounts."SendMethod",
SubAccounts."AccountID", SubAccounts."Login", SubAccounts."Shell",
SubAccounts."FirstName", SubAccounts."LastName", SubAccounts."PhoneHome",
SubAccounts."PhoneWork", SubAccounts."AccountType", SubAccounts."DiffCost",
SubAccounts."Cost", SubAccounts."Discount", SubAccounts."Description",
SubAccounts."Password", SubAccounts."MaidenName", SubAccounts."SystemType",
SubAccounts."ModemSpeed", SubAccounts."NetSoftware", SubAccounts."SignDate",
SubAccounts."saExpireDate", SubAccounts."Extension",
AccountTypes."Cost"
FROM
{ oj ("Emerald"."dbo"."MasterAccounts" MasterAccounts INNER JOIN
"Emerald"."dbo"."SubAccounts" SubAccounts ON
MasterAccounts."CustomerID" = SubAccounts."CustomerID")
INNER JOIN "Emerald"."dbo"."AccountTypes" AccountTypes ON
SubAccounts."AccountType" = AccountTypes."AccountType"}
WHERE
MasterAccounts."SendMethod" = 1
ORDER BY
MasterAccounts."CustomerID" ASC,
SubAccounts."AccountID" ASC
Any help appreciated. I figure someone out there has had to modify this one
at some point.
Richard Sensale
Network Administrator
VisitUs Internet, Inc.
5468 Schaefer Road
Dearborn, MI 48126 USA
(313) 945-5588
(313) 945-0202 fax
http://www.visitus.net
richards@visitus.net