racko

I'm beginning to think this won't work period. Since I need to calculate things that are past due from today and back, and things that are not due from today on. Using group doesn't give me the option to add things up using this technique, for lack of a better word. Here is an image of what I'm trying to calculate, again any ideas would be great [attachment deleted by admin]

I get this error: PHP Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Microsoft JET Database Engine<br/><b>Description:</b> You tried to execute a query that does not include the specified expression 'Customer' as part of an aggregate function.' When I change my code to this: $rs = $conn->Execute("Select Location, Customer, Visual FROM hose WHERE Customer = '" . $_SESSION['account'] . "' AND (Status <> '0' AND Status <> 'scrap') GROUP BY Location order by Location ASC");

Ok, so I'm guessing this is turning into an sql problem (though I'm using an access db ) I've done the Group by and it almost works, problem is this: $rs = $conn->Execute("Select Location, Customer, Visual FROM hose WHERE Customer = '" . $_SESSION['account'] . "' AND (Status <> '0' AND Status <> 'scrap') GROUP BY Location, Customer, Visual order by Location ASC"); Only way I could get it to work was by adding all the selects in the group by, and if I exclude them, which I can because I need them, I get a PHP Fatal Error...

I just read on google you can't return more then one thing with return... as for the looping array mine only returns array and nothing else.. I'm still playing around with the group by clause and it doesn't like me very much but I'll keep plugging away at it

I think the problem is with the return on the ListLocations() function, when I just echo $Location[$count] all the locations list, but the return function only returns the first variable in the list of locations. Even though it's in the while loop??? How do I get return to actually return everything I get the feeling I'm talking to myself at this point :'(

I've removed the first while loop from the third function since all it did was print out the same location ID for the total number of locations...

I thought so Ok Here are three of the functions I'm working with to try and get it going... function locationCheck(){ extract($GLOBALS); $rs = $conn->Execute("Select * From Location WHERE account = '" . $_SESSION['account'] . "'"); $number=0; while (!$rs->EOF) { $fv = $rs->Fields("Location"); $Location[$count]=$fv->value; $count++; $rs->MoveNext(); $number++; } return $number; $rs->Close(); } function ListLocations (){ extract($GLOBALS); $rs = $conn->Execute("Select * From Location WHERE account = '" . $_SESSION['account'] . "'"); $number=locationCheck(); $loc = 0; while ($loc <= $number){ while (!$rs->EOF) { $fv = $rs->Fields("Location"); $Location[$count]=$fv->value; // echo "<tr /><td align=\"center\" />". $Location[$count]."</td />"; return $Location[$count]; $count++; $rs->MoveNext(); } $loc++; } $rs->Close(); } function getSummary(){ extract($GLOBALS); $number=locationCheck(); $loc = 0; while ($loc <= $number){ $rs = $conn->Execute("Select * From Hose WHERE Customer = '" . $_SESSION['account'] . "' AND (Status > '0' OR Status <> 'scrap') AND Location = '" . ListLocations() . "'"); $count = 0; while (!$rs->EOF) { // $fv = $rs->Fields("Location"); // $Location[$count]=$fv->value; // echo "<td />" . $Location[$count] . "</td />"; $fv = $rs->Fields("Visual"); $Visual[$count]=$fv->value; $due_date = strtotime(date("Y-m-d", strtotime($Visual[$count])) . "+".getVisualRetest()."month"); $date = date("m", $due_date); $date_year = date("Y", $due_date); if ($date == "01"){ if ($date_year < date("Y") || $date <= date("m")){ $jan_count++; }else { $jan_due++; } }if ($date == "02"){ if ($date_year < date("Y") || $date <= date("m")){ $feb_count++; }else { $feb_due++; } }if ($date == "03"){ if ($date_year < date("Y") || $date <= date("m")){ $mar_count++; }else { $mar_due++; } }if ($date == "04"){ if ($date_year < date("Y") || $date <= date("m")){ $apr_count++; }else { $apr_due++; } }if ($date == "05"){ if ($date_year < date("Y") || $date <= date("m")){ $may_count++; }else { $may_due++; } }if ($date == "06"){ if ($date_year < date("Y") || $date <= date("m")){ $jun_count++; }else { $jun_due++; } }if ($date == "07" && $Visual[$count] != "-"){ if ($date_year < date("Y") || $date <= date("m")){ $jul_count++; }else { $jul_due++; } } if ($date == "08"){ if ($date_year < date("Y") || $date <= date("m")){ $aug_count++; }else { $aug_due++; } }if ($date == "09"){ if ($date_year < date("Y") || $date <= date("m")){ $sep_count++; }else { $sep_due++; } }if ($date == "10"){ if ($date_year < date("Y") || $date <= date("m")){ $oct_count++; }else { $oct_due++; } }if ($date == "11"){ if ($date_year < date("Y") || $date <= date("m")){ $nov_count++; }else { $nov_due++; } }if ($date == "12"){ if ($date_year < date("Y") || $date <= date("m")){ $dec_count++; }else { $dec_due++; } } $count++; $rs->MoveNext(); } $rs->Close(); $total_past=$jan_count+$feb_count+$mar_count+$apr_count+$may_count+$jun_count+$jul_count+$aug_count+$sep_count+$oct_count+$nov_count+$dec_count; $total_due=$jan_due+$feb_due+$mar_due+$apr_due+$may_due+$jun_due+$jul_due+$aug_due+$sep_due+$oct_due+$nov_due+$dec_due; $grand_total=$total_due+$total_past; $percentage_due = $total_due/$grand_total*100; $percentage_past = $total_past/$grand_total*100; $zero_decimal_due = number_format($percentage_due); $zero_decimal_past = number_format($percentage_past); echo "<tr /><td align=\"center\" />".ListLocations ()."</td />"; echo "<td align=\"center\" bgcolor=\"#00FF00\" />".$total_due."</td />"; echo "<td align=\"center\" bgcolor=\"#FF0000\" />".$total_past."</td />"; echo "<td align=\"center\" />".$grand_total."</td />"; echo "<td align=\"center\" bgcolor=\"#FF0000\" />".$zero_decimal_past."%</td />"; echo "<td align=\"center\" bgcolor=\"#00FF00\" />".$zero_decimal_due."%</td /></tr >"; $loc++; } } I know I should use case statements instead of all these if's but they work

Hi folks, Need some help with a function I'm trying to write a function that pulls data from an MS Access DB. There are a bunch of locations which show items that are due on January, February, etc... I need to show one location per line, with the Grand totals showing which is not due and which is past due. I have the totals part working, but I can only get one location to show up, even though there are around 20 - 30.

Hey All, I've searched the entire board and cannot seem to find the info I'm looking for. Basically, what I'm trying to do is get the data to show only the totals for the months. I found some code around here that I've used but it only groups the date if they are exactly the same, for example, all 10/1/2008 gets grouped as one then all the 10/2/2008 gets grouped etc.. What I am looking for is all of October on one line all of November on the next line etc... Here is what I've got so far: $sql = mssql_query ("SELECT * FROM summary_sales_daily WHERE location_id = 100"); //GROUP BY summary_date"); if(!mssql_num_rows($sql)){ echo 'No Records Found'; } else{ while($row = @mssql_fetch_array($sql)){ $loc = $row["location_id"]; $total_temp = ($row["summary_value"] + $row["summary_value"]); $total = round($total_temp, 2); $x++; if($x == '1'){ $current_date = $row["summary_date"]; $current_total = $total; }else{ if ($row["summary_date"] == $current_date){ $current_total = $current_total + $total; }else{ echo "<TR>"; echo "<td>$loc</td>"; echo "<td>$current_date</td>"; echo "<td>$current_total</td>"; echo "</TR>"; $current_date = $row["summary_date"]; $current_total = $total; } } } } As you can see I've commented out group by because I keep getting this error and I can figure out how to fix it.... PHP Warning: mssql_query(): message: Column 'summary_sales_daily_uid' is invalid in the select l ist because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16 ) in monthly_summary.php on line 46 PHP Warning: mssql_query(): Query failed in monthly_summary.php on line 4 6 PHP Warning: mssql_num_rows(): supplied argument is not a valid MS SQL-result resource in monthly_summary.php on line 53 Thanks in advance!!