I am trying to create a report that will show me all the checks I have
written on my system in numerical order, the problem is I have my checks
stored in two different tables in the database. These two tables split up
expense checks and client checks, both tables have quite different data,
however they do share some common data (check_Num), Ammount, and a date
drafted, the other data isn't the same data types because I use a customer
ID(number) to pull the name of the client for the check and I just type the
name of the recipient(text) for the expense checks. Right now I am able to
get all the checks in order with the following query.
-----------------------------------
<cfquery name="rsGetPayments" datasource="# dsn#">
SELECT Check_Num, CheckAmount, expen_Date
FROM tbl_Expenses
WHERE expen_Date BETWEEN #CreateODBCDateTime(cFullStartDate)# AND
#CreateODBCDateTime(cFullEndDate)#
UNION
SELECT Check_Num, pur_Amount,
pur_Date_Purchased
FROM tbl_HCFA_Purchase_Transactions
WHERE pur_Date_Purchased BETWEEN #CreateODBCDateTime(cFullStartDate)#
AND
#CreateODBCDateTime(cFullEndDate)#
ORDER BY Check_Num
</cfquery>
This gets me a unioned list sorted by check number in a date range supplied
by a form.
My question, is there a better way to union this data together because right
now after I run this query I am going to have to run two separate queries in
each table to get who the check was made out to and what the expense code
was for the check.
Right now this one query takes about 50 seconds to run and I imagine will
only get longer with how many checks I write.
Thanks in advance,
Mallek
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289110
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
