select ig.iid as itineraryid , ig.id as groupid, gf.id, gf.fname, sum(fi.amt), sum(fr.amt)
from family_receipt_dtls as fr, family_invoice as fi, group_families as gf, itinerary_groups as ig
where ig.active=1 and gf.gid=ig.id and fr.fid=gf.id and fi.fid=gf.id
group by gf.id;

CREATE TABLE `family_invoice` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID',
  `fid` int(10) unsigned NOT NULL COMMENT 'Family ID',
  `invoiceto` text NOT NULL COMMENT 'Invoice To',
  `selfinvoice` varchar(15) NOT NULL COMMENT 'Managed invoice',
  `dt` date NOT NULL COMMENT 'Date of Invoice',
  `amt` int(10) unsigned NOT NULL COMMENT 'Amount',
  `amtwords` text NOT NULL COMMENT 'Amount in words',
  `comments` text COMMENT 'Comments',
  `agent` varchar(25) NOT NULL COMMENT 'Agent',
  PRIMARY KEY  (`id`),
  KEY `fid` (`fid`,`selfinvoice`,`dt`),
  KEY `agent` (`agent`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Invoice generation' AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `family_receipt_dtls`
-- 

CREATE TABLE `family_receipt_dtls` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID',
  `dttm` datetime NOT NULL COMMENT 'Date time of entry',
  `fid` int(10) unsigned NOT NULL COMMENT 'Family ID',
  `receivedfrom` text NOT NULL COMMENT 'Received From',
  `pmethod` tinyint(3) unsigned NOT NULL COMMENT 'Payment Method',
  `docno` varchar(50) NOT NULL COMMENT 'Referance number of payment method  such as cheque draft number',
  `amt` int(10) unsigned NOT NULL COMMENT 'Amount paid',
  `amtwords` text NOT NULL COMMENT 'Amount in words',
  `comments` text NOT NULL COMMENT 'Comments',
  `agent` varchar(25) NOT NULL COMMENT 'Agent',
  PRIMARY KEY  (`id`),
  KEY `dttm` (`dttm`,`fid`,`pmethod`),
  KEY `agent` (`agent`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Payments made by family/inidividuals' AUTO_INCREMENT=2 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `group_families`
-- 

CREATE TABLE `group_families` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Family ID',
  `gid` int(10) unsigned NOT NULL COMMENT 'Group ID',
  `fname` varchar(75) NOT NULL COMMENT 'Family Name',
  `familyhead` bigint(20) unsigned default '0' COMMENT 'Head of the family',
  PRIMARY KEY  (`id`),
  KEY `gid` (`gid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Group Families' AUTO_INCREMENT=6 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `itinerary_groups`
-- 

CREATE TABLE `itinerary_groups` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Unique Group ID',
  `iid` int(10) unsigned NOT NULL COMMENT 'Itinerary ID',
  `grpname` varchar(150) NOT NULL COMMENT 'Group name',
  `fromloc` varchar(50) NOT NULL COMMENT 'From Location',
  `toloc` varchar(50) NOT NULL COMMENT 'To Location',
  `fromdt` date NOT NULL COMMENT 'From Date',
  `todt` date NOT NULL COMMENT 'Till Date',
  `grouplimit` mediumint(8) unsigned NOT NULL COMMENT 'Group Limit',
  `comments` text NOT NULL COMMENT 'Comments',
  `active` tinyint(1) NOT NULL default '1' COMMENT 'is this group active or no',
  `agent` varchar(25) NOT NULL COMMENT 'Agent',
  PRIMARY KEY  (`id`),
  KEY `iid` (`iid`),
  KEY `active` (`active`),
  KEY `agent` (`agent`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Groups for this particular itinerary' AUTO_INCREMENT=5 ;