count function - how to use like SUMPRODUCT in Excel

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,
 
J

Jerry Whittle

Please show us an example of the table in question with the table name(s) and
field names.
 
D

Duane Hookom

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top