count function - how to use like SUMPRODUCT in Excel

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

 

[Jerry Whittle]

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

 

[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.

 

[Rabbie Williams]

Hi,

I've uploaded the demo I did in Excel to
http://www.athrodax.co.uk/ARlog_demo.xls. My corresponding effort in Access
is uploaded to http://www.athrodax.co.uk/ARlog_demo.mdb

One of the reasons i've had to change it to access is that more than one
product range may be discussed with a single individual so I decided I needed
to have tick boxes.

Cheers,