Calculations based on data from mutiple tables

[ericsante]

I have several tables that contains info for my customer. The tblCustomer table contains keys for the data from other tables. I need to perform calculations based on the values stored in the other tables here is the database structure and some of the data.

Any assistance would be most helpful. ???

there are 3 calculations, I will be performing a search by date range to get my dataset from tbltranslog:

1. based on the SubID in tbltranslog I need calculate the "Fee" based on the value that is set in tblCustomer, I need to do this for each transaction that is in tbltranslog
2. calculate the check fee for each item that is tbltranslog that is a check, again the rate for the check is set in the tblCustomer.
3. calculate the credit card fee for each item that is tbltranslog that is a credit card, again the rate for the check is set in tblCustomer

CREATE TABLE `tblCustomer` (
`Customer_ID` int(5) NOT NULL auto_increment,
`AcctID` varchar(8) NOT NULL,
`SubID` varchar(8) NOT NULL,
`Merchant` varchar(32) NOT NULL,
`Status` varchar(8) NOT NULL,
`Address1` varchar(32) NOT NULL,
`Address2` varchar(32) NOT NULL,
`City` varchar(32) NOT NULL,
`State` varchar(20) NOT NULL,
`Zip` varchar(10) NOT NULL,
`Phone` varchar(12) NOT NULL,
`Fax` varchar(12) NOT NULL,
`Email` varchar(32) NOT NULL,
`Contact` varchar(32) NOT NULL,
`BankID` varchar(20) NOT NULL,
`AuthNet` varchar(20) NOT NULL,
`MID` varchar(20) NOT NULL,
`TID` varchar(20) NOT NULL,
`TermKey_ID` varchar(32) NOT NULL,
`CCFees_ID` varchar(4) NOT NULL,
`CKFees_ID` varchar(4) NOT NULL,
`Fee` varchar(20) NOT NULL,
`MCSAcct` varchar(19) NOT NULL,
`DebitAcct` varchar(50) NOT NULL,
`PaymentType` varchar(16) NOT NULL,
`AVS` varchar(4) NOT NULL,
`CVV2` varchar(3) NOT NULL,
`Reserve` varchar(4) NOT NULL,
`Payout` varchar(4) NOT NULL,
`Pricing_ID` varchar(4) NOT NULL,
PRIMARY KEY (`Customer_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--
-- Dumping data for table `tblCustomer`
--

INSERT INTO `tblCustomer` (`Customer_ID`, `AcctID`, `SubID`, `Merchant`, `Status`, `Address1`, `Address2`, `City`, `State`, `Zip`, `Phone`, `Fax`, `Email`, `Contact`, `BankID`, `AuthNet`, `MID`, `TID`, `TermKey_ID`, `CCFees_ID`, `CKFees_ID`, `Reseller`, `MCSAcct`, `DebitAcct`, `PaymentType`, `AVS`, `CVV2`, `Reserve`, `Payout`, `Pricing_ID`) VALUES (12, 'ABCD1', '10001', 'Eric''s Test Acco', '1000', '123 some street', '', 'Some Town', 'Some State', '33137', '212-555-1212', '212-555-1212', '[email protected]', 'Eric', 'My Bank', '1006', '12345', '12345', '1234-12', '1009', '1001', 'No', '', '12345', 'ACH', 'Yes', 'Yes', '1000', '1002', '');

CREATE TABLE `tblCCFees` (
`CCFees_ID` varchar(4) NOT NULL,
`Rate` varchar(5) NOT NULL,
PRIMARY KEY (`CCFees_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblCCFees`
--

INSERT INTO `tblCCFees` (`CCFees_ID`, `Rate`) VALUES ('1000', '.25'),
('1001', '.30'),
('1002', '.35'),
('1003', '.40'),
('1004', '.45'),
('1005', '.50'),
('1006', '.55'),
('1008', '1.00'),
('1007', '.75'),
('1009', '1.25'),
('1010', '1.35'),
('1012', '1.45'),
('1013', '1.50');

INSERT INTO `tblCKFees` (`CKFees_ID`, `Rate`) VALUES ('1000', '1.00'),
('1001', '.75'),
('1002', '1.25'),
('1003', '1.50');


INSERT INTO `tblReserveRate` (`ReserveRate_ID`, `Reserve`) VALUES (1000, '10.00'),
(1010, '9.0'),
(1011, '9.25'),
(1012, '9.5'),
(1013, '9.75'),
(1014, '8.0'),
(1015, '8.25'),
(1016, '8.5'),
(1017, '8.75');


CREATE TABLE `tbltranslog` (
`TransLog_ID` int(10) NOT NULL auto_increment,
`SubID` int(7) NOT NULL,
`Date` char(21) NOT NULL,
`Amount` varchar(9) NOT NULL,
`TransType` varchar(15) NOT NULL,
`TransResult` varchar(8) NOT NULL,
`OrderNo` varchar(10) NOT NULL,
`TransKey` varchar(10) NOT NULL,
PRIMARY KEY (`TransLog_ID`),
UNIQUE KEY `OrderNo` (`OrderNo`)
) ENGINE=MyISAM AUTO_INCREMENT=10479 DEFAULT CHARSET=latin1 AUTO_INCREMENT=10479 ;

--
-- Dumping data for table `tbltranslog`
--

INSERT INTO `tbltranslog` (`TransLog_ID`, `SubID`, `Date`, `Amount`, `TransType`, `TransResult`, `OrderNo`, `TransKey`) VALUES (10095, 10001, '12/05/2006 20:48:43', '25.00', 'Sale', 'Approved', '53807312', '68295959');
INSERT INTO `tbltranslog` (`TransLog_ID`, `SubID`, `Date`, `Amount`, `TransType`, `TransResult`, `OrderNo`, `TransKey`) VALUES (10094, 10001, '12/05/2006 18:56:04', '29.99', 'Check Pre-Auth', 'Approved', '53805218', '68293514');
INSERT INTO `tbltranslog` (`TransLog_ID`, `SubID`, `Date`, `Amount`, `TransType`, `TransResult`, `OrderNo`, `TransKey`) VALUES (10093, 10001, '12/05/2006 18:50:06', '29.99', 'Sale', 'Approved', '53805092', '68293359');