SUMPRODUCT??

[Lisa]

HELP…. I have an Excel workbook that I have moving over to Access 2000. I
have imported my entire workbook with the exception of a scorecard that is
maintained with formulas that are attached to the other sheets in the
workbook.
(i.e.=SUMPRODUCT((sheet1!$E$3:$E$1826="o")*(sheet1!$F$3:$F$1826=C2)*(sheet1!$J$3:$J$1826="R"))
Now that all my sheets have become tables can I do the same thing within a
query and if so how?
Any assistance would be greatly appreciated and Thank you and advance.

 

[John Nurick]

Hi Lisa,

I've never seen SUMPRODUCT() used anything like your formula below and
don't really know what it does. Maybe it's equivalent to something like

SELECT COUNT(FieldE) AS Score
FROM MyTable
WHERE (FieldE='o')
AND (FieldF=XXX)
AND (FieldJ='R')
;

XXX might be a value or a subquery depending on what's the equivalent of
Sheet1!C2 in your database.

If the reference to C2 is a relative reference and the SUMPRODUCT()
formula is filled down or across multiple cells, things get a bit more
complicated and you'll need to JOIN the table to itself (if filled down)
or UNION it to itself (if filled across).

 

[John Vinson]

HELP…. I have an Excel workbook that I have moving over to Access 2000. I
have imported my entire workbook with the exception of a scorecard that is
maintained with formulas that are attached to the other sheets in the
workbook.
(i.e.=SUMPRODUCT((sheet1!$E$3:$E$1826="o")*(sheet1!$F$3:$F$1826=C2)*(sheet1!$J$3:$J$1826="R"))
Now that all my sheets have become tables can I do the same thing within a
query and if so how?
Any assistance would be greatly appreciated and Thank you and advance.

Well... Excel is a spreadsheet. Access is a relational database. They
ARE DIFFERENT and applying spreadsheet logic will get you into no end
of trouble!

That said, you can create a Query joining the Tables on some
appropriate field (I don't know what that might be, but it will
certainly NOT be a row number since tables don't have row numbers).
You can put an expression in a vacant Field cell in the query grid. I
do not know what the Excel SUMPRODUCT function does, so I can't give
specific advice; but I expect that between individual-row level
calculations using expressions in a Field cell, and Totals queries,
you should be able to replicate the funtionality. It just won't be
done the same way!

John W. Vinson[MVP]