fly_mo
Posts: 109
|
| Posted: 09/08/2008, 8:00 PM |
|
Hello All,
this is drivin me mad...
I've built a report with several small reports providing a series of totalled values based on a single PK. Each report is a factor in a process - joined by that key.
I need to be able to take each report total and recalulate across all reports to form a single reconcillialtion total.
Is there a way to access the totals from each report and use them?
Thanks
John
I can get all of the tables(10) in sql but I have not been able to get the required sums in place.
_________________
John |
 |
 |
mentecky
Posts: 321
|
| Posted: 09/09/2008, 6:18 PM |
|
I use unions a lot for some really weird reports, of course you'll have to do them all in SQL. What I do is add an extra field for where the data came from, ie... table_name = 'sales' or whatever. Then group by that field first. The trick is don't group in the query builder, let the report builder build the groups.
The other trick is to make sure each union returns ALL the required fields or the report builder gets confused. For example, if one table doesn't contain a field in a row, create an aliased field set to 0.
Basically, make each UNION report the exact same fields... when the table doesn't have a field another table does have, create an alias of the same name and set it to a default value.
After that, complete the report builder and let it group stuff for you.
I hope that makes sense... I have a cold so I'm a little on the slow side today.
Rick
_________________
http://www.ccselite.com |
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/10/2008, 5:10 AM |
|
Thanks Rick,
I'll give that a try.
Frustrating thing is that I took the tables into MS Access did the query in about 3 minutes and it's absolutely what I need..except it's in MS sql which doesn't translate into the real world of sql....... 
_________________
John |
|
|
|
mentecky
Posts: 321
|
| Posted: 09/10/2008, 5:25 AM |
|
John,
Have you tried this tool? http://www.toadsoft.com/toadmysql/Overview.htm
I use it everyday and it's free!!!
Rick
_________________
http://www.ccselite.com |
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/10/2008, 5:31 AM |
|
Thanks Rick,
I normally use Navicat or phpMyAdmin - but sometimes if my skills are lacking I need a little help through.
The toad seems to be an excellent option I'll give it a try.
Thanks again
John
_________________
John |
|
|
|
mentecky
Posts: 321
|
| Posted: 09/10/2008, 5:37 AM |
|
Quote fly_mo:
Thanks Rick,
I normally use Navicat or phpMyAdmin - but sometimes if my skills are lacking I need a little help through.
The toad seems to be an excellent option I'll give it a try.
Thanks again
John
Skills lacking... That's why I use Toad. 
Good luck! If you have more probs post the SQL here and i'll take a look.
Rick
_________________
http://www.ccselite.com |
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/10/2008, 9:12 AM |
|
Hi Rick,
Great program...
I'm about 95% there just can't get the final two fields from one table the sql doesn't seem to carry from the sub query. I'd appreciate if you'd take a look to see if there is anything obvious that I'm missing.
Full query;
SELECT tblprocessed_details.processed_detail_ID,
tblprocessed_details.Amount_processed,
tblprocessed_details.amount_crushed,
tblbooking_shipped.shipped_quantity,
tblbooking_shipped.returned_quantity,
`From Clause SubQuery`.processed_detail_ID,
`From Clause SubQuery`.sum_tran_in,
`From Clause SubQuery`.sum_tran_out
FROM ( seedmanager.tblprocessed_details tblprocessed_details
LEFT OUTER JOIN
(SELECT tbltransfer.processed_detail_ID,
tbltransfer.amount_transfered_in AS sum_tran_in,
tbltransfer.amount_transfered_out AS sum_tran_out
FROM seedmanager.tbltransfer tbltransfer
GROUP BY tbltransfer.processed_detail_ID)
`From Clause SubQuery`
ON (tblprocessed_details.processed_detail_ID =
`From Clause SubQuery`.processed_detail_ID)
AND (tblprocessed_details.processed_detail_ID =
`From Clause SubQuery`.processed_detail_ID))
RIGHT OUTER JOIN
seedmanager.tblbooking_shipped tblbooking_shipped
ON (tblprocessed_details.processed_detail_ID =
tblbooking_shipped.processed_detail_id)
GROUP BY tblprocessed_details.processed_detail_ID,
tblprocessed_details.Amount_processed,
tblprocessed_details.amount_crushed,
`From Clause SubQuery`.processed_detail_ID
Sub Query
SELECT tbltransfer.processed_detail_ID,
sum(tbltransfer.amount_transfered_in) AS sum_tran_in,
sum(tbltransfer.amount_transfered_out) AS sum_tran_out
FROM seedmanager.tbltransfer tbltransfer
GROUP BY tbltransfer.processed_detail_ID
Hope I didn't edit this too much...
The only two values that won't work are sub_tran_in & sub_tran_out.
Thanks
John
_________________
John |
|
|
|
mentecky
Posts: 321
|
| Posted: 09/10/2008, 9:17 AM |
|
Can you post the table defs for me?
Rick
_________________
http://www.ccselite.com |
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/10/2008, 9:40 AM |
|
Hi Rick,
I just did an export - hope this is what you needed...
-- phpMyAdmin SQL Dump
-- version 2.10.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 10, 2008 at 12:37 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.3
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `seedmanager`
--
-- --------------------------------------------------------
--
-- Table structure for table `tblbooking_shipped`
--
CREATE TABLE `tblbooking_shipped` (
`delivery_order_ID` int(11) NOT NULL auto_increment,
`Booking_detail_ID` int(11) default NULL,
`Order_ID` int(11) default NULL,
`processed_detail_id` int(11) default NULL,
`shipped_quantity` decimal(10,2) default NULL,
`returned_quantity` decimal(10,2) default NULL,
`delivery_date` date default NULL,
`ship_address_ID` int(11) default NULL,
`processor_address_ID` int(11) default NULL,
`confirmed` tinyint(4) default NULL,
`comments` varchar(500) default NULL,
`ship_date` datetime default NULL,
`shipped_by` varchar(25) default NULL,
`Lot_number` varchar(50) default NULL,
PRIMARY KEY (`delivery_order_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=115 ;
--
-- Dumping data for table `tblbooking_shipped`
--
INSERT INTO `tblbooking_shipped` VALUES (111, 72, 45, 16, 100.00, 10.00, '2008-09-07', 39, 1, 1, NULL, NULL, NULL, NULL);
INSERT INTO `tblbooking_shipped` VALUES (112, 73, 45, 16, 250.00, 5.00, '2008-09-07', 39, 1, 1, NULL, NULL, NULL, NULL);
INSERT INTO `tblbooking_shipped` VALUES (113, 75, 12, 16, 125.00, 5.00, '2008-09-08', 42, 1, 1, NULL, NULL, NULL, NULL);
INSERT INTO `tblbooking_shipped` VALUES (114, 77, 12, 16, 125.00, 10.00, '2008-09-09', 42, 1, 1, NULL, NULL, NULL, NULL);
-- --------------------------------------------------------
--
-- Table structure for table `tblprocess_order_details`
--
CREATE TABLE `tblprocess_order_details` (
`process_order_details_ID` int(11) NOT NULL auto_increment,
`process_order_ID` int(11) NOT NULL,
`Variety_ID` int(11) default NULL,
`Treatment_ID` int(11) default NULL,
`Ped_status_ID` int(11) default NULL,
`Processing_required` double default NULL,
`Processing_sugg` double default NULL,
`Unit_ID` int(11) default NULL,
`Package_ID` int(11) default NULL,
PRIMARY KEY (`process_order_details_ID`),
UNIQUE KEY `PrimaryKey` (`process_order_details_ID`),
KEY `Package_ID` (`Package_ID`),
KEY `Ped_status_ID` (`Ped_status_ID`),
KEY `process_order_ID` (`process_order_ID`),
KEY `Treatment_ID` (`Treatment_ID`),
KEY `Unit_ID` (`Unit_ID`),
KEY `Variety_ID` (`Variety_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;
--
-- Dumping data for table `tblprocess_order_details`
--
INSERT INTO `tblprocess_order_details` VALUES (25, 50, 8, 2, 3, 1000, 1250, 1, 5);
INSERT INTO `tblprocess_order_details` VALUES (26, 50, 9, 3, 3, 1000, 1250, 1, 5);
INSERT INTO `tblprocess_order_details` VALUES (27, 50, 10, 4, 1, 1000, 1250, 1, 5);
INSERT INTO `tblprocess_order_details` VALUES (28, 51, 8, 2, 3, 900, 1000, 1, 5);
INSERT INTO `tblprocess_order_details` VALUES (29, 51, 9, 3, 3, 900, 1000, 1, 5);
INSERT INTO `tblprocess_order_details` VALUES (30, 51, 10, 4, 1, 900, 1000, 1, 5);
-- --------------------------------------------------------
--
-- Table structure for table `tbltransfer`
--
CREATE TABLE `tbltransfer` (
`transfer_ID` int(11) NOT NULL auto_increment,
`processed_detail_ID` int(11) NOT NULL,
`proc_address_ID` int(11) NOT NULL COMMENT 'tran out from',
`address_id` int(11) NOT NULL COMMENT 'transfer to',
`amount_transfered_in` decimal(10,2) default NULL,
`amount_transfered_out` decimal(10,2) default NULL,
`Unit_ID` int(11) NOT NULL,
`Package_ID` int(11) default NULL,
`transfer_date` date NOT NULL,
`comments` varchar(500) default NULL,
`enteredby` varchar(25) default NULL,
`entered` datetime default NULL,
PRIMARY KEY (`transfer_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
--
-- Dumping data for table `tbltransfer`
--
INSERT INTO `tbltransfer` VALUES (7, 16, 2, 2, 50.00, NULL, 1, 5, '2008-09-07', NULL, NULL, NULL);
INSERT INTO `tbltransfer` VALUES (8, 16, 1, 1, 60.00, NULL, 1, 5, '2008-09-09', NULL, NULL, NULL);
INSERT INTO `tbltransfer` VALUES (9, 16, 1, 2, NULL, 75.00, 1, 5, '2008-09-09', NULL, NULL, NULL);
INSERT INTO `tbltransfer` VALUES (10, 16, 1, 2, NULL, 25.00, 1, 5, '2008-09-09', NULL, NULL, NULL);
_________________
John |
|
|
|
wkempees
Posts: 1679
|
| Posted: 09/10/2008, 1:37 PM |
|
Why not do CCDLookups() for each?
Let me know if interested and I will give it a go.
Base idea:
Total fields, each have a CCDLookup('sum(colname)', 'tablename','condition', connection)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/10/2008, 1:44 PM |
|
Hello Walter,
Another fresh idea, sometimes you get too into trying make it work to see other possibilities.
I'd be interested in any suggestions that you may have.
Thanks
John
I'm still working with Toad sql - great program. If it helps the learning curve...
_________________
John |
|
|
|
wkempees
Posts: 1679
|
| Posted: 09/10/2008, 5:13 PM |
|
John,
My response was (1) to the wrong post, (2) probably applicable if you were still doing reports.
You are way beyond standard reporting, I read Ricks post with interest, stating not to do grouping but let reportbuilder handle that, nice tip.
When you solved your puzzle, I'd like an explanation for this:
Quote :I've built a report with several small reports
As to your earlier question:
Quote :The only two values that won't work are sub_tran_in & sub_tran_out.
In the Subquery you create aliases sub_tran_in/out
In the full query you select the fields themselves withou aliasing them.
As it confused me reading the query, it might confuse the DB just as much and might resolve to ambigous names.
Please check that.
My2cts.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/10/2008, 5:32 PM |
|
Hi Walter,
explanations are simple - inexperience and a deadline...make for shortcuts...
Once I get a resolution, I'll mark the post solved - with solution.
One report would be great -if I could only get the query to work....
Regards
John
_________________
John |
|
|
|
wkempees
Posts: 1679
|
| Posted: 09/10/2008, 5:47 PM |
|
Quote :tblprocessed_details
Not in your post of table declarations.
My Navicat complaints (gribbits) at your Toadquery, lol
have you dumped the wrong table? as the fields are complete misfit too.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/10/2008, 5:53 PM |
|
Sorry....not enough caffeine today.
-- phpMyAdmin SQL Dump
-- version 2.10.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 10, 2008 at 08:52 PM
-- Server version: 5.0.41
-- PHP Version: 5.2.3
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `seedmanager`
--
-- --------------------------------------------------------
--
-- Table structure for table `tblprocessed_details`
--
CREATE TABLE `tblprocessed_details` (
`processed_detail_ID` int(11) NOT NULL auto_increment,
`Processed_ID` int(25) NOT NULL default '0',
`Lot_number` varchar(255) default NULL,
`Variety_ID` int(11) default NULL,
`Germ` double default NULL,
`Seed/Kg` double default NULL,
`Certificate#` varchar(255) default NULL,
`Amount_processed` decimal(10,2) default NULL,
`amount_crushed` decimal(10,2) default NULL,
`Unit_ID` int(11) default NULL,
`Package_ID` int(11) default NULL,
`Highest_Pedigree` int(11) default NULL,
`Pedigree_Status` int(11) default NULL,
`Treatment_ID` int(11) default NULL,
`QAT_passed` tinyint(4) default NULL,
`GMO_passed` tinyint(4) default NULL,
`Comments` longtext,
PRIMARY KEY (`processed_detail_ID`),
UNIQUE KEY `Lot_num` (`Lot_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;
--
-- Dumping data for table `tblprocessed_details`
--
INSERT INTO `tblprocessed_details` VALUES (16, 8, 'LN-123', 8, 85, 2500, 'CC-123', 1000.00, 25.00, 1, 5, 3, 3, 2, 1, 1, NULL);
INSERT INTO `tblprocessed_details` VALUES (17, 8, 'LN-124', 9, 80, 2400, 'CC-124', 500.00, NULL, 1, 5, 3, 3, 3, 1, 0, NULL);
INSERT INTO `tblprocessed_details` VALUES (18, 8, 'LN-125', 10, 85, 2000, 'CC-125', 500.00, NULL, 1, 5, 1, 1, 4, 0, 1, NULL);
INSERT INTO `tblprocessed_details` VALUES (19, 9, 'LN-333', 8, 85, 2600, 'CC-222', 600.00, NULL, 1, 5, 3, 3, 2, 0, 0, NULL);
INSERT INTO `tblprocessed_details` VALUES (20, 9, 'LN-223', 9, 85, 2400, 'CC-223', 600.00, NULL, 1, 5, 3, 3, 3, 0, 0, NULL);
INSERT INTO `tblprocessed_details` VALUES (21, 9, 'LN-224', 10, 80, 1600, 'CC-224', 300.00, NULL, 1, 5, 1, NULL, 4, 0, 0, NULL);
_________________
John |
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/11/2008, 3:03 PM |
|
I built this report in MS Access in less than 20 minutes........^&^%&^%&%
setting it up with more powerful tools should be a snap - obviously not... 
sleepless in sql
_________________
John |
|
|
|
wkempees
Posts: 1679
|
| Posted: 09/14/2008, 2:47 PM |
|
If a query would report:
processed_detail_id = 16
Amount_processed = 1000
Amount_crushed =25
shipped_quantity = 100
returned_quantity = 10
sum_tran_in =110
sum_tran_out = 100
Would you be interested in the query?
If not, stop reading here.
SELECT tblprocessed_details.processed_detail_ID,
tblprocessed_details.Amount_processed,
tblprocessed_details.amount_crushed,
tblbooking_shipped.shipped_quantity,
tblbooking_shipped.returned_quantity,
(SELECT sum(amount_transfered_in)
FROM seedmanager.tbltransfer
WHERE processed_detail_ID = tblprocessed_details.processed_detail_ID
GROUP BY processed_detail_ID) AS sum_tran_in,
(SELECT sum(amount_transfered_out)
FROM seedmanager.tbltransfer
WHERE processed_detail_ID = tblprocessed_details.processed_detail_ID
GROUP BY processed_detail_ID) AS sum_tran_out
FROM seedmanager.tblprocessed_details tblprocessed_details
RIGHT OUTER JOIN seedmanager.tblbooking_shipped tblbooking_shipped
ON ( tblprocessed_details.processed_detail_ID = tblbooking_shipped.processed_detail_id)
GROUP BY tblprocessed_details.processed_detail_ID
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/15/2008, 5:28 AM |
|
Walter,
I thank you.....
Now I'm going to hit my sql books again to understand how that works. So much simpler than my own efforts......a fresh set of eye's and skill
Much appreciated.
John
_________________
John |
|
|
|
wkempees
Posts: 1679
|
| Posted: 09/15/2008, 8:28 AM |
|
How it works:
SQL (most of them anyway) support SUBSELECTS.
Select x, y, z from table
is the simple way of selecting the value of the fields x, y and z from a table.
the subselects takes the place of a field (say z) by specifying its own select.
The restriction being that a subselect can only return one value, not a set of values.
In your case:
SELECT sum(amount_transfered_in)
FROM seedmanager.tbltransfer
WHERE processed_detail_ID = tblprocessed_details.processed_detail_ID
GROUP BY processed_detail_ID
retreives one value, the sum of all specified records.
Hope this simplifies matters.
In fact earlier I posted why not do a DLookup() in the report.
A Dlookup() could do exactly the same and is a good way to calculate totals on secondary tables.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
fly_mo
Posts: 109
|
| Posted: 09/15/2008, 9:55 AM |
|
Clear explanation, Thanks Walter.
I will now try the DLookup() ...
Then on to fpdf....
Thanks again
John
_________________
John |
|
|
|
|
