count function - how to use like SUMPRODUCT in Excel

  • Thread starter Thread starter Rabbie Williams
  • Start date Start date
R

Rabbie Williams

I've got myself utterly stumped - in Excel it was reasonable straightforward
but I don't really know where to start now that I need to transfer it over to
Access. Any help would be massively appreciated.

There are 3 variables: week number, Territory area, product group
Basically i'm trying to count the number of times each product group is
spoken about (a boolean value) on each territory area on a specified week as
logged on a seperate sheet.

In excel i've done this by setting up a grid with Product names listed along
the top & territory areas listed down the left so that for productA in
territory 6 the following formula is used:
=SUMPRODUCT((ProdRef=B3)*(areaRef=6)*(WeekRef=$B$1))

I'm assuming it needs multiple count queries with an overriding summary
query but this isn't at all elegant and would be a pain to have to update.

Thanks,
 
Please show us an example of the table in question with the table name(s) and
field names.
 
This sounds like a simple crosstab query with Product Names would be the
Column Heading, Territory Area & Week could be the Row Heading, and Count of
something could be the Value.

I could be wrong since you didn't provide sample records with the desired
output/display.
 
Back
Top