karen
Posts: 99
|
| Posted: 09/01/2011, 12:16 AM |
|
Thanks for your response, phpminer. Those rows without any values aren't actually records with null values, the records don't exist at all. Here are more details about the tables I already have.
-- Table structure for table `members_members`
--
CREATE TABLE IF NOT EXISTS `members_members` (
`members_id` int(11) unsigned NOT NULL auto_increment,
`members_firstname` varchar(128) NOT NULL default '',
`members_lastname` varchar(128) NOT NULL default '',
`members_assembly_id` int(11) NOT NULL default '0',
`members_emailadd` varchar(128) default NULL,
`members_spouse` varchar(128) default NULL,
`members_phone` varchar(128) default NULL,
`members_mobile` varchar(128) default NULL,
`members_address` varchar(128) default NULL,
`members_suburb` varchar(128) default NULL,
`members_state` varchar(128) default NULL,
`members_postcode` varchar(10) default NULL,
`members_rnet_leader` int(1) NOT NULL default '0',
PRIMARY KEY (`members_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=331 ;
-- Table structure for table `fin_fbal`
--
CREATE TABLE IF NOT EXISTS `fin_fbal` (
`fbal_id` int(11) NOT NULL auto_increment,
`fbal_members_id` int(11) NOT NULL,
`fbal_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`fbal_user_id` int(11) NOT NULL,
`fbal_month` int(2) NOT NULL,
`fbal_year` year(4) NOT NULL,
`fbal_open` decimal(15,2) NOT NULL,
`fbal_alloc` decimal(15,2) NOT NULL,
`fbal_addinc` decimal(15,2) NOT NULL,
`fbal_interest` decimal(15,2) NOT NULL,
`fbal_gstrefund` decimal(15,2) NOT NULL,
`fbal_repayments` decimal(15,2) NOT NULL,
`fbal_insure` decimal(15,2) NOT NULL,
`fbal_ratestax` decimal(15,2) NOT NULL,
`fbal_furniture` decimal(15,2) NOT NULL,
`fbal_fuel` decimal(15,2) NOT NULL,
`fbal_phone` decimal(15,2) NOT NULL,
`fbal_maint` decimal(15,2) NOT NULL,
`fbal_improve` decimal(15,2) NOT NULL,
`fbal_hosp` decimal(15,2) NOT NULL,
`fbal_books` decimal(15,2) NOT NULL,
`fbal_stationery` decimal(15,2) NOT NULL,
`fbal_clothing` decimal(15,2) NOT NULL,
`fbal_events` decimal(15,2) NOT NULL,
`fbal_gifts` decimal(15,2) NOT NULL,
`fbal_dev` decimal(15,2) NOT NULL,
`fbal_travel` decimal(15,2) NOT NULL,
`fbal_transfer` decimal(15,2) NOT NULL,
`fbal_carpurchase` decimal(15,2) NOT NULL,
`fbal_carinsure` decimal(15,2) NOT NULL,
`fbal_carmaint` decimal(15,2) NOT NULL,
`fbal_carfuel` decimal(15,2) NOT NULL,
`fbal_carreg` decimal(15,2) NOT NULL,
`fbal_driverslic` decimal(15,2) NOT NULL,
`fbal_medtreat` decimal(15,2) NOT NULL,
`fbal_medinsure` decimal(15,2) NOT NULL,
`fbal_bankfees` decimal(15,2) NOT NULL,
`fbal_schoolfees` decimal(15,2) NOT NULL,
`fbal_gstpaid` decimal(15,2) NOT NULL,
`fbal_drawings` decimal(15,2) NOT NULL,
`fbal_close` decimal(15,2) NOT NULL,
`fbal_taxdeducted` decimal(15,2) NOT NULL,
`fbal_alopen` decimal(15,2) NOT NULL,
`fbal_alaccrued` decimal(15,2) NOT NULL,
`fbal_altaken` decimal(15,2) NOT NULL,
`fbal_alclose` decimal(15,2) NOT NULL,
`fbal_alcommenced` datetime default NULL,
`fbal_alcompleted` datetime default NULL,
PRIMARY KEY (`fbal_id`),
UNIQUE KEY `fbal_members_id` (`fbal_members_id`,`fbal_month`,`fbal_year`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=147 ;
This is what happens for the transactions. Every quarter, the users will enter records for their related member id. However, this is not mandatory so there will be members without records for select quarters.
I have a table with all the unique members which I can refer for all the fin_fbal records. But I don't have a table with all the quarters of the years. Therefore, if there are no records for a particular member for the quarter selected, nothing will be displayed for this member and quarter in the report. I want the report to behave such that there are records for every member in every quarter so that it appears as though the amounts are null.
So I'm thinking of creating a temporary table with member id, month and year for every member and quarter of the selected period. Or maybe I just have to create a temporary table with all the month and year for the quarters and iterate this through every member of the permanent table members_members. Is this the right way to go? Thanks so much.
Cheers,
Karen
|
