SUM on DISTINCT FIELD

[rsampathy]

I want to get the sum value of LIMIT on distinct value of FACID.

Table: A1
NAME FACID LIMIT


=======================
A1 F1 100
A3 F1 100
A2 F2 100
A4 F5 200
A5 F5 200
=====================
400=100+100+200= sum of (Limit) for unique FACID.

Need to display this value in the form?
Form contains all the record?
then need to display sum of distinct FACID,LIMIT then sum.

[apr pillai]

Exclude the Name field to do this and create a Query with grouping on both fields. Save it with the name Query1.

Use Query1 as input to the second Query to create the Totals. Sample SQLs are given below:

Query1

SELECT DISTINCT [A1].FACID, [A1].LIMIT
FROM [A1]
GROUP BY [A1].FACID, [A1].LIMIT;

Query2

SELECT Query1.FACID, Sum(Query1.LIMIT) AS SumOfLIMIT
FROM Query1
GROUP BY Query1.FACID;