SUMPRODUCT??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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).
 
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.
Click to expand...

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]
 
Back
Top