Calculate totals from access and display on webpage - PHP

I have 4 different companies that have figures/amounts entered every day into access - so multiple rows in access. I want to display the total per company on the webpage.

Company 1: total                             Company 2: total                  Company 3: total                 Company 4: total

I have seen code where SUM is in the query where company equal company 1. Is this the best way to do it? Any help appreciated....
j8547Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd have to show us how your tables are structured.
0
Jan LouwerensSoftware EngineerCommented:
Yes. Your query would look something like:

SELECT Company, SUM(Amount) AS Total FROM FiguresAmounts
GROUP BY Company

Open in new window


All field and table names are made up, just replace them with your own.
0
j8547Author Commented:
I have a table with Date, Amount, company, Num.  I want it displayed as Company and Total amount.

I was using SELECT SUM(Amount) FROM table Group By Company

How do I display the information across the page by company. Only 4 companies.

My page is just display logo and nothing. No error..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
You might try creating a crosstab query.  That will get the companies all on the same row.  Just keep in mind that if you add a 5th company, you'll get a 5th column in the recordset.
0
j8547Author Commented:
attached is my current display (got help on it previously). It doesn't have code for any of the above. I have been trying to get the above working in a separate page but i wanted to know if I could incorporate it or do i have to have 2 queries....Just trying to have totals at the top for each company. I am ok with the 5th company. I k ow it should be easy to just add the totals as i am already pulling the data from the columns but I have been trying to do it in the query on the separate one which now i am thinking is the wrong way...Hope I make sense.
index1.php
0
j8547Author Commented:
This is how the screen looks now. I am thing a table on top of December show totals of companies...
screen.jpg
0
Ryan ChongCommented:
I have been trying to get the above working in a separate page but i wanted to know if I could incorporate it or do i have to have 2 queries....
if you're using just 1 query to produce 2 different outputs, it means that you got to place quite a lot of logic with summation to generate the proper output.

if you're using 2 separate queries, it could mean simple logic to generate the outputs but the fact that you will need to have 2 queries.
0
Julian HansenCommented:
Are you wanting to COUNT the number of records per company (i.e. how many rows there are for each)
OR
Are you wanting to SUM values in the rows themselves.

You can always use a join on your main query with a query that creates the COUNT / SUM for each company joined on the company name or id.
0
j8547Author Commented:
Looking for the SUM....
0
j8547Author Commented:
I have gotten it mostly working but I just wanted to ask if I have done it the correct way....Also I am struggling with the figures showing as currency. Not sure I can do it with $CellContent....

Attached is Code and screenshot
Screenshot.jpg
Index.txt
0
Julian HansenCommented:
Nothing wrong with what you have done.

Regarding the currency formatting - you can use money_format()
Or
number_format()

Note that to use these functions though you would need to change your output statements
echo '<table width="100%" border="2" cellspacing="0" cellpadding="4">';

foreach($row as $rowArray){
  $total = number_format($row['Total'],2);
  echo <<< HTML
    <th>{$row['Company']}</th>
    <th>$ {$total}</th>

HTML;
        }
}
echo "</table>";

Open in new window

Note in the above I have used number_format() with the currency symbol entered as part of the HTML code
0
j8547Author Commented:
When I change my statements to the above it comes out with no company and 0.00
0
Julian HansenCommented:
Without seeing your source code nothing we can do to help.

When reporting back on what you have done you need to tell us what you did, what was output (errors / program output) etc.

Otherwise we are left guessing.
0
j8547Author Commented:
Sorry about that....I have attached my code. I commented out the old code and added the new code as above.
I have also attached screen shots before and after of the code change so you can get a picture....Thanks for all the help.
after.jpg
Before.jpg
index.php
0
Julian HansenCommented:
You are being undone by confusing variable names.

You have this
$row = $ans->fetchAll(PDO::FETCH_ASSOC);

Open in new window

Then in your loop you have this
foreach($row as $rowArray){
  $total = number_format($row['Total'],2);
  echo <<< HTML
    <th>{$row['Company']}</th>

Open in new window

Do you see how in your loop you are using the array variable instead of the loop variable?

This is probably because you called your array $row and your row within in the array (in the loop) $rowArray

You need your loop to look like this
foreach($row as $rowArray){
  $total = number_format($rowArray['Total'],2);
  echo <<< HTML
    <th>{$rowArray['Company']}</th>
    <th>$ {$total}</th>

Open in new window


Also, as a sidebar, you are creating multiple connections to your PDO on line 28 and 76. Don't do that - rather create one connection and use the same connection in your script.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
j8547Author Commented:
I have been looking at that code for days and changing other things but didn't see what was in front of me. Thanks that worked perfectly. I will fix the multiple connections as well.
0
Julian HansenCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.