Display detail sum in master table

[inilcavalery]

Hi all,

Hope someone can help me here...

I have master table saving
s_id type value
1 A 600
2 B 800
3 C 600
4 D 300

and i have 4 detail table which is represent the type in master table
Detail table A
id s_id value
1 1 100
2 1 100
3 2 100
4 3 100
5 3 100
6 4 100

Detail table B
id s_id value
1 1 200
2 1 200
3 2 200
4 3 200

Detail table C
id s_id value
1 1 200
2 1 200
3 2 200

Detail table D
id s_id value
1 1 100
2 1 200

How can i get the sum of each detail table and display it in master table like in the above sample? i user userid security system, so every user (s_id) only see their own data but administrator can see all the data. I can sum the detail table using aggregate but can't figure out how to put the value in master table. I'm thinking to use view or custom view or custom field maybe.

Thank you

[mobhar]

It actually depends on how you setup your data entry form. Please post your project setting/customization for more discussion.

[inilcavalery]

Lets assume i don't have master table yet.
There are 4 detail tables called A,B,C and D. each table has same column
id : auto increment
s_id: hold the userid value which also used for userid security.
value: INT

In each table there will be many different s_id value. What i'm trying to achieved is:

1. Using userid security so user will only see their own data.
2. SUM/TOTAL the value column using aggregate.
3. Display the SUM/TOTAL of detail table in master table.

Point 1 and 2 can be done easily. Now i'm still can't figure out how to perform point 3. Master table should be look like this
id: auto increment
s_id : hold the userid value
type: this will hold values which represent each of detail table, in this case the values of this column will be A,B,C,D.
Value: this will be the SUM/TOTAL from detail table.

Thank you

[mobhar]

Use "Row_Inserted" server event to update the related record in your master table.

[inilcavalery]

i was thinking about updating master table when new detail record added. If that the case then i need to check if a record is already exist or not. I mean for each user there will be maximum 4 records which is A,B,C and D. If record is already exist then i perform UPDATE query, but if it doesn't exist yet then i should use INSERT query. How can i do that?

[mobhar]

// For example, in your "Row_Inserted" server event of table A:

$val = ew_ExecuteScalar("SELECT COUNT(*) FROM your_master_table WHERE type = 'A'");
if ($val == 0) {
ew_Execute("INSERT INTO your_master_table VALUES ('1', 'A', " . $rsnew["value"] . ")");
} else {
ew_Execute("UPDATE your_master_table SET value = value + " . $rsnew["value"] . " WHERE type = 'A'");
}

[sangnandar]

To avoid redundancy and keep the integrity of you db you should create the master table as a view using UNION ALL to merge all the detail tables.

select
s_id as s_id,
"A" as type,
sum(value) as value,
(select sum(value) from tableA) as total
from tableA group by s_id
UNION ALL
select
s_id as s_id,
"B" as type,
sum(value) as value,
(select sum(value) from tableB) as total
from tableB group by s_id
UNION ALL
...

This query will produce,
s_id | type | value | total

Then tell phpmaker,
Page_Render()
if (user) $this->total->Visible=FALSE;
if (administrator) $this->value->Visible=FALSE;

---

Another alternative is to use WITH ROLLUP after GROUP BY clause to get the total-sum.

select
s_id as s_id,
"A" as type,
sum(value) as value,
from tableA group by s_id with rollup
UNION ALL
....

Advantage: Save some performance by removing sub-query (select sum(value) from tableA).

This query will produce,
s_id | type | value
with an extra additional row for each detail table
s_id | type | value
NULL | A | (total-sum)

Then tell phpmaker,
Recordset_Selecting()
if (user) // s_id is not null
if (administrator) // s_id is null

[inilcavalery]

Hi sangnandar,

I try your query in phpmyadmin and its work. But when i use the query in phpmaker view it won't work, #4200 you have error in your SQL syntax. Check the manual that corresponds to your mariaDB. I use XAMPP with mysql version 5.0.12.

[inilcavalery]

I have successfully make the view, now how can i link each row to the detail table?