How to convert SUMPRODUCT() in MS Access 2010?

[chleng]

Hi all.

I got one Excel working fine with the SUMPRODCUCT() formula and now I wish to convert that to MS Access Query.
Below is the formula that I use in the Excel worksheet:

=SUMPRODUCT((TR!$A$2:$A$108=F2)*(TR!$B$2:$B$108))

How am I going to apply the same thing in MS Access to get the same result in MS Excel?

Thanks,
Xavier

[ssanfu]

In looking at Excel Help to find out what the function does, it looks like there is an error in the formula you posted. According to Help, the syntax could be

=SUMPRODUCT((TR!$A$2:$A$108),(TR!$B$2:$B$108))

or

=SUM((TR!$A$2:$A$108)*(TR!$B$2:$B$108))

But in answer to your question, you would have to write a custom function. Not knowing what the two ranges in the Excel function represent, the trick is getting the correct number of records, especially if the two ranges end up in two different tables in Access.

If the data is in one table, you can use a query to select the data, but again the trick is limiting the records. Once you have a query that selects the data you want, loop through the records (using VBA), multiplying the two fields, then summing the product.

In general, the code would have

Dim MyTotal as double

open a recordset (query)
do while not .EOF
MyTotal = MyTotal + (Field1 * Field2)
loop

[chleng]

Hi ssanfu,

Thanks for your reply.

Sorry for not clarifying my question.

I am trying to sum up the sumproduct of multiple worksheets. The formula that I use is:
=SUMPRODUCT(('Sheet2!$A$2:$A$108 = Sheet1!$F2)*('Sheet1!$B$2:$B$108))


I would like to apply the same thing in MS Access but I don't know how to do it.


Does anyone know how to do it in Access?


Many thanks,
Xavier

[recyan]

How is the excel worksheets data stored in your access db ?

Thanks

[chleng]

How is the excel worksheets data stored in your access db ?

Thanks

The Excel worksheets are stored as different tables in Access.

Thanks.

[recyan]

Assuming, these tables are related,
just post your tables with a few dummy data thrown in ( mentioning the relationships between the tables ) & the resultant data you want out of them, (something like below).

table1
tbl1ID	Field1	Field2
1	10	70
2	20	80
1	30	90
2	40	100
1	50	110
2	60	120
table2
tbl2ID	Field3	Field4
1	130	190
2	140	200
1	150	210
2	160	220
1	170	230
2	180	240
Resultant Data Needed from Query

...............................

Am sure, some one should be able to help.

Thanks

[chleng]

Table 1

Supplier	Grade	Currency	Price	Unit	TRNo	Input	Output
BA	1Com	USD	100	M	QO11831	2.00	1.61260
DA	1Com	USD	259	M	QO12861	3.00	2.69862

The Output field in Table 1 is getting from the SUMPRODUCT() Function - SUMPRODUCT(('Sheet2!$A$2:$A$108 = Sheet1!$F2)*('Sheet1!$B$2:$B$108))

Table 2

TRNo	RMOutputM3
QO11831	0.060853
QO11831	0.060919
QO11831	0.091791
QO11831	0.759972
QO11831	0.639067
QO12861	0.990564
QO12861	1.08962
QO12861	0.618433

When the TRNo in Table 2 is the same with the TRNo in Table 1, the output field in Table 1 will get the total of the same TRNo.
E.g.
Output for TRNo QO11831 = 0.060853 + 0.060919 + 0.091791 + 0.759972 + 0.639067
= 1.61260

[recyan]

Just check out if below gives some guidelines :

Assumes TRNo is unique in Table1 :

SELECT 
    Table1.Supplier, 
    Table1.Grade, 
    Table1.Currency, 
    Table1.Price, 
    Table1.Unit, 
    Table1.TRNo, 
    Table1.Input, 
    qrySumOnTRNo.SumOfRMOutputM3 AS [Output]
FROM 
    Table1 
    INNER JOIN 
    (
        SELECT 
            Table2.TRNo, 
            Sum(Table2.RMOutputM3) AS SumOfRMOutputM3
        FROM 
            Table2
        GROUP BY 
            Table2.TRNo
    )
    AS qrySumOnTRNo 
    ON 
    Table1.TRNo = qrySumOnTRNo.TRNo;

Thanks

[chleng]

Just check out if below gives some guidelines :

Assumes TRNo is unique in Table1 :

SELECT 
    Table1.Supplier, 
    Table1.Grade, 
    Table1.Currency, 
    Table1.Price, 
    Table1.Unit, 
    Table1.TRNo, 
    Table1.Input, 
    qrySumOnTRNo.SumOfRMOutputM3 AS [Output]
FROM 
    Table1 
    INNER JOIN 
    (
        SELECT 
            Table2.TRNo, 
            Sum(Table2.RMOutputM3) AS SumOfRMOutputM3
        FROM 
            Table2
        GROUP BY 
            Table2.TRNo
    )
    AS qrySumOnTRNo 
    ON 
    Table1.TRNo = qrySumOnTRNo.TRNo;

Thanks

Thanks, recyan

The query works great.

[recyan]

Glad you found it helpful.

Thanks